SQL 2005/2008 Max(bit)

I'm certain it was possible to Max(bit) in SQL 2000 but that changed in SQL 2005/2008.

 SELECT Max(Col1+0)
   FROM SomeTable

I love stackoverflow.com

I needed a query to use Rank() row counters within a Group

Here it is:  http://stackoverflow.com/questions/1139719/sql-server-query-for-rank-rownumber-and-groupings

Here is my query and resultset

SELECT
    Rank() over (Partition by EncounterID Order by EncounterID, RefPhysID)+1 as ranks
    ,EncounterID
    , RefPhysID
FROM 
    tbEncountersCourtesyCopies
Group By
    EncounterID, RefPhysID, RefPhysOfficeID 
Order by
    ranks asc

ranks    EncounterID    RefPhysID
2        9              1022
2        12             1095
2        18             91
3        12             1279

Get identity column seed values

Writing a routine to fetch identity column seed values for tables in a SQL 2008 database
Looking to import data into a database and I need to allow room when I do insert with identity_insert on

                using (SqlConnection conn = new SqlConnection(mMRISConnConfig.ConnectionString))
                {
                    string mrisSQL = @"SELECT IDENT_SEED(TABLE_NAME) AS Seed,
                               IDENT_INCR(TABLE_NAME) AS Increment,
                               IDENT_CURRENT(TABLE_NAME) AS Current_Identity,
                               TABLE_NAME
                               FROM INFORMATION_SCHEMA.TABLES
                               WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
                               AND TABLE_TYPE = 'BASE TABLE'
                               AND TABLE_NAME in ('W_PATIENT','W_REFERRING_PHYSICIAN','W_REFERRING_PHYSICIAN_LOCATION')";

                    conn.Open();
                    SqlCommand cmd = new SqlCommand(mrisSQL, conn);
                    SqlDataReader reader = cmd.ExecuteReader();
}

Making good use of SQL 2005/2008 Rank() function

Ran into the age old problem of migrating data from one system into a new system  where new system requires a unique row id but the previous system does not.  In the scerario,  I am moving 400,000 records across a linked server from SQL 2000 to SQL 2005/2008.  Generating a rowID per row is expense in any type of cursor or While Loop for 1/2 million records.  Using the 2005/2008 Rank() function I managed to migrate 400,000 records in under 3 minutes with the linked server connection running over internet through my home cable modem connection.  Not too bad at all.

SELECT rank() OVER (ORDER BY fPat.PatientID, fMrn.MRN) as RowID

FROM [FRIS].FRIS_DB.[dbo].tbPatients fPat                 

INNER JOIN MRIS.[dbo].W_PATIENT mPat ON mPat.Pat_Id = fPat.PatientID

INNER JOIN [FRIS].FRIS_DB.[dbo].tbMRN fMrn ON fMrn.PatientID = fPat.PatientID

Enable xp_cmdshell in SQL server 2008

Use t-sql to turn on  xp_cmdshell in SQL server 2008

Run this on the master database.
Source:

EXECUTE SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
 
EXECUTE SP_CONFIGURE 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
GO
 
EXECUTE SP_CONFIGURE 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

Use while loop through table

Needed a solution to loop through a sql resultset without a temp table / table variable / cursor.
Found this question: http://stackoverflow.com/q/1578198/44597

My answer: http://stackoverflow.com/questions/1578198/can-i-loop-through-a-table-variable-in-t-sql/7417780#7417780

declare @id int

        SELECT @id = min(fPat.PatientID)
        FROM tbPatients fPat
        WHERE (fPat.InsNotes is not null AND DataLength(fPat.InsNotes)>0)

while @id is not null
begin
    SELECT fPat.PatientID, fPat.InsNotes
    FROM tbPatients fPat
    WHERE (fPat.InsNotes is not null AND DataLength(fPat.InsNotes)>0) AND fPat.PatientID=@id
    
    SELECT @id = min(fPat.PatientID)
    FROM tbPatients fPat
    WHERE (fPat.InsNotes is not null AND DataLength(fPat.InsNotes)>0)AND fPat.PatientID>@id
        
end    

T-SQL Merge Example using a value pair

merge W_Entity_Pool
          using ( SELECT 58 AS entity_typ_cd) AS A
                  ON A.entity_typ_cd = W_Entity_Pool.entity_typ_cd

when matched then
update set pool_seq_num = 5

when not matched then
INSERT(entity_typ_cd,DATA_DOMAIN_ID, pool_seq_num)
values(58,1,5);

Find Stored Procedures referencing a table

Two ways:

SELECT o.object_id, o.name, m.definition
FROM sys.sql_modules m
INNER JOIN sys.objects o ON o.object_id = m.object_id
WHERE m.definition like '%tableName%'

SELECT p.name, c.text FROM syscomments c
JOIN sys.procedures p ON p.object_id=c.id
WHERE c.text LIKE '%tableName%'
ORDER BY p.name

How to append to a text field in t-sql SQL Server 2005/2008

update
tablename
set
fieldname = convert(nvarchar(max),fieldname) + 'appended string'

List all constraints for a table in SQL 2005/2008

--Find Constraints for a table
SELECT *
FROM sys.all_objects
WHERE type in ('F','PK') and parent_object_id in (
SELECT object_id from sys.all_objects WHERE Type= 'U' and name= 'tbPatients')

Slow Text queries in SQL Server 2008

From StackOverflow.Net   (http://blog.stackoverflow.com/2008/10/adde-a-second-server/)

   From the article:

      While many queries are faster under SQL Server 2008, and the tooling is dramatically and indisputably better (intellisense for queries!), there is one downside for us: SQL Server 2008 is slower at full-text search operations than SQL 2005. That’s kind of a bummer because we rely heavily on full text. I saw a glimpse of this in my initial testing, when I had both servers up side-by-side with the same data, running the same queries, to see how much faster it would be (new versions are supposed to be faster, right?). My experiments showed that any full-text queries I tried were inexplicably slower on 2008, but we thought it had to do with different query plans, and was something we could work around.

   StackOverflow admin tried the workaround fix shown below but haven’t seen much improvement .

  

http://sqldev.wordpress.com/2008/09/16/sql-server-2008-full-text-slowness/

  

From the article:

It appears to happen if:

• You have AUTO tracking on your full text indexes; although we changed it to manual and still had this issue.
• You experience Full-text queries taking a long time to execute; normally when updates are happening at the same time so you might only see this in production.
• One or more of your queries are complicated or take some time to complete.

You can check to see if your system is slow due to this by:

• SELECT * FROM sys.dm_os_wait_stats statement , it shows very high wait times some of the locks.
• Running Sp_who2; it should consistently show that the full-text gather is blocking full-text queries and, in turn, is being blocked by the queries.

The current work around for this issue is to use a global trace flag microsoft kindly enabled on the RTM build.  To use this, type the following Transact-SQL statement:

DBCC TRACEON (7646, -1)

This has a minor side-effect.. which may or may not be important in your organisation. Certainly in ours, it is not. Your full text result may be ever so slightly out of date – e.g. you might return or not return a particular document from the index. However, this issue relates to the dirtyness of an index in terms of 10’s of milliseconds. Important? Unlikely….

         

    I tested the workaround for the fulltext query fix.

    On a large client DB, I ran the following query:

UPDATE tbExams

SET BusinessNotes=''

WHERE ExamID=

  Several times and the average execution time 3-4 secs

I applied DBCC TRACEON (7646, -1)

 

And then I ran the query again against different exams and the execution time dropped to 0-1 secs

Additional:  SQL Server 2008 must have AWE enabled to use all of the available memory the OS reports.
                   I assume this is necessary for 32/64 bit.

Using linked servers between SQL 2008 (64bit) and SQL 2000 (32bit)

***IMPORTANT: If the client using DSI Ellgence will require a linked server connection from SQL Server 2008 (x64) to SQL Server 2009 (x86 32bit)
You may receive an error message when you try to run distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server or to a linked SQL Server 7.0 server.  

If this occurs: Please review and follow the steps provided  in Microsoft Knowledge base article: http://support.microsoft.com/kb/906954 
To to resolve this problem, manually run the Instcat.sql script that is included with SQL Server 2000 SP3 or SP4 on the 32-bit SQL Server 2000 server.

Append to a text blob in SQL 2008

From StackOverflow
Wish I could remember this without google...

update 
  tablename
set
  fieldname = convert(nvarchar(max),fieldname) + 'appended string'

Show Stats and Execute plan in query window

The results of these commands are displayed after a query is executed from Query Analyzer, generally after the results of the query are displayed. They include:

• SET SHOWPLAN_TEXT ON: Returns estimated (not actual, as the query is not run) detailed information on how the query will run.
• SET SHOWPLAN_ALL ON: Returns estimated (not actual, as the query is not run) detailed information on how the query will run, plus additional information, such as the estimated number of rows, I/O, CPU, and the average size of a the query.
• SET STATISTICS IO ON: Shows the number of scans, logical reads, and physical reads performed during the query. Returns actual data based on a query that has run.
• SET STATISTICS TIME ON: Shows the amount of time (in milliseconds) needed to parse, compile, and execute a query. Returns actual data based on a query that has run.
• SET STATISTICS PROFILE ON: Shows a recordset that represents a profile of the query execution. Returns actual data based on a query that has run.

You will not want to run the first two commands listed above at the same time as the others because the first two commands are based on estimated data, while the last three are based on real data.
If you are using SQL Server 2000, using these commands are less needed as you can get all of the same type of data other ways from within Query Analyzer.

Proper Useage related Indexed Views

Hidden Gotcha with Indexed Views NoExpand vs Expand See microsoft article

The NOEXPAND view hint forces the query optimizer to treat the view like an ordinary table with a clustered index - basically I thought this was the main purpose for an indexed View...

Using the NOEXPAND view hint

When SQL Server processes queries that refer to views by name, the definitions of the views normally are expanded until they refer only to base tables. This process is called view expansion. It's a form of macro expansion.

The NOEXPAND view hint forces the query optimizer to treat the view like an ordinary table with a clustered index. It prevents view expansion. The NOEXPAND hint can only be applied if the indexed view is referenced directly in the FROM clause. For example,

SELECT Column1, Column2, ... FROM Table1, View1 WITH (NOEXPAND) WHERE ...

Use NOEXPAND if you want to be sure to have SQL Server process a query by reading the view itself instead of reading data from the base tables. If for some reason SQL Server chooses a query plan that processes the query against base tables when you'd prefer that it use the view, consider using NOEXPAND. You must use NOEXPAND in all versions of SQL Server other than Developer and Enterprise editions to have SQL Server process a query against an indexed view directly. You can see a graphical representation of the plan SQL Server chooses for a statement using the SQL Server Management Studio tool Display Estimated Execution Plan feature. Alternatively, you can see different non-graphical representations using SHOWPLAN_ALL, SHOWPLAN_TEXT, or SHOWPLAN_XML. See SQL Sever books online for a discussion of the different versions of SHOWPLAN.

Using the EXPAND VIEWS query hint

When processing a query that refers to a view by name, SQL Server always expands the views, unless you add the NOEXPAND hint to the view reference. It attempts to match indexed views to the expanded query, unless you specify the EXPAND VIEWS query hint in an OPTION clause at the end of the query. For example, suppose there is an indexed view View1 in the database. In the following query, View1 is expanded based on its logical definition (its CREATE VIEW statement), and then the EXPAND VIEWS option prevents the indexed view for View1 from being used in the plan to solve the query.

SELECT Column1, Column2, ... FROM Table1, View1 WHERE ...
OPTION (EXPAND VIEWS)

Use EXPAND VIEWS if you want to be sure to have SQL Server process a query by accessing data directly from the base tables referenced by the query, instead of possibly accessing indexed views. EXPAND views may in some cases help eliminate lock contention that could be experienced with an indexed view. Both NOEXPAND a

ALTER DATABASE replaces sp_dbcmptlevel

ALTER DATABASE Compatibility Level (Transact-SQL)

Sets certain database behaviors to be compatible with the specified version of SQL Server. New in SQL Server 2008, the following ALTER DATABASE syntax replaces the sp_dbcmptlevel procedure for setting the database compatibility level. For other ALTER DATABASE options, see ALTER DATABASE (Transact-SQL).

ALTER DATABASE AdventureWorks
SET COMPATIBILITY_LEVEL = 100;
GO

vs

sp_dbcmptlevel AdventureWorks 100

Getting this error when trying to create an assembly from a DLL.

Determine current database compatibility level
DBCC CHECKDB

Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:

• Runs DBCC CHECKALLOC on the database.
  
• Runs DBCC CHECKTABLE on every table and view in the database.
  
• Runs DBCC CHECKCATALOG on the database.
  
• Validates the contents of every indexed view in the database.
  
• Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
  
• Validates the Service Broker data in the database.
  

This means that the DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands do not have to be run separately from DBCC CHECKDB. For more detailed information about the checks that these commands perform, see the descriptions of these commands.

Crystal Reports sucks

For VSS.NET 2003 and crystal 9
You must apply SP1, SP2, SP3 to fix a host of issues including table alias not handled properly when crystal renders its internal report query.

Also: If table schema has changed for an existing report, connect to database with current schema and use database expert to sync cached schema in report to actual schema.

Why anyone stills uses this product is beyond me? At least editor ingration into VSS.NET makes it 2% less painful.

Crystal 9 will connect to SQL Server 2008 database.

Microsoft Analysis Management Objects

Microsoft Analysis Management Objects Analysis Management Objects (AMO) is a .NET Framework object model that enables software developers to create client-side applications to manage and administer Analysis Services objects.

Audience(s): Customer, Partner, Developer

X86 Package(SQLSERVER2008_ASAMO10.msi) - 2659 KB
X64 Package (SQLSERVER2008_ASAMO10.msi) - 4317 KB
IA64 Package(SQLSERVER2008_ASAMO10.msi) - 6055 KB

Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider

Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider The Analysis Services 10.0 OLE DB Provider is a COM component that software developers can use to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2008 Analysis Services. This provider implements both the OLE DB specification and the specification’s extensions for online analytical processing (OLAP) and data mining.

Note: Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider requires Microsoft Core XML Services (MSXML) 6.0, also available on this page.

Audience(s): Customer, Partner, Developer

X86 Package(SQLServer2008_ASOLEDB10.msi) - 19490 KB
X64 Package (SQLServer2008_ASOLEDB10.msi) - 43945 KB
IA64 Package(SQLServer2008_ASOLEDB10.msi) - 50682 KB

Microsoft ADOMD.NET ADOMD.NET

Microsoft ADOMD.NET ADOMD.NET is a Microsoft .NET Framework object model that enables software developers to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2008 Analysis Services. ADOMD.NET is a Microsoft ADO.NET provider with enhancements for online analytical processing (OLAP) and data mining.

Note:The English ADOMD.NET setup package installs support for all SQL Server 2008 languages.

Audience(s): Customer, Partner, Developer

X86 Package(SQLSERVER2008_ASADOMD10.msi) - 4312 KB
X64 Package (SQLSERVER2008_ASADOMD10.msi) - 9263 KB
IA64 Package(SQLSERVER2008_ASADOMD10.msi) - 6776 KB