Category Archives: TSQL Tips n Tricks

SQL Server: Limit (MySQL) equal Function in SQL Server

In MySQL, Limit is a powerful function used with SELECT query to return rows BUT within given range. We commonly need it when returning only required rows to application according to paging range. For example to return rows from 11 to 15 we will use following query in MySQL

SELECT * FROM MyTable LIMIT 11, 5

In SQL Server, same functionality can be achieved with three different ways.

1.       With Derived Table (Most inefficient way, but applicable to all versions of SQL Server)

SELECT  *

      FROM    ( SELECT    *, ROW_NUMBER() OVER ( ORDER BY ColumnName ) AS RowNum
          FROM      MyTable ) DerivedTable
       WHERE   RowNum >= 11
        ANDRowNum <= 11 + ( 5 1 )

2.       With CTE Common Table Expression (Applicable to SQL Server 2005/2008/2012)
   ;
     WITH   CTE
          AS ( SELECT   *, ROW_NUMBER() OVER ( ORDER BY ColumnName ) AS RowNum
               FROM     MyTable)
    SELECT  *
    FROM    CTE
    WHERE   RowNum >= 11
            ANDRowNum <= 11 + ( 5 1 )

3.       With OFFSET_ROW FETCH (Applicable to SQL Server 2012 Only)
        Here “OFFSET means, how many rows to skip, “ROWS FETCH NEXT” means, how many rows to skip

 SELECT * FROM MyTable  

        OFFSET 10
        ROWS FETCH NEXT 5 ROWS ONLY;

SQL Server: Get All Databases Size

To get recent size of all databases on an instance, I have found following simple query very useful. 
SELECT  d.name,
       ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM    sys.master_files mf
       INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE   d.database_id> 4 — Skip systemdatabases
GROUP BY d.name
ORDER BY d.name

SQL Server: How to Create a Parameterized Views

In SQL Server functionality of parametrized views can beachieved by creating an in-line table valued function. Let’s see how to convert a commonly used view HumanResources.vEmployee in AdventureWorks to a parametrized view.
CREATE FUNCTION PV_GetEmployeeInformationBySSN
(     
       — Add theparameters for the function here
       @NationalIDNumber VARCHAR(9)
)
RETURNS TABLE
AS
RETURN
(
       — Add theSELECT statement with parameter references here
       SELECT   e.BusinessEntityID, p.Title, p.FirstName, p.MiddleName,
              p.LastName, p.Suffix, e.JobTitle, pp.PhoneNumber,
              pnt.NameAS PhoneNumberType,ea.EmailAddress,
              p.EmailPromotion, a.AddressLine1, a.AddressLine2, a.City,
              sp.NameAS StateProvinceName,a.PostalCode,
              cr.NameAS CountryRegionName,
              p.AdditionalContactInfo
       FROM            HumanResources.Employee AS e INNERJOIN
              Person.Person AS p
              ONp.BusinessEntityID =e.BusinessEntityID INNERJOIN
              Person.BusinessEntityAddressAS bea
              ONbea.BusinessEntityID =e.BusinessEntityID INNERJOIN
              Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN
              Person.StateProvince AS sp
              ONsp.StateProvinceID =a.StateProvinceID INNERJOIN
              Person.CountryRegion AS cr
              ONcr.CountryRegionCode =sp.CountryRegionCode LEFTOUTER JOIN
              Person.PersonPhone AS pp
              ONpp.BusinessEntityID =p.BusinessEntityID LEFTOUTER JOIN
              Person.PhoneNumberType AS pnt
              ONpp.PhoneNumberTypeID =pnt.PhoneNumberTypeID LEFTOUTER JOIN
              Person.EmailAddress AS ea
              ONp.BusinessEntityID =ea.BusinessEntityID
       WHERE e.NationalIDNumber = @NationalIDNumber
)
GO
How to use it. Very Simple 🙂
SELECT * FROM PV_GetEmployeeInformationBySSN (‘112457891’)

SQL Server: TOP…WITH TIES a Beauty of TSQL

TOP clause is commonly used to get top requiredrows from a result set. Beauty of this clause is that it can be used with WITHTIES clause, to retrieve all similar rows to base result set.
According to BOL “WITH TIES Specifiesthat additional rows be returned from the base result set with the same valuein the ORDER BY columns appearing as the last of the TOP n(PERCENT) rows. TOP…WITH TIES can be specified only in SELECT statements, andonly if an ORDER BY clause is specified.
For example from following simple table I need toget records which have minimum purchase date value. In first method we will usecommon IN clause.
–Create temporary table
CREATE TABLE #MyTable (Purchase_Date DATETIME, Amount INT)
–Insert few rows to hold
INSERT INTO #MyTable
SELECT ’11/11/2011′, 100 UNION ALL
SELECT ’11/12/2011′, 110 UNION ALL
SELECT ’11/13/2011′, 120 UNION ALL
SELECT ’11/14/2011′, 130 UNION ALL
SELECT ’11/11/2011′, 150
–Get all records which has minimum purchase date (i.e. 11/11/2011)
SELECT * FROM #MyTable
WHERE Purchase_Date IN
       (SELECT MIN(Purchase_Date) FROM #MyTable)

We can also get ourdesired results by using TOP…WITH TIES.
SELECT TOP(1) WITH TIES * FROM #MyTable
ORDER BY Purchase_Date
By executing above query,you can find TOP WITH TIES worked amazingly but does this short code is reallya smart code. Let’s compare their performance.


Though TOP…WITH TIESclause really shortened our code but you can see that it performed poorly ascompare to our traditional code. This happened just because of ORDER BY clause.
This poor performance canbe controlled by placing a well defined index.

SQL Server: How to Remap Existing Database Users on New Instance

During shifting databases acrossdifferent instances a common problem is ORPHAN USERS and remapping of these orphan database users on new instance. That’s what we were facing in these days, but thanks to Chad Mattox who provided a simple solution.
/*******************************************************
This procedure should be created in the Master database. Thisprocedure takes no parameters. It will remap orphaned users in the currentdatabase to EXISTING logins of the same name. This is usefull in the case a newdatabase is created by restoring a backup to a new database, or by attaching thedatafiles to a new server.
*******************************************************/
UseMaster 
Go 
IF OBJECT_ID(‘dbo.sp_fixusers’) IS NOT NULL
BEGIN
DROP PROCEDUREdbo.sp_fixusers
IF OBJECT_ID(‘dbo.sp_fixusers’) IS NOT NULL
PRINT ‘<<< FAILEDDROPPING PROCEDURE dbo.sp_fixusers >>>’
ELSE
PRINT ‘<<<DROPPED PROCEDURE dbo.sp_fixusers >>>’
END
GO
CREATE PROCEDUREdbo.sp_fixusers
AS
BEGIN
DECLARE @username varchar(25)
DECLARE fixusers CURSOR
FOR
SELECT UserName =name FROM sysusers
WHERE issqluser =1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS= 0
BEGIN
IF @username=‘dbo’
BEGIN
EXEC sp_changedbowner ‘sa’
END
ELSE
BEGIN
EXEC sp_change_users_login ‘update_one’, @username,@username
END
FETCH NEXT FROM fixusers
INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers
END
go
IF OBJECT_ID(‘dbo.sp_fixusers’) IS NOT NULL
PRINT ‘<<<CREATED PROCEDURE dbo.sp_fixusers >>>’
ELSE
PRINT ‘<<< FAILEDCREATING PROCEDURE dbo.sp_fixusers >>>’
go

SQL Server Denali: Format(), a Most Wanted Function

Most of SQL Server developers/DBAs think that convertingDateTime to a specific format is bit difficult as you need to remember specificformat number, which you can use with CONVERT(). Like if you need to convertdate to German format i.e.dd.mm.yy,then you can do it as follow:
SELECT CONVERT(NVARCHAR(30),GETDATE(),104) AS GermanDateFormat
Finding it tough to remember theseconversion code/number, I put these format codes in my early post.But now using SQL Server Denali, you can use a most demanded function FORMAT().

FORMAT ( value, format [, culture ] )

 

DECLARE @d datetime = GETDATE();
SELECT  FORMAT ( @d, ‘dd/MM/yyyy’) AS ‘dd/MM/yyyy’,
        FORMAT ( @d, ‘dd.MM.yyy’)  AS ‘dd.MM.yy’,
        FORMAT ( @d, ‘dd/MMM/yyy’) AS ‘dd/MMM/yy’,
        FORMAT ( @d, ‘MMM dd, yy’) AS ‘MMM dd, yy’,
        FORMAT ( @d, ‘MMMM dd, yyyy (dddd)’) AS ‘MMMM dd, yyyy (dddd)’,
        FORMAT ( @d, ‘dddd MMMM dd, yyyy ‘) AS ‘MMMM dd, yyyy (dddd)’,
        FORMAT ( @d, ‘hh:mm:ss’) AS ‘hh:mm:ss’
FORMAT()is not limited to Date/Time only
DECLARE @I int = 15;
SELECT FORMAT(@I,‘c’) AS Currency,
              FORMAT(@I,‘e’) AS Scientific,
              FORMAT(@I,‘p’) AS Percent_,
              FORMAT(@I,‘x’) AS HexaDecimal
Note: In above examples we have not used, third parameter i.e. culture. If value for culture is not provided then it will use session default language. In my case it is “en-US”

SQL Server Denali: New Function to Get Last Date of Month

 A new function is introduced in SQL Server DenaliCTP3, though which you can easily find out last date of any month. Prior tothis we were using different methods to get last date of month. Following one,I like most.
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GetDate())+1,0))
But now it’s more easy with EOMONTH() function. Just provide a date in DateTimeformat or in string format and it will return last date of requiredmonth.
SELECT EOMONTH (GETDATE()) AS Result;
Or you can add number months to get last date.
SELECT EOMONTH (GETDATE(),3) AS Result;

SQL Server Denali: IIF Logical Function

If you have developed some sort of applications usingMicrosoft Access, then you are definitely familiar with “IIF” logical function.In SQL Server, prior to SQL Server Denali we can use “CASE” instead of “IIF” asthis logical function was not available. But in SQL Server Denali CTP3, “IIF”is available with same ease and functionality.
According to BOL”IIF is a shorthand way for writing a CASE statement. Itevaluates the Boolean expression passed as the first argument, and then returnseither of the other two arguments based on the result of the evaluation. Thatis, the true_value is returned if the Boolean expression is true, and thefalse_value is returned if the Boolean expression is false or unknown.true_value and false_value can be of any type. The same rules that apply to theCASE statement for Boolean expressions, null handling, and return types alsoapply to IIF.
The fact that IIF is translated into CASE also has an impact on other aspectsof the behavior of this function. Since CASE statements can nested only up tothe level of 10, IIF statements can also be nested only up to the maximum levelof 10. Also, IIF is remoted to other servers as a semantically equivalent CASEstatement, with all the behaviors of a remoted CASE statement.”

Let’s try it with simple example.

 
(Only applicable for SQL Server DenaliCTP 3)
DECLARE @weather VARCHAR(50) = ‘Rainy’, — Rain/Sunny
             @umbrella BIT = 1 –1= Yes we have, 0=Nowe don’t have
–Single IIF
SELECT IIF(@weather =‘Rainy’,‘Oh! its raining’,‘Sun is shinning..Enjoy’)
–MultipleIIF            
SELECT IIF(
      @weather = ‘Rainy’,IIF(
                          @umbrella = 1,‘Its raining but you cantake umbrella with you’
                                       ,‘Its raining, stay inside’)
                         ,‘Sun is shining..Enjoy’)

SQL Server: Quick Way to Create Single Table Backup

There are several ways to create backup for a single table in SQL Server database. In SQL Server 2008, now you can create insert statements for selected tables. Beside this Export wizard is commonly used to create a flat file backup for a single table. Both methods are reliable but still time consuming. And when I searched for quickest method to take backup of a single table I found following code by SQL Expert Fred.

USE AdventureWorks

GO

DECLARE @table VARCHAR(128),
@file VARCHAR(255),
@cmd VARCHAR(512)

— If i need to create CSV file Product table then   
SET @table = ‘Production.Product’
SET @file = ‘D:\BCP_OUTPUT\’ + @table + ‘_’ + CONVERT(CHAR(8), GETDATE(), 112)
+ ‘.csv’
SET @cmd = ‘bcp “AdventureWorks.’ + @table + ‘” out “‘ + @file + ‘” -S. -T -c -t,’

EXEC master..xp_cmdshell @cmd

Code basically uses BCP to create a CSV file for a given table. I can create a template of above code, and then just load, change values and execute. So simple but still it has a drawback. It creates a CSV file for all rows but WITHOUT column header row. Now how can I import this table later on, without column header row?

Then I found quickest and simplest way to achieve this goal. Simply type select * query but before execution
• Click on Tools — > options and change values for Query Result Output Format and Custom Delimiter (I preferred pipe sign “|”)

• Press Ctrl+Shift+F buttons, so it can save result to file.
    SELECT * FROM Production.Product
• On execution, provide file name and your desired path and it’s done

Don’t worry about newly created file extension. When need to import just select it from All Files (*,*).

SQL Server: Does Unwanted Tables in a Query or View Affect Performance

Recently a friend of mine asked, that is it true that presence of extra tables in joins section of a query, will affect query performance. Extra tables means,tables which can be skipped from query without affecting query result. For example following query has extra tables (other than vendor and contact tables) in join section
USE AdventureWorks
GO

SELECT Vendor.Name,
Contact.Title,
Contact.FirstName,
Contact.MiddleName
FROM Person.Address AS a
INNER JOIN Purchasing.VendorAddress AS VendorAddress
ON a.AddressID = VendorAddress.AddressID
INNER JOIN Person.StateProvince AS StateProvince
ON StateProvince.StateProvinceID = a.StateProvinceID
INNER JOIN Person.CountryRegion AS CountryRegion
ON CountryRegion.CountryRegionCode = StateProvince.CountryRegionCode
INNER JOIN Purchasing.Vendor AS Vendor
INNER JOIN Purchasing.VendorContact AS VendorContact
ON VendorContact.VendorID = Vendor.VendorID
INNER JOIN Person.Contact AS Contact
ON Contact.ContactID = VendorContact.ContactID
INNER JOIN Person.ContactType AS ContactType
ON VendorContact.ContactTypeID = ContactType.ContactTypeID
ON VendorAddress.VendorID = Vendor.VendorID
Though this is NOT common to have extra tables in our usual queries but it could be possible in views. A view can be created with multiple tables and selecting columns from each joined table. And later on when we will query this view we can use only few columns in our select statement. So when we will execute above query SQL Server Query Analyzer will skip all those tables which are not part of game. Here is execution plan of above query.

Same query with more columns, pushing all tables in action.

SELECT Vendor.Name,
ContactType.Name AS ContactType,
Contact.Title,
Contact.FirstName,
Contact.MiddleName,
a.AddressLine1,
a.AddressLine2,
a.City,
StateProvince.Name AS StateProvinceName,
a.PostalCode,
CountryRegion.Name AS CountryRegionName,
Vendor.VendorID
FROM Person.Address AS a
INNER JOIN Purchasing.VendorAddress AS VendorAddress
ON a.AddressID = VendorAddress.AddressID
INNER JOIN Person.StateProvince AS StateProvince
ON StateProvince.StateProvinceID = a.StateProvinceID
INNER JOIN Person.CountryRegion AS CountryRegion
ON CountryRegion.CountryRegionCode = StateProvince.CountryRegionCode
INNER JOIN Purchasing.Vendor AS Vendor
INNER JOIN Purchasing.VendorContact AS VendorContact
ON VendorContact.VendorID = Vendor.VendorID
INNER JOIN Person.Contact AS Contact
ON Contact.ContactID = VendorContact.ContactID
INNER JOIN Person.ContactType AS ContactType
ON VendorContact.ContactTypeID = ContactType.ContactTypeID
ON VendorAddress.VendorID = Vendor.VendorID

If we create a view using our second query and use our view in following style then execution plan will be same to our first query.

SELECT Name,
Title,
FirstName,
MiddleName
FROM vw_MyView
Hence, SQL Server Query Analyser is quite smart and work on only those tables which are part of actual game and it doesn’t matter that extra tables are part of your query or a view.