Unexpected results Min() Max() results from SELECT TOP 100 sql statement

Testing a solution to find min() max() of 100 rows returned from a query provided some interesting results.

SELECT  min(x.radar_queue_key), max(x.radar_queue_key)
FROM (SELECT TOP 100 radar_queue_key FROM z_radar_queue WHERE radar_group_Code='SFV OTS' ORDER BY radar_queue_key asc) as x

Results:867 966

SELECT  min(x.radar_queue_key), max(x.radar_queue_key)
FROM (SELECT TOP 100 radar_queue_key FROM z_radar_queue WHERE radar_group_Code='SFV OTS') as x

Results:867 1879

SELECT  min(radar_queue_key), max(radar_queue_key)
FROM  z_radar_queue 
WHERE radar_group_Code='SFV OTS'

867 1879

What is interesting is that actual Min() =867 and actual max() =966 for the Top 100 rows.

Both of these queries return exactly the same rows in exactly the same order (even when flushing data cache) because radar_queue_key is the primary key on the table.

SELECT TOP 100 radar_queue_key FROM z_radar_queue WHERE radar_group_Code='SFV OTS' ORDER BY radar_queue_key asc

SELECT TOP 100 radar_queue_key FROM z_radar_queue WHERE radar_group_Code='SFV OTS'

Value of 1879 does not appear in either result set. The execution is completely different if the sql statements are used as a subquery.

Lesson here is always use an order by clause when fetching rows for aggregation + TOP xxxx

Useful sqlite system queries

List tables and indexes in SQlite database:

SELECT * FROM sqlite_master


List columns for a table:

pragma table_info()

Using BCP utility

Bulk copy program bcp is still supported by SQL 2008 R2 and SQL 2012 Yeah!!!

SQL 2008 R2 http://technet.microsoft.com/en-us/library/ms162802(v=sql.105).aspx
The bcp 10.0 client is installed when you install Microsoft SQL Server 2008 R2 tools. If tools are installed for both SQL Server 2008 R2 and SQL Server 2005, depending on the value of the PATH environment variable, you might be using the earlier bcp client instead of the bcp 10.0 client. This environment variable defines the set of directories used by Windows to search for executable files. To discover which version you are using, run the bcp /v command at the Windows Command Prompt. For information about how to set the command path in the PATH environment variable, see Windows Help.

SQL 2012 http://technet.microsoft.com/en-us/library/ms162802.aspx
The bcp 11.0 client is installed when you install Microsoft SQL Server 2012 tools. If tools are installed for both SQL Server 2012 and an earlier version of SQL Server, depending on the value of the PATH environment variable, you might be using the earlier bcp client instead of the bcp 11.0 client. This environment variable defines the set of directories used by Windows to search for executable files. To discover which version you are using, run the bcp /v command at the Windows Command Prompt. For information about how to set the command path in the PATH environment variable, see Windows Help.

For exporting

bcp [database name].dbo.[table name] out  -n -S -U -P

For Import

bcp [database name].dbo.[table name] out  -n -S -U -P


case is important on arguments.  -n is very important. forces bcp to sql native format.  This is essential of you are exporting and importing data to tables to prevent truncating.

Example

Bcp [New410].dbo.tbCPTDx out tbCPTDx.bcp –n –SRISQA2008R2 –Usa –Psa

Loop through SQL values without a cursor.

3rd Time I had to recreate this example so I'm posting it now.

   1:   

   2:  DECLARE @Users TABLE( 

   3:    [RowNumber] Int  PRIMARY KEY,

   4:    [UserId] uniqueidentifier)

   5:   

   6:  DECLARE @iCount Int

   7:   

   8:  INSERT INTO @Users (RowNumber, UserId)

   9:  SELECT rank() Over (order by UserId)

  10:          ,UserId

  11:  FROM Profile

  12:  WHERE PropertyValuesString like '%ReportPending%'

  13:   

  14:   

  15:  SET @iCount=1

  16:  WHILE ((SELECT MAX(RowNumber) FROM @Users) >= @iCount)

  17:  BEGIN

  18:   

  19:          SELECT * FROM @Users WHERE RowNumber = @iCount

  20:          SET @iCount=@iCount+1

  21:  END

Stripping special characters from SQL text

Reposting only to keep this from disappearing.  Simple redundancy and I do not claim any part of this work as my known.

This is a repost from Sean McWherter's blog.  

Excerpt from his blog:
Was looking for a better solution than a mile long REPLACE statement, found this. Originally written by Christian d’Heureuse, modified slightly by me. Removes all characters except: 0-9, a-z, A-Z, and spaces (remove “or @c = 32” if you want to also remove spaces).

Here is my usage.  Only added fx_ to function name.

CREATE function dbo.fx_RemoveSpecialChars (@s varchar(256)) returns varchar(256)
   with schemabinding
begin
   if @s is null
      return null
   declare @s2 varchar(256)
   set @s2 = ''
   declare @l int
   set @l = len(@s)
   declare @p int
   set @p = 1
   while @p <= @l begin
      declare @c int
      set @c = ascii(substring(@s, @p, 1))
      if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122 or @c = 32
         set @s2 = @s2 + char(@c)
      set @p = @p + 1
      end
   if len(@s2) = 0
      return null
   return @s2
   end

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

Using stored proc with table parameters

Sample stored procedure. SQL Server 2008+

CREATE PROCEDURE [dbo].[usp_audit_select_criticalresults]
    @exams dbo.ExamIDType READONLY    
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    SELECT DISTINCT x.ExamID, 
           case 
             WHEN sap2.ParameterValue is not null then CONVERT(bit,sap2.ParameterValue)
             else CONVERT(bit,0)
           END            
    FROM @exams x
    LEFT JOIN SessionActivityParameters sap
        ON sap.ParameterName='ExamId' AND x.ExamID = sap.ParameterValue
    LEFT JOIN SessionActivityParameters sap2 
        ON sap2.ParameterName='confirmed' AND sap.SessionActivityId =sap2.SessionActivityId
    LEFT JOIN SessionActivity sa
        ON sa.SessionActivityId = sap.SessionActivityId
    WHERE (sa.AttributeId='55CD62C2-AED5-4216-A3C6-FABD0183E130' or sa.AttributeId is null)

            
END

Executing stored procedure with test data in T-SQL:

DECLARE @exams dbo.ExamIDType;

INSERT INTO @exams (ExamID)
SELECT '00993736'
UNION SELECT '00993737'
UNION SELECT '00993738'
UNION SELECT '00993749'
UNION SELECT '00993750'
UNION SELECT '00993766'
UNION SELECT '00993767'

--SELECT * FROM @exams

exec usp_audit_select_criticalresults @exams

Using Merge to Update or Insert single row of data

Example of recent procedure I wrote to either update an existing row or insert a new row.
Table has the same structure as MembershipEmailFormat the table variable.

Found it easiest to create a table variable to be the source in the Using clause.  I realize that the main purpose of Merge statements really are merging muliple rows between two tables.  My use case is that I need to insert a new email address for a user or modify and existing email address.

CREATE PROCEDURE [dbo].[usp_user_merge_emailformat]
        @UserID UNIQUEIDENTIFIER,
        @Email varchar(256),
        @UseHTML bit
AS
BEGIN
    --SELECT @UserID='04EFF187-AEAC-408E-9FA8-284B31890FBD',
    --       @Email='gkindel@merge.com',
    --       @UseHTML=0
       
    DECLARE @temp TABLE
    (
            UserID UNIQUEIDENTIFIER,
            Email varchar(256),
            HtmlFormat bit
    )       

    INSERT INTO @temp(UserID,Email, HtmlFormat)
    Values(@UserID,@Email,@UseHTML)
           
    SELECT * FROM @temp    

    MERGE dbo.MembershipEmailFormat as t
    USING @temp AS s
    ON (t.UserID = s.UserID and t.Email = s.Email)
    WHEN MATCHED THEN UPDATE SET t.HtmlFormat = s.HtmlFormat
    WHEN NOT MATCHED THEN INSERT VALUES(s.UserID,s.Email,s.HtmlFormat);
END    

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

How to re-seed identity columns

DBCC CHECKIDENT (yourtable, reseed, 34)

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();
}

How to drop linked Server from SQL Server 2008

Another task I can't seem to remember.

Two useful system stored procedures showing linked servers in SQL 2005/2008


exec sp_helpserver
exec sp_linkedservers

I had trouble removing a linked server because I forgot to remove all of the logins.
(seems like removing a linked server should cascade remove the associated logins)

exec sp_droplinkedsrvlogin 'FUSIONRIS','sa'
exec sp_droplinkedsrvlogin 'FUSIONRIS',NULL
exec sp_dropserver 'FUSIONRIS'

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    

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 create a linked server from a local SQL server instance

I needed to add my local server as a named linked server on my development workstation.
Here is the magic hand shake...

--local linked server
EXEC sp_addlinkedserver
@server='FUSIONRIS',
@srvproduct='',
@provider='SQLNCLI',
@datasrc=''

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

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

Multiple Cursor Example

DECLARE @IPaddress nvarchar(40)
DECLARE @PartnerName nvarchar(40)
DECLARE @AllMessageCodes nvarchar(200)
DECLARE @MessageCode nvarchar(10)

DECLARE @Partners TABLE
(
IPAddress nvarchar(40),
PartnerName nvarchar(40),
AllMessageCodes nvarchar(200)
)


DECLARE cur CURSOR FOR
select distinct p.IPaddress, p.PartnerName
from tbHL7Partners p
inner join tbHL7MessageMapHeader MMH on p.PartnerID = MMH.PartnerID
inner join tbHL7MessageMapDetail MMD on MMH.MsgMapHeaderID = MMD.MsgMapHeaderID
inner join tbcdHL7Messages M on MMD.MessageID = M.MessageID
where p.PartnerDeleted = '0' and mmh.Active = '1' and mmd.[On] = '1'

OPEN cur
FETCH NEXT FROM cur INTO @IPaddress, @PartnerName
WHILE (@@FETCH_STATUS = 0)
BEGIN

SET @AllMessageCodes=''

DECLARE curMessages CURSOR FOR
select M.MessageCode
from tbHL7Partners p
inner join tbHL7MessageMapHeader MMH on p.PartnerID = MMH.PartnerID
inner join tbHL7MessageMapDetail MMD on MMH.MsgMapHeaderID = MMD.MsgMapHeaderID
inner join tbcdHL7Messages M on MMD.MessageID = M.MessageID
where p.PartnerDeleted = '0' and mmh.Active = '1' and mmd.[On] = '1'
and p.IPaddress =@IPaddress and p.PartnerName=@PartnerName
GROUP BY p.IPaddress, p.PartnerName, M.MessageCode

OPEN curMessages
FETCH NEXT FROM curMessages INTO @MessageCode
WHILE (@@FETCH_STATUS = 0)
BEGIN

SET @AllMessageCodes = @AllMessageCodes + ' ' + @MessageCode
--SELECT @MessageCode

FETCH NEXT FROM curMessages INTO @MessageCode
END
CLOSE curMessages
DEALLOCATE curMessages

INSERT INTO @Partners(IPAddress, PartnerName, AllMessageCodes)
SELECT @IPaddress, @PartnerName, @AllMessageCodes

FETCH NEXT FROM cur INTO @IPaddress, @PartnerName
END
CLOSE cur
DEALLOCATE cur

INSERT IDENTITY syntax

Piece of SQL knowledge that I can't seem to keep in my brain...

SET IDENTITY_INSERT table ON

SET IDENTITY_INSERT table OFF


Now to Reseed the Identity column in a table


DBCC CHECKIDENT (table, reseed, newseedvalue)