Query to Find First and Last Day of Previous, Current and Next Month - SQL Server

Hello All,

This is normal requirement to find First and Last day of Month. I got one in which I have to find not only current month or given month but Previous and Next month as well.

Following query will gives start date and end date for previous, current and next month respectively. That is First Day of Previous Month, Last Day of Previous Month, First Day of Current Month, Last Day of Current Month, First Day of Next Month and Last Day of Next Month.

DECLARE @PreviousMonthFirstDay DATETIME
DECLARE @PreviousMonthLastDay DATETIME
DECLARE @CurrentMonthFirstDay DATETIME
DECLARE @CurrentMonthLastDay DATETIME
DECLARE @NextMonthFirstDay DATETIME
DECLARE @NextMonthLastDay DATETIME

DECLARE @CurrentDate AS DATETIME = CONVERT( DATETIME, CONVERT( DATE, GetDate() ) )

SELECT @CurrentMonthFirstDay = DATEADD(dd, -(DAY(@CurrentDAte)) + 1, @CurrentDate)
SELECT @CurrentMonthLastDay = DATEADD(d, -1, DATEADD(mm, 1, @CurrentMonthFirstDay))

SELECT @PreviousMonthFirstDay = DATEADD(m, -1, @CurrentMonthFirstDay)
SELECT @PreviousMonthLastDay = DATEADD(d, -1, @CurrentMonthFirstDay)

SELECT @NextMonthFirstDay = DATEADD(d, 1, @CurrentMonthLastDay)
SELECT @NextMonthLastDay = DATEADD(d, -1, DATEADD(mm, 1, @NextMonthFirstDay))

SELECT 
     @PreviousMonthFirstDay PreviousMonthFirstDay
    ,@PreviousMonthLastDay PreviousMonthLastDay
    ,@CurrentMonthFirstDay CurrentMonthFirstDay
    ,@CurrentMonthLastDay CurrentMonthLastDay
    ,@NextMonthFirstDay NextMonthFirstDay
    ,@NextMonthLastDay NextMonthLastDay 

Output as of 12-Dec-2012
You can make this more generic by replacing GetDate() with perticular date while setting up value of @CurrentDate

How to get multiple result set of procedure using LINQ to SQL

There always be case where one procedure returns more then one result set. Getting those data in DataSet is lazy way of coding, best way to do that is using DataReader. DataReader having method call NextResult which allows us to read next result set if any.

Read more from here.

Schema and Data Compare Scripts in MS SQL

Hello All,

I was searching for a tool which gives me the Schema and or data comparison for SQL Server, its really needed while we are updating staging or updating our live site. There may be lots of tool available on net but I found one tool which is part of Microsoft® Visual Studio Team System 2008 Database Edition GDR R2.

Team Edition provides lots of functionality, mainly we are using to write Test Case which is very useful for Test Driven Development.

On top of this if you have Database edition then you can do lots of things with it. One of the feature is Schema and Data Compare. This blog post will walk you thru steps to compare data and schema.

Schema Comparison

1. Go to New Schema Comparison form Data menu

2. Next select the two database, one is source and other is target

 

3. When press OK will give you the Schema Compare of all the objects including Tables, Views, Stored Procedures and many more.

Here you can see the list of Objects and the Object Definitions windows

4. In following image Table is expanded so you can see the changes in details on what table what action is held.

 

5. To see more in details select one of the updated row and you can see the definition in Object Definitions window; it will generate Create script and highlight the difference. There is also Schema Update Script which will have alter script of all the changes that is detected during Schema Compare.

6. You can either copy the script or export to file or editor by using Export To Edition command on tool box.

This was all about how to detect schema change, its very handy with lots of options.

Data Comparison

Now same way we have Data Comparison, which is use to compare data, it provides tons of details and script for insert/update and delete. The steps are pretty much starlight forward. I have listed the steps here.

1. Click on New Data Comparison from Data menu

2. It will ask for Source and Target tables

3. On click of Finish, it will provide you all the information that is differ in between two database tables

You can see here it shows 2 rows are only in source, means two rows are added to SB_mst_Group table. It also provides the what exact rows are added in middle section along with other options.

In third last section which is Data Update Script; provides you the script for the action, here we have addition, so its provide the insert statements which we can run in UAT to get the date.

I found it very useful and easy to generate required script in few mouse click!

Getting child ids in string separate with delimiter along with parent id[SQL SERVER]

Hi Friends,

I come accros one requirement where I need... the child id should be delemited by '|' and along with the parent Id. Lets say I am having parent as company, one company is having more then one code.

So now my requirement is to get the company code in '|' saperated with company id, just like this.

Comp_id     company_code
----------- ------------
1           1|2
2           1|2|3|4
3           1|2

We can achive this by either using a user-defined aggregate function or using loop. I will explore the user-defined aggreagate function and post how to do, but for now lets do it with looping. Here is the code to achive this by using while-loop.

DECLARE @companies Table
(
    Comp_id INT,
    company_code  int
)
 
insert into @companies values(1,1)
insert into @companies values(1,2)
insert into @companies values(2,1)
insert into @companies values(2,2)
insert into @companies values(2,3)
insert into @companies values(2,4)
insert into @companies values(3,1)
insert into @companies values(3,2)
 
DECLARE @TmpTable Table
(
    Id int IDENTITY (0, 1) NOT NULL,
    CompanyId INT
)
 
SET NOCOUNT ON
 
DECLARE @Tmpcompanies Table
(
    Comp_id int,
    company_code  varchar(10)
)

INSERT INTO @TmpTable SELECT DISTINCT comp_id FROM @companies
 
DECLARE @MaxId INT
DECLARE @PipedList VARCHAR (MAX)
Declare @Count INT
Declare @CurrComp INT
 
SELECT @MaxId = MAX(id) FROM @TmpTable
SET @Count = 0
WHILE( @Count <= @MaxId)
BEGIN
    SET @PipedList = NULL
    SELECT @CurrComp = Comp_id, @PipedList = COALESCE(CAST(@PipedList AS VARCHAR(MAX)) + '|', '') 
    + CAST(company_code AS VARCHAR(10)) FROM @companies Cmp, @TmpTable TT
        WHERE CMP.Comp_id = TT.CompanyId AND TT.ID = @Count
    INSERT INTO @Tmpcompanies VALUES(@CurrComp, @PipedList)
    SET @Count = @Count + 1
END
 
SELECT * FROM @Tmpcompanies

Inserting collection of string to DB with duplicate check

Hi all,

We need some kind of functionality where from frontend [ASP.NET] we pass the comma or semicolon saperated strings into database for inserting purpose and also we need to check the duplication of the string.

With the world of XML we can do this easily, here is the Sql Script to do so. My string collection is the email list, i have to insert multiple emails and also need to check for the duplication. at the end I get the duplicate email list, which can be easily get form Frondend [ADO.NET] using output param or any other way.

SET NOCOUNT ON

DECLARE @Table AS TABLE
(
    Email VARCHAR(100)
)
 
INSERT INTO @Table VALUES('a@b.com')
INSERT INTO @Table VALUES('c@d.com')
  
DECLARE @EmailList VARCHAR(MAX)
DECLARE @XMLEmailList XML
  
SELECT @EmailList = 'a@b.com;c@d.com;aa@aa.com;fads@ddd.com'
SELECT @XMLEmailList= '<email>' +  REPLACE(@EmailList, ';', '</email><email>') + '</email>'
  
DECLARE @DuplicateEmail VARCHAR(100)
  
SELECT @DuplicateEmail = COALESCE(CAST(@DuplicateEmail AS VARCHAR(MAX)) + ';', '') + + CAST(a.value('.', 'varchar(100)') AS VARCHAR(10))
FROM @XMLEmailList.nodes('/email') v(a)
WHERE a.value('.', 'varchar(100)') in (SELECT Email FROM @Table)
  
INSERT INTO @table
SELECT a.value('.', 'varchar(100)')
FROM @XMLEmailList.nodes('/email') v(a)
WHERE a.value('.', 'varchar(100)') not in (SELECT Email FROM @Table)
  
SELECT @DuplicateEmail
SELECT * FROM @Table 

Limitations of the XML Data Type

Hi all,

I found the limitation of XML Data Type introduced in SQL Server 2005.

Although the XML datatype is treated like many other datatypes in SQL Server 2005, there are specific limitations to how it is used. These limitations are:

1.    XML types cannot convert to text or ntext data types.


2.    No data type other than one of the string types can be cast to XML.


3.    XML columns cannot be used in GROUP BY statements.


4.    Distributed partitioned views or materialized views cannot contain XML data types.


5.    Use of the sql_variant instances cannot include XML as a subtype.


6.    XML columns cannot be part of a primary or foreign key.


7.    XML columns cannot be designated as unique.


8.    Collation (COLLATE clause) cannot be used on XML columns.


9.    XML columns cannot participate in rules.


10.    The only built-in scalar functions that apply to XML columns are ISNULL and COALESCE. No other scalar built-in functions are supported for use against XML types.


11.    Tables can have only 32 XML columns.


12.    Tables with XML columns cannot have a primary key with more than 15 columns.


13.    Tables with XML columns cannot have a timestamp data type as part of their primary key.


14.    Only 128 levels of hierarchy are supported within XML stored in the database.

Query operators evaluation

Hi all,

Here is the order in which query operators are evaluated. There are 11 levels.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH{CUBE | ROLLUP}
7. HAVING
8. SELECT
9. DISTINCT
10. HAVING
11. TOP

First the query processor reads all the rows from the FROM the left table and apply the ON condition with the right table provided in JOIN.

If there are more JOINs the same is done for all the JOINs.

Then the WHERE clause is applied to filter rows.

Then GROUP BY is done

There WITH clause get evaluated followed by HAVING.

Then the columns are selected. (This is the reason why you cannot use a column alias in WHERE or GROUP BY)

Then DISTINCT clause applied

Then ORDER BY is processed...(This is the reason why you CAN use a column alias in the ORDER BY clause)

Then TOP clause get evaluated.
Refrence: SQL Server 2005: Query processing basics. An event presented by Vinod Kumar

Here is the screen-shot of the video captured during the event presented by Vinod Kumar in event.

OUTPUT CLAUSE (Transact-SQL)

How to know your INSERT UPDATE or DELETE statement effect how much recoreds? Or what if I need the list of identity values which get generated by INSERT statements?

One way is before insert I should get the MAX ID; after sucessfull insert again I get the ID and select the ID which are in betwwen them, like...

DECLARE @MinId INT 2: DECLARE @MaxId INT
 
SELECT @MinId = MAX(ID) from SearchResult
 
INSERT INTO SearchResult(Keyword, Hits)
SELECT Keyword, Hits FROM TmpTable
 
SELECT @MaxId = MAX(ID) from SearchResult
 
SELECT ID FROM SearchResult WHERE ID > @MinID AND <= @MaxID

But the result will not always true, like in the world of multitasking, what if some other insert also take place??

Now? How can we get all the newly added or created identities?

The other and best way is using OUTPUT CLAUSE. Here we go...

DECLARE @IDTable Table
{
    Id BIGINT
}
 
INSERT INTO SearchResult(Keyword, Hits)
OUTPUT INSERTED.ID INTO @IdTable

SELECT Keyword, Hits FROM TmpTable

The inserted IDs will be in magic table called INSERTED, and by using OUTPUT CLAUSE we can grab it and save it to temp table or temporary variable... from there we can select the newly added IDs, like..

SELECT ID FROM @IDTable

Read more on OUTPUT CLAUSE

Using SP_EXECUTESQL

What we can do with EXECUTE?

With EXECUTE you can build the complicate query which contains the replacement of parameters values run time. Just imagine the situation where you have only single query which needs to get run 3-4 times; each time the substitution is taking place?

Have a look at the following query; which requires running twice and also substituting the values.

/* Following by using EXEC*/

DECLARE @AcTypeID nvarchar(40)
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @Gender int

/* Specify the parameter value*/
SET @AcTypeID = 1
set @Gender = 1

/* Build the SQL string*/
SET @SQLString = 'SELECT count(*) as TotalUserByAccount FROM [User] WHERE AccountTypeId = ' + CAST( @AcTypeID as NVARCHAR(10))
SET @SQLString = @SQLString + ' And Gender = ' + CAST(@Gender as NVARCHAR(1))

/* Execute the same string*/
EXEC(@SQLString)

/* Specify the parameter value*/
SET @AcTypeID = 5
set @Gender = 0

/* Build the SQL string AGAIN*/
SET @SQLString = 'SELECT count(*) as TotalUserByAccount FROM [User] WHERE AccountTypeId = ' + CAST( @AcTypeID as NVARCHAR(10))
SET @SQLString = @SQLString + ' And Gender = ' + CAST(@Gender as NVARCHAR(1))

/* Execute the same string*/
EXEC(@SQLString)

So this is the first problem with EXECUTE?command, now next problem; it does not generate execution plans which are more likely to be reused by SQL Server. So the performance is not good if we have such query execute frequent.

Now, using SP_EXECUTESQL we can overcome both of above mentions problem. SP_EXECUTESQL gives you the possibility to use parameterized statements, EXECUTE does not. Parameterized statements gives no risk to SQL injection and also gives advantage of cached query plan. I will show you the cached query plan too.

First here is the query.

/* Now lets use sp_executesql */

DECLARE @AcTypeID nvarchar(40)
DECLARE @Gender nvarchar(40)
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(1000)

/* Build the SQL string once. */
SET @SQLString = N'SELECT count(*) as TotalUserByAccount FROM [User] WHERE AccountTypeId = @paramAcTypeID and Gender = @paramGender'

/* Specify the parameter format once. */
SET @ParmDefinition = N'@paramAcTypeID bigint, @paramGender int'

/* Set the param value */
set @Gender = 1
SET @AcTypeID = 1

/* Execute the query */
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @paramAcTypeID = @AcTypeID, @paramGender = @Gender

/* set only param value again*/
set @Gender = 0
SET @AcTypeID = 5

/* Execute the query */
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @paramAcTypeID = @AcTypeID, @paramGender = @Gender

Now let’s check our Cache Objects of SQL Server, [I used DBCC FREEPROCCACHE first so its cleare all the cache plan and run the query]



Now the thing that I like most; getting OUTPUT variable by using SP_EXECUTESQL, here are the code for getting variable as OUTPUT.

/* Variable declaration */
DECLARE @UserID uniqueidentifier
DECLARE @UserName nvarchar(40)
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(1000)

/* Build the SQL string*/
SET @SQLString = N'SELECT @paramUserID = UserID FROM [User] WHERE FavUserName = @paramUserName'

/* Specify the parameter format once. */
set @ParmDefinition = N'@paramUserName nvarchar(40), @paramUserID uniqueidentifier output'

/* Execute the string with the parameter value. */
EXECUTE sp_executesql @SQLString, @ParmDefinition,
            @paramUserName = 'imran786', @paramUserID = @UserID OUTPUT

/* Get the output value */
print @UserID

One of the limitations of SP_EXECUTESQL in SQL Server 2000 was that the input code string was practically limited to 4000 characters. This limitation is not relevant anymore because you can now provide sp_executesql with an NVARCHAR(MAX) value as input. Note that SP_EXECUTESQL supports only Unicode input—unlike EXEC which supports both regular character and Unicode input.

Read more...

Procedure to generate C# Class file

-- =============================================
-- Description:   Generates C# class code for a table
--                and fields/properties for each column.
--                Run as "Results to Text" or "Results to File" (not Grid)
--                Example: EXEC usp_TableToClass 'MyTable'
-- =============================================

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_TableToClass]
@table_name SYSNAME
AS

SET NOCOUNT ON

DECLARE @temp TABLE
(
    sort INT,
    code TEXT
)

INSERT INTO @temp
SELECT 1, 'public class ' + @table_name + CHAR(13) + CHAR(10) + '{'

INSERT INTO @temp
SELECT 2, CHAR(13) + CHAR(10) + '#region Constructors' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 3, CHAR(9) + 'public ' + @table_name + '()'
+ CHAR(13) + CHAR(10) + CHAR(9) + '{'
+ CHAR(13) + CHAR(10) + CHAR(9) + '}'

INSERT INTO @temp
SELECT 4, '#endregion' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 5, '#region Private Fields' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 6, CHAR(9) + 'private ' +

CASE
    WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
    WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
    WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
    WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
    WHEN DATA_TYPE = 'BIT' THEN 'bool '
    WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
        ELSE 'object '
    END + '_' + COLUMN_NAME + ';' + CHAR(9)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 7, '#endregion' +
CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 8, '#region Public Properties' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 9, CHAR(9) + 'public ' +
CASE
    WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
    WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
    WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
    WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
    WHEN DATA_TYPE = 'BIT' THEN 'bool '
    WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
        ELSE 'object '
    END + COLUMN_NAME +
    CHAR(13) + CHAR(10) + CHAR(9) + '{' +
    CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
    'get { return _' + COLUMN_NAME + '; }' +
    CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
    'set { _' + COLUMN_NAME + ' = value; }' +
    CHAR(13) + CHAR(10) + CHAR(9) + '}'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 10, '#endregion' +
CHAR(13) + CHAR(10) + '}'

SELECT code FROM @temp
ORDER BY sort

APPLY Clause in SQL Server 2005

select ClientId, Birthday from
(select top 10 Clnt.* from [Client] Clnt
inner join CaseClient CC on Clnt.ClientId = CC.ClientID
inner join [Case] C on C.CaseID = CC.CaseID order by Clnt.ClientID) as Result

This is the query which returns me the ClientId and Birthday; its TOP 10 Clients.

select Clnt.ClientId, Birthday, TopData.FormNumber  from
(select top 10 Clnt.* from [Client] Clnt
inner join CaseClient CC on Clnt.ClientId = CC.ClientID
inner join [Case] C on C.CaseID = CC.CaseID order by Clnt.ClientID) as Result
inner join
(select TOP (3) * from ClientSession where ClientSession.ClientID = Result.ClientId) TopData
on TopData.ClientID = Result.ClientID

What I am trying to do here is… there is multiple sessions for one client, and form that multiple I need top 3 rows and its FormNumner. The above query will syntactically right, parser will not generate any error; but it will at compile time it will throw error

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Result.ClientId" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Clnt.ClientId" could not be bound.

For correlated Join; Result is not defined.

So here is the solution with SQL Server 2005's new APPLY clause. The APPLY clause let's you join a table to a table-valued-function. That let's you write a query like this:

select Result.ClientID, Result.Birthday, TopData.FormNumber from
(select top 10 Clnt.* from [Client] Clnt
inner join CaseClient CC on Clnt.ClientId = CC.ClientID
inner join [Case] C on C.CaseID = CC.CaseID order by Clnt.ClientID) as Result
CROSS/span> Apply
 fn_GetTopClientSession(Result.ClientId, 3) AS TopData

And here is the expected output

ClientID    Birthday                FormNumber
----------- ----------------------- -----------
46          1990-01-01 00:00:00.000 11094
46          1990-01-01 00:00:00.000 11062
46          1990-01-01 00:00:00.000 30211
52          1983-01-04 00:00:00.000 11159
52          1983-01-04 00:00:00.000 11155
52          1983-01-04 00:00:00.000 30190
53          2000-01-01 00:00:00.000 11154
53          2000-01-01 00:00:00.000 11158
53          2000-01-01 00:00:00.000 11157
68          2000-01-01 00:00:00.000 10104
68          2000-01-01 00:00:00.000 12168
68          2000-01-01 00:00:00.000 11215
73          1957-10-09 00:00:00.000 11137
73          1957-10-09 00:00:00.000 32464
73          1957-10-09 00:00:00.000 11150

And here is the function

CREATE FUNCTION dbo.fn_GetTopClientSession(@ClientId AS int, @n AS INT)
RETURNS TABLE
AS
RETURN
  select TOP (@n) * from ClientSession where ClientSession.ClientID = @ClientId
GO

I just put the Correlated Join query inside the Function, nothing more.

You can see the APPLY clause acts like a JOIN without the ON clause!!!

There are two flavors of APPLY clause, CROSS and OUTER. The OUTER APPLY clause returns all the rows on the left side whether they return any rows in the table-valued-function or not. The columns that the table-valued-function returns are null if no rows are returned. The CROSS APPLY only returns rows from the left side f the table-valued-function returns rows.

Notice that I'm just passing in the ClientId to the function. It returns the TOP 3 rows based on the amount of the order. Since I'm using CROSS APPLY a Cases without Client won't appear in the list. I can also pass in a number other than 3 to easily return a different number of Cases per Client. So I could list the top 5. How cool is that?!?

Flexibility using TOP clause in SQL Server 2005

Hello all,

Upto now we know that how to use TOP clause in Sql Server 2000 . SQL Server 2005 come up with more flexible way to use TOP clause.

Here is the simple way to get TOP 10 [or say 'n' a dynamic number] from a table.

DECLARE @Rows INT
SET @Rows = 10

SELECT TOP ( @Rows ) *
FROM TempMaster

This will return the top 10 rows from TempMaster. You can also replace @Rows with anything that evaluates to a number.

Now look at the following query; its odd but runs just fine:

SELECT TOP ( SELECT COUNT(*) FROM TempMaster ) *
FROM TempDetails

You can also use the TOP clause for INSERT, UPDATE and DELETE statements. If you wanted to DELETE in batches of 500 you can now do that using the TOP clause.

SQL Server And XML: XML Workshop XX - Generating an RSS 2.0 Feed with TSQL(SQL server 2000)

I love to work with RSS and ATOM. Here is one more good and powerful feature of Sql Server.

The FOR XML EXPLICIT approach will work for both SQL Server 2000 and 2005/2008. You can find the article here, here or here.

How one can generate objects scripts with DROP and Create in same file in SQl Server 2005 Like in SQl Server 2000

I often generate scripts of stored procedures and save that code in a txt file,in development env its very crucial because to overwrite stored procedures any other development db.

For my this task sql server 2000 was very good it generated drop and create statemntments implicitly. But now in sql server its very difficult and in our development environment there are more than 3000 procs so when i try to generate script in sql server 2005 ,first i need to generate "drop to" and then "create to" and it also take a very long time and often goes to stuck...

I found solution from Microsoft site, we just need to install SQL Server 2005 SP2.

If you interested to see whats new in SQL Server 2005 SP2 then check it out here.

Converting row to column in Sql Server 2005

Consider the following data and our target is to have all the ClientId starting form 247 to 252 will be in column
name Client1, Client2... etc. 

UserID      CaseID      CaseNumber  ClientID
----------- ----------- ----------- -----------
80          216         1087        247
80          216         1087        248
80          216         1087        249
80          216         1087        250
80          216         1087        251
80          216         1087        252
80          276         1140        328
80          277         1143        329
80          347         1191        438
80          348         1192        439

SQL Server 2005 introduced Ranking, Partitioning and Pivoting. By using all togather
we can achive our goal.



Ranking functions that provide the ability to rank a record within a partition. 
In this case, we can use RANK() to assign a unique number for each record, and partition
by the ClientID (so that the RANK will reset for each ClientID)

By prefixing some text to the rank number, we end up with something like:

SELECT UserID, C.CaseID, CaseNumber, ClientID, 
'ClientId' + CAST(
    RANK() OVER (
    PARTITION BY C.CaseID, CaseNumber
 ORDER BY ClientID) AS VARCHAR(10)) ClientIdListing
FROM  [Case] C, CaseClient CC
WHERE C.CaseId = CC.CaseID AND USerID = 80

Result:

UserID      CaseID      CaseNumber  ClientID    ClientIdListing
----------- ----------- ----------- ----------- ------------------
80          216         1087        247         ClientId1
80          216         1087        248         ClientId2
80          216         1087        249         ClientId3
80          216         1087        250         ClientId4
80          216         1087        251         ClientId5
80          216         1087        252         ClientId6
80          276         1140        328         ClientId1
80          277         1143        329         ClientId1
80          347         1191        438         ClientId1
80          348         1192        439         ClientId1
            

The new column (ClientIdListing) is the concatenation of the literal string "ClientId"
and the string representation of the number that the RANK function returned. But
the bigger point is that now this column can be used for pivoting, and result in
a series of new columns called [ClientId1], [ClientId2], [ClientId3], etc.




Pivoting in SQL Server 2005 requires explicit declaration of values as a column
list. In this case, we can't just say "Pivot on the ClientIdListing column", but
rather must say "Pivot on the ClientIdListing column, and make new columns only
for these specific values". This restriction is a little bit of a downside because
we need knowledge of the values in the column. Or, in this case, we need to know
how many ClientIds a Case could possibly have so that we create enough columns in
the result.




So here is the final query:

SELECT * FROM 
    (SELECT  UserID, C.CaseID, CaseNumber, ClientID, 'ClientId' 
        + CAST(RANK() OVER (PARTITION BY C.CaseID, CaseNumber
        ORDER BY ClientID) 
AS VARCHAR(10)) ClientIdListing
    FROM [Case] C, CaseClient CC
    WHERE C.CaseId = CC.CaseID 
    AND USerID = 80) P 

    PIVOT 
    (MAX(ClientID) FOR ClientIdListing IN 
        (ClientID1, ClientID2, ClientID3, ClientID4, ClientID5, ClientID6)
    ) AS Clients 

And here is the Output:

UserID      CaseID      CaseNumber  ClientID1   ClientID2   ClientID3   ClientID4   ClientID5   ClientID6
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
80          216         1087        247         248         249         250         251         252
80          276         1140        328         NULL        NULL        NULL        NULL        NULL
80          277         1143        329         NULL        NULL        NULL        NULL        NULL
80          347         1191        438         NULL        NULL        NULL        NULL        NULL
80          348         1192        439         NULL        NULL        NULL        NULL        NULL

Multiple Active Result Sets - Yet another powerful feature of SQL Server 2005

MARS [Multiple Active Result Sets ] is a new SQL Server 2005 feature that allows the user to run more than one SQL batch on an open connection at the same time.

If you for instance wanted to do some processing of the data in your data reader and updating the processed data back to the database you had to use another connection object which again hurts performance. There was no way to use the same opened connection easily for more than one batch at the time. There are of course server side cursors but they have drawbacks like performance and ability to operate only on a single select statement at the time.

SQL Server 2005 team recognized the above mentioned drawback and introduced MARS. So now it is possible to use a single opened connection for more than one batch. A simple way of demonstrating MARS in action is with this code:

string strConn = "Data Source=[DATASOURCE];Initial Catalog=[DATABASE];User ID=[UID];Password=[PWD];MultipleActiveResultSets=true";
string strSql = "select DoctorId, PatientId from [Patient] where DoctorId = {0}";
string strOutput = "<br/>DoctorId:{0} - PatientId{1}";

using (SqlConnection con = new SqlConnection(strConn))
{
    //Opening Connection
    con.Open();

    //Creating two commands form current connection
    SqlCommand cmd1 = con.CreateCommand();
    SqlCommand cmd2 = con.CreateCommand();

    //Set the comment type
    cmd1.CommandType = CommandType.Text;
    cmd2.CommandType = CommandType.Text;

    //Setting the command text to first command
    cmd1.CommandText = "select distinct DoctorId from [Doctor] where HospitalId = 8";



    //Execute the first command
    IDataReader idr1 = cmd1.ExecuteReader();

    while (idr1.Read())
    {
        //Read the first doctor from data source
        int intDoctorId = idr1.GetInt32(0);

        //create another command, which get patients of doctor
        cmd2.CommandText = string.Format(strSql, intDoctorId);

        //Execute the reader
        IDataReader idr2 = cmd2.ExecuteReader();

        while (idr2.Read())
        {
            //Read the doctor and patient
            Response.Write(string.Format(strOutput, idr2.GetInt32(0), idr2.GetInt32(1)));
        }
        //Dont forgot to close second reader, this will just close reader not connection
        idr2.Close();
    }
}

MARS is disabled by default on the Connection object. You have to enable it with the addition of MultipleActiveResultSets=true in your connection string.

SQL Server TSQL Coding standards

Hello All,

I found some good articles about SQL Procedure coding standards.

Have a look at following links

SQL Server Best Practices
SQL Server TSQL Coding Conventions, Best Practices, and Programming Guidelines
XML Best Practices for Microsoft SQL Server 2005

Different Options for Importing Data into SQL Server

Hello all,

I was having problem in getting data from different server, a hugh data millions of records that i have to import.

I tried lots of methods but its very difficult to get it done.

I found some solution for such hugh task.

Have a look @ Different Options for Importing Data into SQL Server.

Implement full text search using Stored procedures

Hello friends,

While working with Full-Text search, I come to situation where I have to write script which creates the catalog, enable it and start population on it.

Then I found there are stored procedures in master database only, which can usefull to craete, enable and populate the Full-Text Catalog in SQL Server.

Have a look at Implement full text search using Stored procedures.

Look at Help With Full-Text Catalogs - Stored Procedures are available too.

Avoid dynamic query at some extend [SQL 2k]

select * from [northwind].[dbo].[orders]

This will probably returns 830 rows [thats default],

Now what if I want top 10 rows or to 20 rows may be more, I will create dynamic query like...

declare @statement varchar(100)
declare @iTop int
set @iTop=3
set @statement ='select top ' + convert(varchar(2),@iTop) +  ' * from [northwind].[dbo].[orders]'
EXEC (@statement)

We can do as follows which don't requrie creating dynamic query.

declare @iTop int
set @iTop=3
set rowcount @iTop
select * from [northwind].[dbo].[orders]

This will display top 3 records!!

Now set rowcount to 0 to get all the records

set rowcount 0