Connect SQL

SQL Server: Idera SQL Doctor

2012-01-31T18:20:00.003Z

If you don't know much about SQL Server internals and its performance tuning techniques, but still need your SQL Server performance, right upto the mark, then you must try SQL Doctor, a really helpful tool by Idera.

SQL doctor is a revolutionary technology that analyzes the performance of SQL Server and provides recommendations for improving performance.

SQL Server: Get All Databases Size

2012-01-10T18:10:00.000Z

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

2011-11-18T22:49:00.001Z

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

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

)

How to use it. Very Simple :)

SELECT * FROM PV_GetEmployeeInformationBySSN ('112457891')

SQL Server 2012: A New More Flexible Create Index Dialog box

2011-11-16T20:51:00.001Z

With every new version SQL Server is becoming morepower full yet more users friendly. Today, while creating index on SQL Server2012, I have found that Index Creation Dialog box is become more easy to use.

In prior versions, more irritating thing for me (atleast), was naming a non-clustered index. Now dialog box suggests you moremeaningful name. Secondly dialog box contains two tabs to add key columns andinclude columns.

In prior versions, a common error which I havealways faced is that key column can’t be listed as include column.

Now in SQL Server 2012, Index Create Dialog box willautomatically disable check boxes for those columns which are already added askey columns. Similarly, if a column is already added as include column it willbecome disable on key column selection tab.

Remaining options are almost same to SQL Server R2, while COMPRESSION option is removed.

SQL Server: TOP…WITH TIES a Beauty of TSQL

2011-11-15T21:54:00.001Z

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 2012: Introducing New Edition "Business Intelligence"

2011-11-04T22:50:00.000Z

Along with other major editions, Microsoft is now going to add a brand newbusiness-intelligence SKU and core-based licensing to its coming SQL Server2012 database offering due next year. Read complete story.

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

2011-11-03T19:39:00.000Z

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

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

CREATE PROCEDUREdbo.sp_fixusers

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

IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL

PRINT '<<<CREATED PROCEDURE dbo.sp_fixusers >>>'

ELSE

PRINT '<<< FAILEDCREATING PROCEDURE dbo.sp_fixusers >>>'

SQL Server 2011 (Denali): Changing Backup Files Default Path

2011-10-24T19:06:00.002Z

Up to SQLServer 2008 R2 (10.5), we were unable to change default path (easily) for “Backupfiles”, though it was possible after making some changes in registry at path. “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\ MSSQLServer\BackupDirectory”

Note: For SQL Server 2005 its MSSQL.1, for SQL Server 2008, its MSSQL10 and for SQL Server 2008 R2 its MSSQL10_50

Thanks toSQL Server 2011 (Code name DENALI), as now we can change this default path for backup filesto our required one by just opening server properties page and on “DatabaseSettings” tab.

Try Taking abackup from SSMS and now you can find that it has already pointing toward yougiven path.

SQL Server: How to Import Data from Pervasive

2011-10-18T19:09:00.000Z

To extract data frompervasive data files Pervasive database Engine is required. Latest Trialversion 11 can be downloaded from http://www.pervasivedb.com/psqlv11/pages/default.aspx

Once you have installedpervasive Engine, follow following steps to view and import it into SQL Server.

1. Open Control Center through programs>Pervasive> PSQL v11>control Center and documentation.

2. Make sure services pervasive PSQLRelational Engine and pervasive PSQL Transaction Engineare running (Expand services in Left panel)

3. To open a database Expand Engines>[server name] > Databases

4. Right Click on Databases and selectNew>Database

5. In Database Name Box give a name toyour database.

6. In Location Box locate the databasefiles path. (.mkd, .ddf, .dat)

7. You can uncheck “relationalintegrity enforced”.

8. Click Finish to complete the process.

9. Expand Database and you can see theadded database.

10. Double click on a table to see records

Moving data into SQLServer:

1. Right click on target database andselect Tasks> import data to open import and export wizard.

2. Hit next on the welcome screen.

4. To choose a data source click on thedrop down list and select Pervasive Provider, Release 3.2

5. Under Standard connection: specifypervasive Database Name, Host and server DSN as shownin figure below. DSN DEMODATA is automatically created byPervasive Engine.

6. Click next.

7. Chose SQL server native Client asDestination. Hit Next.

8. Click next

9. Provide Query to extract data from agiven table. Click parse to verify. Click next

10. Change Destination table name and click on EditMappings button. You can also preview source data.

11. In column mappings you can specify Field names,data type and size for destination table.

12. Click "OK" button to close Column mappings and hit finish toexecute the import process.

13. On successful completion refresh database in SQLServer to see imported data.

SQL Server: Using SQL Profiler to Capture Stored Procedure Call from Application

2011-10-17T17:38:00.000Z

SQL Profiler is a high-quality tool to figure outdifferent database issues, like “Which are costliest queries running”, “Whichquires acquired exclusive locks”, “Which indexes are missing” and soon. But in development environment and on production when solving a problem,developers like to use SQL Profiler to get exact procedure call which is beinggenerated by front end application.

Worst practice is, that developers like to useexisting built in templates for this purpose and normally use default one i.e. STANDARD. If you are also using SQL Profiler for this procedure call purpose, thenselecting STANDARD trace template is not a good choice, as on productionserver it affects its performance and even on development server it returnsmuch more extra information.

Good practice is, if you have not created your owntemplate then always select TUNING.

It also has some extra information so when you onlyneed to catch procedure calls generated from your application then click on “EventSelection” tab and keep only “RPC:Completed” event. You need not toselect “Sp: stmt Completed” as you just need to capture “execute procedure calls”and not all the statement inside this procedure. You can also omit “SP:Batch Completed” as we need calls that are only generated from application. If you also need to capture calls from SSMS then you can keep it.

To avoid extra work pressure on server and to getyour required results only, you must also apply filters on “Database Name”and “Text”

Use % sign, just like you use in LIKE operator.

Now run your trace and you will find your required results quickly and clearly, even without putting extra work load on database server.

SQL Server Denali: New Backup/Restore Options

2011-08-17T19:31:00.001Z

Backupprocess in SQL Server Denali is quite same to previous versions. But there arefew changes in restore dialog box.

Restore dialog box is now divided into three tabs instead of two. General tab is almost same to existing versions but options tab is further divided into “options” and “files” tabs.

A good thing about new restore page is “Backup Timeline”. Backup Timeline dialog box is useful to graphically locate and specify backups to restore a database to a point-in-time.For detail

http://blogs.msdn.com/b/wesleyb/archive/2011/07/18/restore-improvements-in-sql-server-denali-ctp3-management-studio.aspx

With SQL Server Denali, now you can restore corrupt pages.

SQL Server Denali: CTP 3 Product Guide Released

2011-08-11T23:04:00.002Z

Product guide of SQL Server DenaliCTP 3 is released and is available for free download (about 456 MB in size) from Microsoft Download Center. Guide is in formof an application and includes useful resources and demos that will help you inyour evaluation of CTP3. The said guide contains:

14 Product Datasheets
8 PowerPoint Presentations
5 Technical White Papers
13 Hands-On Lab Preview Documents
6 Click-Through Demonstrations
13 Self-Running Demonstrations
26 Links to On-Line References
44 Links to On-Line Videos including 26 Presentations from North America TechEd 2011

SQL Server Denali : TRY_CONVERT(), a Good Addition

2011-08-09T18:59:00.005Z

While working with different typeof data, I have not found a single day, without data type conversion error.Sometime error could be just because of a single row, out of million of correctrows. SQL Server never mentions which row is the culprit, it just throw anerror. Sometime you want to ignore those problematic rows but can’t because youhave no other option to found those wrong data.You can correct them manually or write anintelligent query. Consider following simple example

CREATE TABLE #TempTable (Val1 VARCHAR(10))

INSERT INTO #TempTable

SELECT '2.01' UNION ALL

SELECT 'A2.4' UNION ALL

SELECT '6.51' UNION ALL

SELECT '$37' UNION ALL

SELECT '56'

---Simple convert

SELECT CONVERT(float,Val1) AS Val1

FROM #TempTable

Result of above selectquery is an error, because row:2 data can’t be converted into float.

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to float.

So whatwill I do, so I can execute my query without any error.

SELECT CASE ISNUMERIC(Val1)

WHEN 1 THEN

CASELEFT(Val1,1)

WHEN'$' THEN NULL

ELSECONVERT(float,Val1) END

ELSE NULL END AS Val1

To avoid such error, with out writing long quires, SQL Server Denali has introduced a new function TRY_CONVERT(). Thisfunction try to convert values according to your given format and if failed itwill return NULL (instead of error). Let’s try this amazing function.

SELECT TRY_CONVERT(float,Val1) AS Val1

FROM #TempTable

SQL Server Denali: Partition Limit Enhancement

2011-08-08T18:46:00.000Z

Microsoft SQL Server Denali (CTP 3) supports up to 15,000 partitions by default. In earlier versions (upto SQL Server 2008), the number of partitions was limited to 1,000 by default. Though partition number was enhanced in SQL Server 2008 SP2, but for only Enterprise Edition.

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

2011-08-06T19:44:00.002Z

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"

For more information http://msdn.microsoft.com/en-us/library/hh213505%28v=SQL.110%29.aspx

SQL Server Denali: Get Date/Time from Parts

2011-08-04T19:46:00.009Z

SQL Server Denali has madedevelopers life quite easy by introducing new useful functions. We have alreadydiscussed EOMONTH() in anearly post.

DATEFROMPARTS() is a new function introduced in SQL Server Denali,which will help us to avoid lengthy code to get date from year, month and dayinputs. DATEFROMPARTS() takes three input parameters YEAR, MONTH and DAYand returns value in DATE format.

DATEFROMPARTS ( year, month, day )

DATEFROMPARTS() is not onlyfunction to get DATETIME values from parts but 5 other functions are alsointroduced.

TIMEFROMPARTS () which returns output in TIME format.

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

SMALLDATETIMEFROMPARTS() which returns output in SMALLDATETIME2 format.

SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

DATETIMEFROMPARTS() which returns output in DATETIME format.

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

DATETIME2FROMPARTS() which returns output in DATETIME2 format.

DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

DATETIMEOFFSETFROMPARTS() which returns output in datetimeoffset format.

DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

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

2011-08-03T17:33:00.001Z

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

2011-08-02T14:26:00.005Z

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 Denali: CTP3 Insallation Error "The Data is invalid"

2011-08-01T18:13:00.002Z

After donwloding SQL Server Denali from here, I thought, itwill take only few mintues to install but unfortunatly it took 3 hours. I triedevery thing but every time error was same “Data is invalid”

To install SQL Server Denali CTP 3 (at 32bit machine) you must have

Integrated_CT2776682.exe
SQLFULL_x86_ENU_Core.box
SQLFULL_x86_ENU_Install
SQLFULL_x86_ENU_Lang.box

For x64 name of files is same, just replace x86 with x64.

I found that “Data is invalid” error occurs when you havenot downloaded “SQLFULL_x86_ENU_Core.box” properly. So I you are also facingsame error, repeat your download process for only “SQLFULL_x86_ENU_Core.box”and re-execute SQLFULL_x86_ENU_Install.exe.

SQL Server: Extracting Data from OpenEdge Progress without OLE DB Driver

2011-07-25T18:59:00.003Z

Importing data from OpenEdge’sProgress® data base is simple if you have OLE DB data provider. But if youdon’t have OLE DB driver or unable to configure it properly then there are fewsimple steps to migrate data from OpenEdge’s Progress® to SQL Server.

(Worst thing in Progress ®database I found that you can only restore your database from backup file withonly version with which this backup files was created i.e. you can’t restoreProgress 9.1 backup file on Progress 10 or newer version)

We will achieve our data migrationgoal through two step, first to import table structures and import data fromflat files to newly created tables.

Copying Table Structuer:

Once you have restored yourrequired backup file, you can access it from “Data Administrator” by connectingyour database file.

Progress normally keep itsdatabases with extension “db” at installationdrive\Progress\WRK\YourDatabaseName.db

Once you are connected to yourdesired database, click on “ProgressDB to M SQL Server” through given path.

Provide necessary information forProgressDB to ODBC Conversion. Type any name of your choice for “Name of schemaholder database” and correct ODBC data source name.

On pressing OK button, it willcreate “.sql” file on “installation drive\Progress\WRK\”. This sql filecontains create table query for all database tables. Open this .sql file in SQLServer and create tables.

Extracting Data

In next step we will extract tabledata to CSV files.

Select table of your choice.

Provide file name with target folderpath. Select “All (Max 255)” fields to export. You can provide WHERE clause tofilter output rows. Press OK button to proced

Provide any record start string.It will add given string at start of each row. Which you can remove, once datais imported in SQL Server.

Now you have tables structure anddata in text format. Execute simple Data Import process to import your desireddata from text files to already created tables.

SQL Server: Import Data from Sybase Advatage (adt Files) using SSIS

2011-07-24T14:53:00.002Z

In last post, we have learned to free a Sybase Advantagetable (“adt” file) from its data dictionary, so we can import its data to SQLServer.

To extract these “adt” files by using SSIS package first weneed a “New OLE DB Connection”.

Select “Advantage OLE DB Provider” as provider and completefolder address in “Server or file name” section. Use “adssys” as username and leave password blank. Don't forget to change server type value to "ads_local_server", by clicking on "ALL" button on left of connection Manager. In Initial catalog provide “adt” filename and press “Test Connection ” to test newly created connection.

Drag a new OLE DB Source

Double click “OLE DB Source” to open it in editor andprovide OLE DB connection manager and name of required table.

Open properties page for “OLE DB Source” and mark TRUE to“AlwaysUseDefaultCodePage”

Now you are ready to import data from OLE DB Source to anytype of destination. You can provide OLE DB Destination to insert data directlyinto a SQL Server database. In this demo we have use Flat File as destination.

SQL Server: How to Import Data from Sybase Advantage (adt Files)

2011-07-16T18:36:00.003Z

Sybase Advantage database create separate files for eachtable and its indexes. Table files are created with extension “adt”. One canimport data from Sybase Advantage to SQL Server if she has

Accessto adt files
AdvantageOLE DB Provider

But import process is bit tricky because first you must freetarget “adt” files from its database dictionary, which can be achieved by usingAdvantageData Architect. It can be downloadedfrom http://devzone.advantagedatabase.com/dz/content.aspx?Key=20&Release=16&Product=8&Platform=6. Once you have install Advantage Data Architect, follow these step to freeyour target adt files.

1. Clickon new connection wizard

2. Createa connection to a directory of existing tables

3. ProvideDatabaseName of your choice and thenprovide correct path of folder where adt files are located on your hard driveand press finish.

4. Onceyou have created connection, it will start showing adt tables BUT still youcan’t open or export these tables as these are bound to directory.

5. Tofree these tables, click on Tools --> Free Data Dictionary Bound Tables

6. Provideadt file name with its complete pathand press OK button.

7. Youradt table is now free. Now you canopen it in Data Architect. You can export or you can close Data Architect andimport this table from SQL Server Import Wizard.

Note: In next post, we will explore a simple method to import adt files by using SQL Server Integration Services Package.

In next post: How to import adt files by using SSIS package

SQL Server: Where You Can Find Saved Import/Export SSIS Packages

2011-06-28T16:07:00.001Z

Using SQL Server Import Export Utility, have you ever saved this SSIS package to SQL Server ?

Recently, a friend of mine asked that after saving Import/Export process as SSIS package to SQL Server, Where he can find this saved package to re-execute it.

Once you have saved your SSIS package,open SQL Server Management studio and then on object explorer panel click on connect and select Integration Services. Use valid user name and password to connect.

Once connect, expend Stored Packages node and then expend MSDB node. Here you can find your saved package. Right click on it and select “Run Package” to re-execute your saved import/export steps.

SQL Serve Videos: Installing SQL Server and Creating Your First Database

2011-06-19T19:18:00.001Z

If it’s your first day with SQL Server and you need someone who can help you in installation and creation of your first database in SQL Server 2008, then following videos at youtube are uploaded for you.

Install SQL Server 2008 R2 Named Instance Quick & Simple.

Length. 3:03

Create Database in SQL Server 2008 R2 Quick & Simple

Length. 6:20

Installation of SQL Server 2005 is bit different as compared to new versions. If you are interested in old version, like SQL Server 2005 then, click on following link to get proper help.

SQL Server 2005 Video Tutorial - Installing

Length 9:55

As 90% functions of SQL Server Management Studio are same in 2005 and later versions, following video (which is based on SQL Server 2005) will be helpful for users who are facing SQL Server Management Studio very first time.

SQL Server 2005 Management Studio

Length 10:47

Once you have installed SQL Server and successfully connected to your first database then, next thing you must learn is T-SQL Queries

Getting Started with T-SQL Queries using SQL Server Management Studio

Length 8:57

SQL Server: Quickest Method to Create Single Table Backup

2011-06-09T17:39:00.001Z

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

2011-06-08T18:21:00.001Z

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.

SQL Server: Automatic Query Execution at Every Instance Startup

2011-06-03T21:34:00.000Z

Though production database servers are design to stay up for 24x7, but still when ever these production database servers go down and restart, sometime we need to execute some queries automatically on every start-up, like clean some setup tables or capture some sort of necessary data which is only available at instance start-up.

For such queries which need to be executed automatically at every start-up, we have to create a store procedure to encapsulate all these queries. Then automatic execution of this stored procedure is achieved by using the sp_procoption system stored procedure.

(Note: Best place to store such stored procedure is MASTER database)

Let’s create a stored procedure to store instance start-up time in a log table.

USE MASTER

--Create table to hold startup time

CREATE TABLE dbo.InstanceLog

(StartupTime DATETIME)

--Create stored procedure to execute on startup automatically

CREATE PROCEDURE dbo.Proc_InsertStartupTime

INSERT dbo.InstanceLog

SELECT GETDATE()

Now we will use SP_PROCOPTION to tell SQL Server that we want to execute our stored procedure at every instance start-up. Syntax will be as follow:

EXEC SP_PROCOPTION

@ProcName = 'Proc_InsertStartupTime',

@OptionName = 'STARTUP',

@OptionValue = 'TRUE'

After executing above statement, when ever SQL Server instance will restart, stored procedure will be executed automatically and a new row in our log table dbo.InstanceLog will be inserted.

To revert this option and to stop stored procedure from automatic execution, we will use following syntax.

EXEC sp_procoption

@ProcName = 'Proc_InsertStartupTime',

@OptionName = 'STARTUP',

@OptionValue = 'OFF'

(Applicable for SQL Server 2005 and above versions)

SQL Server: Changed Data Capture without Using Trigger

2011-06-02T19:51:00.002Z

Most of SQL Server users think that trigger is the only place where we can use DELETED and INSERTED virtual tables to capture data change in result of any INSERT, UPDATE or DELETE statement.

Old and new data values affected by DML operations can be captured by using OUTPUT clause along with virtual DELETED and INSERTED tables. Here are few examples which will be helpful to understand usage of this OUTPUT clause in simple insert, update and delete queries.

UPDATE:

USE AdventureWorks

-- Create a table variable to hold updated rows

DECLARE @UpdatedRecords TABLE

(AddressID INT, OldAddressLine2 VARCHAR(50), NewAddressLine2 VARCHAR(50))

UPDATE Person.Address

SET AddressLine2 = 'Silver Street new'

OUTPUT DELETED.AddressID, DELETED.AddressLine2,INSERTED.AddressLine2

INTO @UpdatedRecords

WHERE AddressID = 3

SELECT * FROM @UpdatedRecords

DELETE:

USE AdventureWorks

-- Create a table variable to hold deleted rows

DECLARE @DeletedRecords TABLE

(AddressID INT, AddressLine1 VARCHAR(50), AddressLine2 VARCHAR(50)

,City VARCHAR(50),StateProvinceID INT,PostalCode VARCHAR(10)

,rowguid UNIQUEIDENTIFIER,ModifiedDate DATETIME)

DELETE FROM Person.Address

OUTPUT DELETED.*

INTO @DeletedRecords

WHERE AddressID = 3

SELECT * FROM @DeletedRecords

INSERT:

INSERT INTO Person.Address

(AddressLine1 , AddressLine2 ,City ,StateProvinceID ,PostalCode

,rowguid ModifiedDate)

OUTPUT INSERTED.*

INTO @InsertedRecords

VALUES ('3rd BlackStone rd','wst.51','Bothell',78,98010,NEWID(),GETDATE()

Note: This method is applicable for SQL Server 2005 and above versions.

SQL Server: What I Have Learned in May 2011

2011-05-31T20:45:00.000Z

Dedicated to Most Energetic SQL Expert Gail Shaw

SQL Tips & Tricks:

SQL Server: Column Value with Leading Zeros

SQL Server: How to Remove Extra Spaces From String Value

SQL Server: How to Remove Special Characters

SQL Server: Conditional WHERE clause (Filter for a Filter)

Third Party Tools:

SQL Server: How SQL Diag Became Easy for Every DBA

SQL Server: Shortcuts for TSQL Code in SSMS

SQL Server: sp_WhoIsActive, a Must Tool for a DBA

SQL Server: Keeping Log of Each Query Executed Through SSMS

Performance Tuning:

SQL Server: Why to Avoid TRIM functions in WHERE and JOIN clauses

SQL Server: Short Code Doesn’t Mean Smart Code

SQL Server: Which Performs Better, IN or EXISTS

SQL Server Management Studio:

SQL Server: Query Template, a Time Saving Feature

Database Management Views & Functions:

SQL Server: Create/Drop Scripts for All Existing Foreign Keys

SQL Server: How to Get All Indexes List With Involved Columns Name

SQL Server: Create/Drop Scripts for All Existing Foreign Keys

2011-05-26T21:21:00.001Z

Today, when I need a script to get create and drop scripts of all existing foreign keys on a specific table (or in a whole database), I searched my query bank and find my required script quickly but on opening it I found that I was written for SQL Server 2000, time when there was no concept of schema, so I have made some changes so I can use it for SQL Server 2005 or 2008.

Create Foreign Keys:

SELECT 'ALTER TABLE ' + SCHEMA_NAME(F.schema_id) + '.'

+ OBJECT_NAME(F.parent_object_id) + ' ADD CONSTRAINT ' + F.name

+ ' FOREIGN KEY ' + '(' + COL_NAME(FC.parent_object_id,

FC.parent_column_id) + ')'

+ ' REFERENCES ' + SCHEMA_NAME(RefObj.schema_id) + '.'

+ OBJECT_NAME(F.referenced_object_id) + ' ('

+ COL_NAME(FC.referenced_object_id, FC.referenced_column_id) + ')'

FROM SYS.FOREIGN_KEYS AS F

INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.constraint_object_id

INNER JOIN sys.objects RefObj ON RefObj.object_id = f.referenced_object_id

--WHERE OBJECT_NAME(F.PARENT_OBJECT_ID) = 'YourObjectName'

Drop Foreign Keys

SELECT 'ALTER TABLE ' + SCHEMA_NAME(F.schema_id) + '.'

+ OBJECT_NAME(F.parent_object_id) + ' DROP CONSTRAINT ' + F.name

FROM SYS.FOREIGN_KEYS AS F

INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.constraint_object_id

--WHERE OBJECT_NAME(F.PARENT_OBJECT_ID) = 'YourObjectName'

SQL Server: Shortcuts for TSQL Code in SSMS

2011-05-25T17:35:00.002Z

For a developer or DBA it’s common to start their day with “SELECT * FROM” and in a daily routine work we type same lines of script many times. If you are a lazy developer or DBA like me then sometime it feels boring to type same code again and again. Intellisence in SQL Server 2008, saved lot of time but still it lacks few features, which other third party tools were offering before SQL Server 2008. Through such tools like SQL PROMPT, we have ability to type shortcuts in editor which after pressing Enter or Tab turn into a predefined code block. Like I will just type * and it will convert it to “SELECT * FROM”.

If you don’t like to purchase these tools but still want to make your life easy then you need SSMS Tools by Mladen Prajdić, totally free and you can download from here. Beside other good tools it has an option of SQL Snippets. Although it already have a good list of shortcuts but still you can add of your choice.

It has shortcuts like:

SSC = SELECT COUNT(*) FROM

SSF = SELECT * FROM

UPD = UPDATE <>

SET <>

FROM <>

SQL Server: Column Value with Leading Zeros

2011-05-24T16:23:00.000Z

Till date I was using old CASE option to add zeros before a digit to get all values with same length for columns like social security number but today SQL Expert Shakeeb Younas shared a very simple method to achieve above mentioned goal.

DECLARE @Emp TABLE ( SSN VARCHAR(9) )

INSERT INTO @Emp

SELECT '1'

UNION ALL

SELECT '654'

UNION ALL

SELECT '824741'

UNION ALL

SELECT '123456789'

UNION ALL

SELECT '37'

-- Get SSN with leading zeros

SELECT REPLACE(STR(SSN, 9), ' ', '0') AS SSN

FROM @Emp

SQL Server: Which Performs Better, IN or EXISTS

2011-05-23T17:43:00.003Z

It’s true that to solve a problem in TSQL you have many choices, and adopting any of these techniques you can get your desired results. Today we will discuss two very commonly used IN and EXISTS clause to filter query result set based on records from a sub-query. As compared to EXISTS, IN is more commonly used, but which one is more efficient? That’s what we are looking for, today.

IN and EXISTS perform in a same way if we use them with single column search. Like as follow:

USE AdventureWorks

--IN

SELECT * FROM Production.Product pr

WHERE ProductID IN

(SELECT ProductID FROM Purchasing.ProductVendor)

--EXISTS

SELECT * FROM Production.Product pr

WHERE EXISTS

(SELECT 1 FROM Purchasing.ProductVendor
WHERE ProductID = pr.ProductID)

Logical reads and query elapsed time is also same for both queries.

In situations where you need to filter records based on more than one columns existence in sub-query, you will find EXISTS much better in performance. To observe this, let’s create two temporary tables.

CREATE TABLE #Cars (Make VARCHAR(50), Color VARCHAR(30), Seats INT)

CREATE TABLE #CarIssuance (Make VARCHAR(50),Color VARCHAR(30),IssuanceDate DATETIME)

INSERT INTO #Cars

SELECT 'Honda','Black',2 union all

SELECT 'Honda','White',2 union all

SELECT 'Toyota','Black',4 union all

SELECT 'Toyota','Silver',4 union all

SELECT 'BMW', 'Red',2

INSERT INTO #CarIssuance

SELECT 'Honda','Black','2011-05-20' union all

SELECT 'BMW','Red','2011-05-03' union all

SELECT 'Toyota','Black','2011-05-03'

If we need to get all records from #Cars table, where records exists in #CarIssuance table on basis of “make” and “color” columns. Let’s first try traditional IN clause.

SELECT * FROM #Cars

WHERE Color IN

(SELECT Color FROM #CarIssuance)

AND Make IN

(SELECT Make FROM #CarIssuance)

Table '#CarIssuance Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0

Table '#Cars Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 40 ms.

Now execute same query with EXISTS option

SELECT * FROM #Cars Cr

WHERE

EXISTS

(SELECT 1 FROM #CarIssuance CI

WHERE Make = Cr.Make

AND Color = Cr.Color)

Table '#CarIssuance Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0

Table '#Cars Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

Exists performed much better (We got result in 1ms instead of 40ms) just because we have used single sub-query but in case of “IN”, we have used multiple sub-queries. So if somehow, we change our first query of “IN” clause so that we can use only one sub-query, performance will be same as to EXISTS. But code will be bit mixed up and in real life sometime even its not possible.

SELECT * FROM #Cars Cr

WHERE Color IN

(SELECT Color FROM #CarIssuance WHERE Make = Cr.Make)

Summary: Exists performs much better when used for more then one column filter from a sub-query but as a good programming practice EXISTS must be preferred even when handling with single column filter.

SQL Server: How to Remove Special Characters

2011-05-20T18:33:00.000Z

One more useful function from my query bank, which I like to use to remove special characters from a string.

CREATE FUNCTION dbo.RemoveSpecialChars ( @InputString VARCHAR(8000) )

RETURNS VARCHAR(8000)

BEGIN

IF @InputString IS NULL

RETURN NULL

DECLARE @OutputString VARCHAR(8000)

SET @OutputString = ''

DECLARE @l INT

SET @l = LEN(@InputString)

DECLARE @p INT

SET @p = 1

WHILE @p <= @l

BEGIN

DECLARE @c INT

SET @c = ASCII(SUBSTRING(@InputString, @p, 1))

IF @c BETWEEN 48 AND 57

OR @c BETWEEN 65 AND 90

OR @c BETWEEN 97 AND 122

--OR @c = 32

SET @OutputString = @OutputString + CHAR(@c)

SET @p = @p + 1

END

IF LEN(@OutputString) = 0

RETURN NULL

RETURN @OutputString

END

How to use it.

SELECT dbo.RemoveSpecialChars ('This string contains special chracters:/ Which * & we % need @ to #remove')

According to this function space is a special character and if you want to ignore spaces then uncomment “OR @c = 32”.

SQL Server: How to Remove Extra Spaces From String Value

2011-05-19T15:40:00.004Z

Extra spaces between characters of a string value is a common problem and if you’re a developer then you must have faced the problem. On request of a blog reader here is a script from my query bank which I like to use to remove such extra spaces.

--Create a temp table for testing our query

CREATE TABLE #ExtraSpaces ( MyVal VARCHAR(8000))

--Insert some value to test

INSERT INTO #ExtraSpaces

SELECT 'This is my message. '

UNION ALL

SELECT 'This message contains tabs and extra spaces'

-- Lets remove extra spaces and tabs

WHILE 1 = 1

BEGIN

UPDATE #ExtraSpaces

SET MyVal = REPLACE(

SUBSTRING(MyVal, 1,

CHARINDEX(' ', MyVal, 1) - 1) + ' '

+ LTRIM(

SUBSTRING(MyVal,

CHARINDEX(' ', MyVal, 1), 8000)),' ',' ')

WHERE CHARINDEX(' ', MyVal, 1) > 0

IF @@rowcount = 0

BREAK

END

--Lets see the updated result

SELECT MyVal FROM #ExtraSpaces

--drop temp table when not required

DROP TABLE #ExtraSpaces

SQL Server: Query Template, a Time Saving Feature

2011-05-18T16:25:00.002Z

While working with SQL Server, its common to have a saved script which you need to execute for different server or databases and usually you open this saved script, make changes according to requirements and execute. SQL Server 2008 has one more time saving facility. You can save your script as template, like following is a simple query script, which I like to execute for different databases and with different order by clause.

USE AdventureWorks

GO

SELECT     s.SalesPersonID, c.Title, c.FirstName,

c.MiddleName, c.LastName, c.Suffix,e.Title AS JobTitle,s.SalesQuota, s.SalesYTD

FROM        Sales.SalesPerson AS s INNER JOIN

HumanResources.Employee AS e

ON e.EmployeeID = s.SalesPersonID

LEFT OUTER JOIN

Sales.SalesTerritory AS st

ON st.TerritoryID = s.TerritoryID

INNER JOIN

Person.Contact AS c

ON c.ContactID = e.ContactID

ORDER BY c.FirstName

One solution to avoid any labor work to change above script manually according to my requirements, is to create a dynamic query. But, in SQL Server 2008 I would prefer Query Template, by saving above query in following Template.

USE

GO

SELECT     s.SalesPersonID, c.Title, c.FirstName,

c.MiddleName, c.LastName,

c.Suffix,e.Title AS JobTitle,s.SalesQuota, s.SalesYTD

FROM        Sales.SalesPerson AS s INNER JOIN

HumanResources.Employee AS e

ON e.EmployeeID = s.SalesPersonID

LEFT OUTER JOIN

Sales.SalesTerritory AS st

ON st.TerritoryID = s.TerritoryID

INNER JOIN

Person.Contact AS c

ON c.ContactID = e.ContactID

ORDER BY

Next time when I need to execute this script, I will just open the template and press Ctlr+Shift+M or click on replace button, and it will ask me for template parameters value and on pressing OK button it will replace template parameters with my values on whole script.

Defining a template needs three parameters separated with comma and enclosed with < and > signs.

<TemplateParameter, ParameterDataType, DefaultValue>

Replace Button

SQL Server: sp_WhoIsActive, a Must Tool for a DBA

2011-05-13T18:50:00.001Z

A mail with subject “Low Database Response” or a call with words “What happened to the database?” is normal in a SQL Server DBA’s life. To cope with such bad situations normally a DBA keeps his drawer filled with necessary tools and scripts. But like me, most of DBAs first query to check “What is happening actually” is shortest query in SQL Server i.e. sp_who or sp_who2. Second returns almost same data to sp_who but with more detail information.

sp_who

sp_who2

Though both system stored procedures are helpful to collect basic information to find out problematic sessions of database but to reach your desired rows, you have to skim through large number of rows and you will find more then 95% of rows useless. Some time information provided by sp_who or sp_who2 is not enough to understand the real problem which leads you to query some other DMVs for such information.

Thanks to Adam Machanic who resolved this problem. His stored procedure WhoIsActive is a must tool for a DBAs kit. This single stored procedure collects almost all necessary information for a DBA, which will be helpful to understand the real problem of a database. The beauty of this stored procedure is that it collects information using almost 15 DMVs but still returns only necessary rows.

SQL Server: Short Code Doesn’t Mean Smart Code

2011-05-10T16:05:00.004Z

Recently a blog reader shared an interesting thing. This mail was basically in response to my early post Why to Avoid TRIM functions in WHERE and JOIN clauses, where we have discussed, that why we should avoid functions (user defined or system) in where clause columns, because these functions in WHERE, JOIN and GROUP clauses mislead query optimizer for proper index selection and ultimately results in poor query performance.

Blog reader asked that he tried to remove functions from WHERE clause of all the queries but few queries where date was involve, was hard to correct and after asking at some forum he got a solution and now his code is shorter and quicker.

Actual query was something like as following:

Use AdventureWorks

DECLARE @FindDate DATETIME

SET @FindDate = '2005/09/12 12:00:00'

SELECT *

FROM Purchasing.PurchaseOrderDetail

WHERE CAST(CONVERT(VARCHAR(30), ModifiedDate, 101) AS DATETIME) = CAST(CONVERT(VARCHAR(30), @FindDate, 101) AS DATETIME)

It’s an everyday query, where records from “Purchasing.PurchaseOrderDetail” table are required but where modifieddate column values are equal to given parameter (date). But comparison should be based on date only and time portion should be ignored.

Modified smart query :

SELECT *

FROM Purchasing.PurchaseOrderDetail

WHERE DATEDIFF(DD,ModifiedDate,@FindDate) = 0

Though new query is shorter, but is it quick? Let’s checkout input/output statistics, query time and execution plan for both quires.

Table 'PurchaseOrderDetail'. Scan count 1, logical reads 66, physical reads 0

CPU time = 0 ms, elapsed time = 11 ms.

Table 'PurchaseOrderDetail'. Scan count 1, logical reads 66, physical reads 0

CPU time = 0 ms, elapsed time = 9 ms

Though second query looks more smart but if we ignore minor difference of query elapsed time, both query are almost same as both queries are using clustered index scan and have same value of logical reads. Because problem still exists i.e. Function on WHERE clause columns. For best query performance we have to get rid of this DATEDIFF function too. Here is a better version, as per performance and not the code because our first priority should be performance.

SELECT *

FROM Purchasing.PurchaseOrderDetail

WHERE ModifiedDate >= CAST(

CONVERT(VARCHAR(30), @FindDate, 101) AS DATETIME)

AND ModifiedDate < = DATEADD(SS, 86399,

CAST(CONVERT(VARCHAR(30), @FindDate, 101) AS DATETIME))

Query code is even more lengthy then first version but what about performance, lets check out input/output stats with query time.

Table 'PurchaseOrderDetail'. Scan count 1, logical reads 4, physical reads 0

CPU time = 0 ms, elapsed time = 1 ms.

From execution plan, it’s clear that after removing functions from modifieddate column (used in WHERE clause), query optimizer selected proper non clustered index, which searched only 4 pages for result and finally query performance is increased.

SQL Server: Conditional WHERE clause (Filter for a Filter)

2011-05-06T16:34:00.001Z

No one is unaware of WHERE clause. Everyone knows that we can filter output records by using WHERE clause but recently I found my team member stuck when he need a Conditional WHERE clause (filter for a filter). Let’s try to figure out the problem and its solution with a simple scenario.

Suppose we have a table to keep students result with follow structure

CREATE TABLE #Result

(

StudentId INT,

TeacherId INT,

GroupId INT,

Result VARCHAR(10),

MarksObtained INT

)

INSERT INTO #Result

SELECT 101,1,1,'PASS',510 UNION ALL

SELECT 102,1,1,'PASS',622 UNION ALL

SELECT 103,2,1,'FAIL',174 UNION ALL

SELECT 104,2,2,'PASS',652 UNION ALL

SELECT 105,3,2,'FAIL',134

Our requirement is to create a stored procedure with only two parameters, one for id (it could be student, teacher or group id), we will call it @id and other to hold information that will decide that what type of id is being passed to stored procedure i.e. student, teacher or group, we will call it @idType

DECLARE @Id INT -- It could be StudentId,TeacherId,GroupId

DECLARE @IdType VARCHAR(10) -- Type could be Student,Teacher or Group

We need a query which can be used for all three criteria

SELECT @Id = 2, @IdType= 'Teacher'

--OR-- @Id = 102, @IdType= 'Student'

--OR-- @Id = 1, @IdType= 'Group'

Let’s move to our targeted query, with conditional where clause.

SELECT * FROM #Result

WHERE 1 = (CASE

WHEN @IdType='Student' AND StudentId = @Id

THEN 1

WHEN @IdType='Teacher' AND TeacherId = @Id

THEN 1

WHEN @IdType='Group' AND GroupId = @Id

THEN 1

ELSE 0 END)
(Note: TSQL is a rich query language, a problem can be solved by several methods. Above query is just written to show conditional WHERE clause)

SQL Server: How SQL Diag Became Easy for Every DBA

2011-05-05T19:45:00.001Z

SQL Diag is a useful diagnostic tool, through which we can collect lot of useful information, like

Windows performance logs
Windows event logs
SQL Server Profiler traces
SQL Server blocking information
SQL Server configuration information

Only problem with this tool is that you have to configure an XML file to provide information to SQL Diag that basically decides that what type of information you want. Though a default XML file is provided with tool as an example but it is not according to everyone’s requirement and somehow you have to change this XML file to get proper information.

Like me, most of DBAs and Developers are not good with XML, and that is why unable to use SQL Diag according to their needs. But recently this problem is solved by providing a simple SQL Diag Configuration utility, through which you can configure input XML file according to your requirements BUT just using your mouse and you need not to know a single line of XML.

You can download it from here.

SQL Diag Configuration Tool can be used for SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2.

Provide your machine name , instance and a valid user credentials. But user must be a member of Windows Administrators group and a member of the SQL Server sysadmin fixed server role.

Select counters of your own choice and in the end save file as XML at your desired path

Next, on command shell type following line and press enter to start information collection process.

C:/> sqldiag /I "SD_Detailed.xml" /O "C:\SQLDiagOutPut" /N2

Here first part /I "D:\SQLDiag100.XML" is input XML file path, which we already generated through SQLDiag Configuration Tool. And second part /O "D:\SQLDiagOutPut" is path and folder name where you want to place your output files. While third parameter /N is an output folder management parameter.

There are two options for /N parameter

N1: Automatically overwrite the output folder data

N2: Do not overwrite existing data, rather it keeps history of existing output by automatically creating folder like SQLDiag_00000, SQLDiag_00001 and so on.

SQL Server: How to Get All Indexes List With Involved Columns Name

2011-05-04T20:42:00.002Z

Recently a friend of mine asked for a script, for documentation purpose which can help them to create all of their indexes list with column names used in each index. I thought, I must share this simple script with my blog readers.

SELECT Tab.[name] AS TableName,

Ind.[name] AS IndexName,

SUBSTRING(( SELECT ', ' + AC.name

FROM sys.[tables] AS T

INNER JOIN sys.[indexes] I

ON T.[object_id] = I.[object_id]

INNER JOIN sys.[index_columns] IC

ON I.[object_id] = IC.[object_id]

AND I.[index_id] = IC.[index_id]

INNER JOIN sys.[all_columns] AC

ON T.[object_id] = AC.[object_id]

AND IC.[column_id] = AC.[column_id]

WHERE Ind.[object_id] = I.[object_id]

AND Ind.index_id = I.index_id

AND IC.is_included_column =0

ORDER BY IC.key_ordinal

FOR

XML PATH('')

), 2,8000) AS KeyCols,

SUBSTRING(( SELECT ', ' + AC.name

FROM sys.[tables] AS T

INNER JOIN sys.[indexes] I

ON T.[object_id] = I.[object_id]

INNER JOIN sys.[index_columns] IC

ON I.[object_id] = IC.[object_id]

AND I.[index_id] = IC.[index_id]

INNER JOIN sys.[all_columns] AC

ON T.[object_id] = AC.[object_id]

AND IC.[column_id] = AC.[column_id]

WHERE Ind.[object_id] = I.[object_id]

AND Ind.index_id = I.index_id

AND IC.is_included_column =1

ORDER BY IC.key_ordinal

FOR

XML PATH('')

), 2,8000) AS IncludeCols

FROM sys.[indexes] Ind

INNER JOIN sys.[tables] AS Tab

ON Tab.[object_id] = Ind.[object_id]

ORDER BY TableName

SQL Server: Why to Avoid TRIM functions in WHERE and JOIN clauses

2011-05-03T16:48:00.000Z

Just creating indexes on JOIN, WHERE and GROUP clause columns doesn’t mean that your query will always return your required results quickly. It is query optimizer which selects proper index for a query to give you an optimum performance but query optimizer can only suggest optimum query plan by using proper indexes WHEN your are helping it by writing good query syntax.

Using any type of function (system or user defined) in WHERE or JOIN clause can dramatically decrease query performance because this practice create hurdles in query optimizer work of proper index selection. One common example is TRIM functions, which are commonly used by developers in WHERE clause. For more understandings, let’s compare performance of two queries, one with TRIM function in WHERE clause and other one without TRIM functions.

USE AdventureWorks

SELECT pr.ProductID,pr.Name,pr.ProductNumber,wo.* fROM Production.WorkOrder wo

INNER JOIN Production.Product pr

ON PR.ProductID = wo.ProductID

WHERE LTRIM(RTRIM(pr.name)) = 'HL Mountain Handlebars'

SELECT pr.ProductID,pr.Name,pr.ProductNumber,wo.* fROM Production.WorkOrder wo

INNER JOIN Production.Product pr

ON PR.ProductID = wo.ProductID

WHERE pr.name = 'HL Mountain Handlebars'

Though outputs of both queries are same but first query took almost 99% of total execution time. This huge difference is just because of these trim functions so on production databases we must avoid these TRIM and other functions in both JOIN and WHERE clauses.

SQL Server: Keeping Log of Each Query Executed Through SSMS

2011-05-02T15:45:00.001Z

During a normal working day, a DBA or Developer executes countless queries using SQL Server Management Studio. Some of these queries, which are thought important, are saved and roughly 80% of query windows are closed without pressing save button. But after few minutes, hours or even days, most DBAs and Developers like me want their quires back, which they have executed but can’t save.

SQL Server Management Studio has no such feature through which we can get our unsaved queries back. If you need to keep log of each query you have executed in SSMS then you must install free tool “SSMS Tools Pack 1.9” by Mladen Prajdić, which contains “Query Execution History” and much more.

SQL Server: What I have Learned in April 2011

2011-05-01T19:16:00.000Z

Dedicated to Unbeaten Hero of SQL Server Community Jacob Sebastian

TSQL Tips & Tricks

SQL Server: Float to Varchar Conversion

SQL Server: Working With Date but With Format of Your Own Choice

SQL Server: Sorting Data in Ascending Order but NULL at the End

SQL Server Basics: TO_DATE() Function in SQL Server

SQL Server: LEN() or DATALENGTH()

SQL Server: Two Misconceptions about CHAR Data type

SQL Server Management:

SQL Server: How to Manage Error Log File on Production Server

SQL Server: How to Read Trace File to Detect Database Detachment

SQL Server: Script to Create Replica of Existing Database User

SQL Server: NULL As Nothing vs NULL As String Value in SSMS

Performance Tuning:

SQL Server: How to Analyze Ad Hoc Queries Workload

SQL Server: High Logical Reads for Strange Worktable

SQL Server Community:

SQL Server: A Query Slow in Application but Fast in SSMS

SSIS: Opportunity to Learn SQL Server Integration

SQL Server: Float to Varchar Conversion

2011-04-29T16:31:00.006Z

Recently, someone asked that, if we convert float value to varchar then result is truncated and what is the best way to get whole value converted into varchar without any truncation.

DECLARE @FloatVal FLOAT

SELECT @FloatVal = 421.765433

First lets check, how SQL Server implicitly converts float value to varchar

DECLARE @VarcharVal VARCHAR(50)

SELECT @VarcharVal = @FloatVal

SELECT @VarcharVal

OOPS. Implicit conversion from float to varchar is NOT according to our required output. Let convert it explicitly and then assign this value to varchar variable.

SELECT @VarcharVal = CAST(@FloatVal AS VARCHAR(50))

SELECT @VarcharVal

You will observe same truncated value as a result of above query. Finally,let’s try it with convert as convert supports some formatting too.

SELECT CONVERT (VARCHAR(50), @FloatVal,128)

Yeah, that's what we were looking for.

SQL Server: How to Manage Error Log File on Production Server

2011-04-28T18:29:00.001Z

In one of my early post “How to Avoid Big Single Error Log File on Production Servers”, I have tried to explain that how error log file growth can be controlled to avoid very large files on production servers.

Though the post was very short but response was really great. Out of received comments, today I would like to share one of SQL Expert Martin C. suggestions on this topic.

“If you are monitoring your log on a daily basis for specific errors then the need to backup and keep old logs simply becomes dependent upon any compliance regulations. If you are not constrained by any compliance (e.g. SOX etc) then once you have examined the logs for any useful messages they become less useful for anything else.

You could make use of tools to parse the log entries and store anything significant in a database table so you retain these for later analysis, but messages that regularly appear (such as a backup completed) are really of no further use once you have confirmed the backup was successful especially as you have the information also stored in MSDB.

I'd suggest you ensure you have sufficient monitoring and checking of the error logs and perhaps even manually issue sp_cycle_errorlog after you have checked them.”

SQL Server: Working With Date but With Format of Your Own Choice

2011-04-27T18:09:00.001Z

Today, a blog reader asked that, why he is facing error for following simple varchar to datetime conversion query

SELECT CAST('24/04/2011' AS DATETIME)

Msg 242, Level 16, State 3, Line 1

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

But same query works fine, once he changes date and month position in given text.

SELECT CAST('04/24/2011' AS DATETIME)

By default SQL Server expects date in MDY or it works fine with MYD and YMD. And if your date format is other then above three formats, then you have to inform SQL Server about your date format by using SET statement.

SET DATEFORMAT dmy

SELECT CAST('24/04/2011' AS DATETIME)