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

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

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    

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')

View TOP 20 expense queries on SQL server

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

Rebuild SQL Server 2005 Master database

Re-run setup:

start /wait setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=mypassword

Microsoft Suggestion:

Use of the Setup command to rebuild the master database has changed in this Microsoft SQL Server release. Run Setup.exe to rebuild, verify, and repair a SQL Server instance, and rebuild the system databases. This procedure is most often used to rebuild the master database for a corrupted installation of SQL Server.