SQL Server 2008 (KATMAI) - New Data Type: DATETIME2

Check it out here

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.

Silverlight 2 Beta2 Released

There is an announcement made on 06/06/2008 by Scoot Guthrie about Silverlight 2 Beta2 Released
Have a look at his post, its very nice and also informative, you can find the videos and tutorials

Some Weird/Good behaviour of Mozilla

I face one problem in Mozilla but with other browser it works fine.

The problem was like... the page get called twice!!! There is case when the server side code called twice, not client side.

After doing some good work found the solution, and was simpley good. The following line was causing the problem.

<asp:Image ID="imgTest" runat="Server" />

In first look you can see there is no problem with it. But there is still problem; the missing ImageUrl. Mozilla send request to current page asking for an image, which we havent set yet; as the value get assign from server side, by this reason the only server side code is get executed twice!!!!

Interesting, isn't it!!

So here is the solution:

<asp:Image ID="imgTest" runat="Server" ImageUrl="abc.jpg"  />

Working with Extension less URL

Many of us are some time requires to hanlde request which does not contains the extension.

http://yourdomain.com/imran
http://yourdomain.com/knowledge
http://yourdomain.com/world

If you want to achive this functionality then you have to use third party ISAPI module or IIS 6.0 Wildcard mapping feature.

If you goes with ISAPI module, then you should have dedicated server to install that module. And with the other approch of IIS 6.0 Wildcard mapping, its created the proformance problem, as each and every request it tries to apply mapping and degrade the performance.

So in this case the following solutions is good and easy.

What if you made request to a page which out extension? Yes, you will get HTTP 404 page, and IIS are not automatically forward your request to ASP.NET; we can't serve the URL.

So here is the solution,

- Configure the IIS so that; each 404 error page should get redirect to an ASPX page.
- Write the few lines of .NET code.

Thats it.

Lets see how we can do this.

Step 1: Configure IIS for redirection




Step 2: Write the .NET code

You have to capture Application_BeginRequest and put the specilized code to grab 404 url.

protected void Application_BeginRequest(object sender, EventArgs e)
{
    if (Request.Url.ToString().Contains("404;"))
    {
        string[] urlInfo404 = Request.Url.Query.ToString().Split(';');

        if (urlInfo404.Length > 1)
        {
            string strRequestUrl = urlInfo404[1].Replace(":" + Request.Url.Port + "/", "/");

            //You got the URL now take action on it.
        }
    }
}

Benefits:

- No third party ISAPI module
- No Wildcard mapping; leads to good profermance.

More on this article? Read the ScottGu's Blog on Tip/Trick: Url Rewriting with ASP.NET.

SQL SERVER 2008 (KATMAI): SQL Server 2008 (KATMAI) TSQL Enhancements - 1

SQL SERVER 2008 (KATMAI): SQL Server 2008 (KATMAI) TSQL Enhancements - 1

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.

101 LINQ Samples

Samples @delicious

Samples @microsoft