Comparing Files from Different Folders Using SQL Server and XML

Problem
Sometimes there is a need to process files in a folder, but first you need to determine which files need to be processed compared to older files that have already been processed. There are several ways that this can be done, but in this tip I show you a way this can be done using SQL Server and XML.
To further illustrate the issue I have set this up into three folders:
Source - files to be processed
Destination - files that have been processed
NewFiles - holding folder for new files to be processed after they have been compared
Elaborating the problem in depth, let’s say I have three text files (or any type of file) in the Source folder and the Destination folder contains two files. The problem is to identify differences between the files in the Source and Destination folders and copy all the modified or new files from the Source Folder to the folder NewFiles.
Shown below are the set of files with their modified date in the Source folder:
Shown below are the set of files with their modified date in Destination folder:
Looking at the images above, it is clear that Source Folder contains one extra file ‘text3’ and one modified file ‘text2’.
I want to compare the files in these two folders and copy the modified file (text2) and the new file (text3) into folder NewFiles.
SolutionMy first task was to get all the files from the specified folder. This can be achieved via the ‘DIR’ command of DOS. DOS commands can be executed in SQL Server through xp_cmdshell system stored procedure, but it needs to be configured first.
The below code creates a new stored procedure called "MakeXML" which will create a XML document of all of the files in a folder. The code does a few things which you can either turn on or off as desired.
It first drops stored procedure MakeXML so it can create a new sp with this name
It enables xp_cmshell so we can shell out to DOS to get the folder listing
It does a DIR command against the specified folder with the /O-D parameter to order the list by modified date
It then inserts the data into a temporary table
Deletes any rows from this table that are not files that we want to compare
Parses the modified date
Parses the filename
Then it creates this data as a temporary XML object
IF OBJECT_ID (N'MakeXML') IS NOT NULL DROP PROCEDURE MakeXML GO -------INPUT THE FILEPATH FOLDER AND IT WILL RETURN AN XML CREATE PROCEDURE MakeXML @FilePath VARCHAR(2000), @xmlFile XML OUTPUT AS DECLARE @myfiles TABLE (MyID INT IDENTITY(1,1) PRIMARY KEY,FullPath VARCHAR(2000),ModifiedDate datetime, FileName VARCHAR(100)) DECLARE @CommandLine VARCHAR(4000) ----Configure xp_cmdshell in 2005 -- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 -- To update the currently configured value for advanced options. RECONFIGURE -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 -- To update the currently configured value for this feature. RECONFIGURE ----- run dir command on the path specified by ordering on the date modified SELECT @CommandLine =LEFT('dir "' + @FilePath + '" /O-D',10000) ------use xp_cmdshell stored procedure to run dos commands INSERT INTO @MyFiles (FullPath) EXECUTE xp_cmdshell @CommandLine ------Delete all the files that are other than .txt or can specify the file type you want to compare on DELETE FROM @MyFiles WHERE fullpath IS NULL OR Fullpath NOT LIKE '%.txt%' ------Separate the modified date from the fullpath UPDATE @myfiles SET ModifiedDate=SUBSTRING(fullpath,1,PATINDEX('%[AP]M%',fullpath)+1) ------Separate out FileName from fullpath UPDATE @myfiles SET FileName=SUBSTRING(fullpath,40,LEN(fullpath)) -----Make an xml out of separated ModifiedDate,FileName columns and SET the OUTPUT parameter SELECT @xmlFile=(SELECT ModifiedDate,FileName FROM @MyFiles FOR XML PATH('FILE'), ROOT('ROOT') ) GO
For our example the stored procedure would return XML for the Source folder ordered on modified date as shown below.
For our example the stored procedure would return XML for the Destination folder ordered on modified date as shown below.
Now, the next task is to compare the two XML documents from Source and Destination, this is achieved by the following stored procedure.
It takes three input parameters
@Sourcepath which is ‘D:\Source’
@DestPath is ‘D:\Destination’ and
@NewFilesPath as ‘D:\NewFiles’
IF OBJECT_ID (N'CompareXML') IS NOT NULL DROP PROCEDURE CompareXML GO --------COMPARE XML CREATE PROCEDURE CompareXML @SourcePath VARCHAR(255), @DestPath VARCHAR(255), @NewFilesPath VARCHAR(255) AS DECLARE @SourceXML AS XML,@DestXML AS XML ---Call the MakeXML sp to return the XML for Source and Destination Folders EXECUTE MakeXML @SourcePath,@SourceXML OUTPUT EXECUTE MakeXML @DestPath,@DestXML OUTPUT DECLARE @CURSOR_File AS CURSOR ---------Join both the tables retrieved from xml on filename and get all the files having modifieddate mismatch ---------Fetch all the values into a cursor SET @CURSOR_File= CURSOR FOR SELECT A.FNAME FROM ( SELECT N.value('ModifiedDate[1]','varchar(100)') AS MDATE , N.value('FileName[1]','varchar(100)') AS FNAME FROM @SourceXML.nodes('ROOT/FILE') AS X(N) )A INNER JOIN ( SELECT N.value('ModifiedDate[1]','varchar(100)') AS MDATE , N.value('FileName[1]','varchar(100)') AS FNAME FROM @DestXML.nodes('ROOT/FILE') AS X(N) )B ON A.FNAME=B.FNAME AND A.MDATE<>B.MDATE UNION ----------Fetch all the extra files in the source folder SELECT A.FNAME FROM ( SELECT N.value('ModifiedDate[1]','varchar(100)') AS MDATE , N.value('FileName[1]','varchar(100)') AS FNAME FROM @SourceXML.nodes('ROOT/FILE') AS X(N) )A LEFT OUTER JOIN ( SELECT N.value('ModifiedDate[1]','varchar(100)') AS MDATE , N.value('FileName[1]','varchar(100)') AS FNAME FROM @DestXML.nodes('ROOT/FILE') AS X(N) )B ON A.FNAME=B.FNAME WHERE b.fname IS NULL DECLARE @file AS VARCHAR(255) DECLARE @Command AS VARCHAR(500) --------First copy all the files found mismatched on modified date into @NewFilesPath OPEN @CURSOR_File FETCH NEXT FROM @CURSOR_File INTO @file WHILE @@Fetch_Status=0 BEGIN SET @Command='xcopy "'+@SourcePath+'\'+ @file+ '" "' + @NewFilesPath + '"' EXEC xp_cmdshell @Command,no_output FETCH NEXT FROM @CURSOR_File INTO @file END CLOSE @CURSOR_File DEALLOCATE @CURSOR_File GO
The CompareXML stored procedure above will first call the ‘MakeXML’ sp and get the source and destination xml. It will fetch all the values from the nodes of both the XML documents and make two types of comparison on those.
First the comparison would be on the basis of filename having different modified date values.
The second comparison will fetch all the files from Source folder that do not exist in Destination folder.
The output of this is put into a Cursor so that such files are then copied to the ‘NewFiles’ folder one by one using the DOS xcopy command.
After the two stored procedures have been created the code is executed as follows:
EXECUTE CompareXML @SourcePath='D:\Source', @DestPath='D:\Destination', @NewFilesPath='D:\NewFiles'
Based on our example the "NewFiles" folder will now look like this.
The NewFiles folder has the same set of files which had a different modified date and the file which was extra in the Source folder. Now, just copy the files from this NewFiles folder to Destination folder.
The problem of comparing files based on modified date and filename has been drastically reduced compared to doing it manually.

Granting View Definition Permission to a User or Role

In SQL Server 2005 by default users of a database that are only in the public role can not see the definitions of an object while using sp_help, sp_helptext or the object_definition function. Sometimes this is helpful to allow developers or other non-administrators to see the object definitions in a database, so they can create like objects in a test or development database. Instead of granting higher level permissions, is there a way to allow users that only have public access the ability to see object definitions?


When issuing sp_help, sp_helptext or using the object_definition() function the following errors in SQL 2005 will occur if the user does not have permissions to the see the object metadata. Here are a couple of examples of these errors.

EXEC sp_help Customer
Msg 15009, Level 16, State 1, Procedure sp_help, Line 66
The object 'Customer' does not exist in database 'MSSQLTIPS' or is invalid for this operation.

A select against the OBJECT_DEFINITION function will return a value of NULL if the user does not have permissions to see the meta data.

SELECT object_definition (OBJECT_ID(N'dbo.vCustomer'))
NULL

By default users were able to see object definitions in SQL Server 2000, but in SQL Server 2005 this functionality was removed to allow another layer of security. By using a new feature called VIEW DEFINITION it is possible to allow users that only have public access the ability to see object definitions.

To turn on this feature across the board for all databases and all users you can issue the following statement:

USE master
GO
GRANT VIEW ANY DEFINITION TO PUBLIC

To turn on this feature across the board for all databases for user "User1" you can issue the following statement:

USE master
GO
GRANT VIEW ANY DEFINITION TO User1

To turn this feature on for a database and for all users that have public access you can issue the following:

USE AdventureWorks
GO
GRANT VIEW Definition TO PUBLIC

If you want to grant access to only user "User1" of the database you can do the following:

USE AdventureWorks
GO
GRANT VIEW Definition TO User1

To turn off this functionality you would issue the REVOKE command such as one of the following:

USE master
GO
REVOKE VIEW ANY DEFINITION TO User1

-- or

USE AdventureWorks
GO
REVOKE VIEW Definition TO User1


If you want to see which users have this access you can issue the following in the database.

USE AdventureWorks
GO
sp_helprotect

Here are two rows that show where the VIEW DEFINITION action has been granted. The first on a particular object and the second for all objects in the database.





To take this a step further, if you do not want to grant this permission on all objects the following stored procedure can be used to grant this to all objects or particular objects in a database. This is currently setup for all object types, but this can be changed by including less object types in the WHERE clause.

WHERE type IN ('P', 'V', 'FN', 'TR', 'IF', 'TF', 'U')
/*
Included Object Types are:
P - Stored Procedure
V - View
FN - SQL scalar-function
TR - Trigger
IF - SQL inlined table-valued function
TF - SQL table-valued function
U - Table (user-defined)
*/


To use this, you can create this stored procedure in your user databases and then grant the permissions to the appropriate user instead of making things wide open for a user or all users. Just replace ChangeToYourDatabaseName for your database before creating.

USE ChangeToYourDatabaseName
GO
CREATE PROCEDURE usp_ExecGrantViewDefinition
(@login VARCHAR(30))
AS
/*
Included Object Types are:
P - Stored Procedure
V - View
FN - SQL scalar-function
TR - Trigger
IF - SQL inlined table-valued function
TF - SQL table-valued function
U - Table (user-defined)
*/
SET NOCOUNT ON

CREATE TABLE #runSQL
(runSQL VARCHAR(2000) NOT NULL)

--Declare @execSQL varchar(2000), @login varchar(30), @space char (1), @TO char (2)
DECLARE @execSQL VARCHAR(2000), @space CHAR (1), @TO CHAR (2)

SET @to = 'TO'
SET @execSQL = 'Grant View Definition ON '
SET @login = REPLACE(REPLACE (@login, '[', ''), ']', '')
SET @login = '[' + @login + ']'
SET @space = ' '

INSERT INTO #runSQL
SELECT @execSQL + schema_name(schema_id) + '.' + [name] + @space + @TO + @space + @login
FROM sys.all_objects s
WHERE type IN ('P', 'V', 'FN', 'TR', 'IF', 'TF', 'U')
AND is_ms_shipped = 0
ORDER BY s.type, s.name

SET @execSQL = ''

Execute_SQL:

SET ROWCOUNT 1

SELECT @execSQL = runSQL FROM #runSQL

PRINT @execSQL --Comment out if you don't want to see the output

EXEC (@execSQL)

DELETE FROM #runSQL WHERE runSQL = @execSQL

IF EXISTS (SELECT * FROM #runSQL)
GOTO Execute_SQL

SET ROWCOUNT 0

DROP TABLE #runSQL

GO

Once this procedure has been created you can grant the permissions as follows. This example grants view definition to a user "userXYZ" in "MSSQLTIPS" Database for all object types that were selected.

USE MSSQLTIPS
GO
EXEC usp_ExecGrantViewDefinition 'userXYZ'
GO

Next Steps

Getting the Most out of SQL Server 2000's Query Analyzer, Part I

Introduction

The first time I started up Query Analyzer after upgrading my client tools from SQL Server 7 to 2000, I noticed the Object Browser immediately. Playing around a bit, I tinkered with the Transact SQL debugger and generally just explored some of the new functionality. Most of it was quite useful, but not exactly straight-forward. When I began looking into getting more out of some of these new features, I found the documentation on Query Analyzer was intermingled with the rest of Books Online and I thought then how it could easily be overlooked. About a month ago, I was talking with a developer about some issues he was having debugging code, looking at execution plans, and trying to determine the best places to put indexes. I started asking him some questions about how the development team was using Query Analyzer and quickly realized that they saw it basically as an environment to type in their stored procedures and write simple queries. They weren't using any of the main features, and the reason was because they weren't aware of how to use them.

This series will hopefully be a helpful "How To" guide to maximizing the use of Query Analyzer. There are quite a few great database tools out there other than Query Analyzer, but the main advantage of Query Analyzer is it comes with SQL Server, installs by default as part of the client set, and is very powerful in its own right. If we're supporting SQL Server, we'll always have Query Analyzer.

The key is to understand what Query Analyzer can do for us, the kind of information it can provide us, and its limitations. This article will cover the basics of starting up Query Analyzer and connecting to SQL Server. I'll introduce the various ways to execute Query Analyzer and talk a bit about authentication methods and a new tool provided for us by Windows 2000. We'll also take a quick look at a problem with linked servers, Query Analyzer (or any client, for that matter), and Windows authentication.

For those who've been using Query Analyzer, most of the information in this article will be old hat but hopefully there will be some new information for everyone. We'll start from the very beginning, to ensure we leave no gaps. Query Analyzer has a lot of functionality, and some of it can get pretty in depth. As with most good tools, it takes a little bit of work and some time to really learn all of the features and options in order to put the tool to maximum effect. So without further ado, let's dive right in!

Starting Query Analyzer

There are several ways to start Query Analyzer. The first is from the Programs sub-menu:

The second is from the Tools menu of Enterprise Manager:

A third way is to simply execute the program (isqlw) from Start >> Run:

We'll see more about bringing up Query Analyzer via isqlw in a bit when we talk about the RunAs command.

Connecting to a Server

Upon entering Query Analyzer, we're prompted to connect to a server. The connection dialog box is the same we'll see in other SQL Server tools such as Profiler. With it we can choose the server to connect to, as well as the connection method:

One area that I've seen issues with is understanding the two different authentication methods. I have seen developers take their NT (Windows) authentication and try and use the username and password using SQL Server authentication. This usually occurs when the developer is logged into the workstation as one user and is trying to get access to the SQL Server using another NT login.

Windows authentication deals with domain or local computer user accounts. SQL Server authentication deals with SQL Server logins created on the particular SQL Server, which means SQL Server must be running in mixed mode. Obviously, one cannot authenticate Windows accounts using SQL Server authentication.

Under Windows NT 4.0, there aren't too many options. Windows authentication is going to match the user account logged into the workstation. With Windows 2000, however, there is the RunAs command. The general syntax is:

RUNAS [/profile] [/env] [/netonly] /user: program

An example of its use to bring up Query Analyzer is:

runas /user:MyDomain\User2 isqlw

This will allow us to use Windows authentication with Query Analyzer under the User2 account. The User2 account is only active for Query Analyzer. When we exit Query Analyzer, nothing will be running under the User2 context.

Digressing a little, this is a good practice for privileged accounts. Best practices state that we shouldn't do day-to-day activities such as checking email, writing status reports, etc., using a privileged account because whatever account we use for those tasks is the most vulnerable to compromise. From a risk perspective, if a non-privileged account is compromised, the potential damage is far less than if a privileged account is compromised. By utilizing the RunAs command, we can carry out our daily tasks with a non-privileged account, and if we have a second account with the appropriate privileges, we can use it only in the specific context of certain applications. For other SQL Server utilities, given default paths, here are the runas commands:

Enterprise Manager (SQL Server 2000):

runas /user:MyDomain\User2 "mmc.exe /s \"C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC\""

SQL Profiler:

runas /user:MyDomain\User2 profiler

As can be seen, the RunAs command is very helpful from a security perspective. Running in a non-privileged account with the ability to go to privileged mode has been around in the Unix world for a long time with the su command (superuser). It's nice to see this security measure has come around to the Windows world.

Linked Servers, NT Authentication, and "Double Hop"

One other issue I've seen that can throw a developer or DBA into fits involves querying against linked servers. The root of the issue involves Windows authentication and the concept of the "double hop," which is prohibited by Windows authentication under NT non-Kerberos environments (such as NT 4.0).

Most of the time when we're using Query Analyzer, we're sitting at a workstation accessing a physically different computer running SQL Server. For instance:

In SQL Server we can create a linked server from one SQL Server to another. Diagramming this:

However, we may be running QA from our workstation, and we're writing queries on one SQL Server that may need to include data from a linked server.

If we're using SQL Server authentication to get to the first SQL Server, and the SQL Server uses SQL Server authentication to get to the linked server, we can get at our data without issue. SQL Server is able to make the connection to the second server by using the SQL Server login we specified when we created the linked server.

However, when we connect to a SQL Server using Windows authentication (specifically NTLM, the method of authentication for NT 4.0) and that SQL Server then attempts to use Windows authentication to the linked server, we'll get one of the following errors:

Server: Msg 18456, Level 14, State 1, Line 1 Login failed for user '\'

or

Server: Msg 18456, Level 14, State 1, Line 1 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

The problem is with NTLM. SQL Server is having to take the Windows authentication passed to it by the client and then authenticate using the same Windows authentication to the second server. If we think of going from one computer to another as a hop, we have to traverse two hops to get from client system to the linked server. NTLM does not support such a "double hop" and as a result, when the second hop is attempted, SQL Server must try and make an anonymous connection, since it has no other credentials which to use.

As a result, we fail and receive the error message above. This problem is not limited to SQL Server, as it can occur in FrontPage 2000 under similar conditions when trying to work with security. Kerberos does not have such a limitation and SQL Server 2000 running on Windows 2000 with Active Directory and Kerberos will not have the same issue. Windows authentication can then traverse the double hop. If that's not our setup, however, the only workaround is to make the second connection via SQL Server authentication.

This gets around the double hop issue, though admittedly it means the second SQL Server has to run in Mixed Mode. Now, one thing to remember is if we're running Query Analyzer at the console of the SQL Server and not from a separate client is that we'll not encounter the double hop. The double hop occurs when we have to go from one computer to another, and then finally to a second. Also, if we have jobs on a particular server that need to access a linked server, those jobs can work with a link via Windows authentication, since we're still only talking about a single hop.

Wrap Up

In this article we've covered the basics of starting up Query Analyzer and authenticating on SQL Server. We looked at several ways to execute Query Analyzer and we also looked at the RunAs command. The RunAs command allows us to be logged into our Windows 2000 or higher workstation with a non-privileged account while still being able to carry out our administrative duties with the level of permissions we need. We also took a quick loop at authentication and the problems we encounter with the double hop.

In the next article we'll take a closer look at the Object Browser and the Transact-SQL Debugger. The Object Browser is the most visible new feature of Query Analyzer, so we'll take the time to look at it in some detail. We'll also walk through the use of the Transact-SQL Debugger so that we might leverage it for future development efforts. A good understanding of these two tools can save us a bit of time here and there and so we'll drill down on them.

References

• Linked Server Double Hop Issue: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q238477
• RunAs for Administrative Context: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q225035

By Brian Kelley, 2002/02/21

continue ...

Getting the Most Out of SQL Server 2000's Query Analyzer, Part II

Deleting Data in SQL Server with TRUNCATE vs DELETE commands

Problem
There are two main keywords used for deleting data from a table: TRUNCATE and DELETE. Although each achieves the same result, the methods employed for each vastly differ. There are advantages, limitations, and consequences of each that you should consider when deciding which method to use.

Solution
Deleting Data Using TRUNCATE TABLE

TRUNCATE TABLE is a statement that quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of logging the deletions, as well as the number of locks acquired; however, it bypasses the transaction log, and the only record of the truncation in the transaction logs is the page deallocation. Records removed by the TRUNCATE TABLE statement cannot be restored. You cannot specify a WHERE clause in a TRUNCATE TABLE statement-it is all or nothing. The advantage to using TRUNCATE TABLE is that in addition to removing all rows from the table it resets the IDENTITY back to the SEED, and the deallocated pages are returned to the system for use in other areas.

In addition, TRUNCATE TABLE statements cannot be used for tables involved in replication or log shipping, since both depend on the transaction log to keep remote databases consistent.
TRUNCATE TABLE cannot used be used when a foreign key references the table to be truncated, since TRUNCATE statements do not fire triggers. This could result in inconsistent data because ON DELETE/UPDATE triggers would not fire. If all table rows need to be deleted and there is a foreign key referencing the table, you must drop the index and recreate it. If a TRUNCATE TABLE statement is issued against a table that has foreign key references, the following error is returned:

Deleting Data Using DELETE FROM Statement

DELETE TABLE statements delete rows one at a time, logging each row in the transaction log, as well as maintaining log sequence number (LSN) information. Although this consumes more database resources and locks, these transactions can be rolled back if necessary. You can also specify a WHERE clause to narrow down the rows to be deleted. When you delete a large number of rows using a DELETE FROM statement, the table may hang on to the empty pages requiring manual release using DBCC SHRINKDATABASE (db_name).
When large tables require that all records be deleted and TRUNCATE TABLE cannot be used, the following statements can be used to achieve the same result as TRUNCATE TABLE:

• DELETE from "table_name"
• DBCC CHECKIDENT("table_name", RESEED, "reseed_value")

Optimize Parameter Driven Queries with the OPTIMIZE FOR Hint

SQL Server doesn't always select the best execution plan for your queries and thankfully there are several different hints that can be used to force SQL Server into using one execution plan over another. One issue that you may be faced with is when using parameters in your WHERE clause, sometimes the query runs great and other times it runs really slow. I recently had a situation where the hard coded values in the WHERE clause worked great, but when I changed the values to parameters and used the exact same values for the parameters the execution plan drastically changed and the overall time it took to run the query increased by about 5 times. This situation is referred to as parameter sniffing where SQL Server stores the values used as part of the execution plan and therefore other queries with different values may act totally different. So what options are there to get around this.

Solution
As mentioned above, SQL Server offers many hints that can be used to force how an execution plan is put together. The option that we are interested in is the OPTIMIZE FOR option. This will allow us to specify what parameter value we want SQL Server to use when creating the execution plan. This is a SQL Server 2005 hint.

Let's take a look at a few examples. These were all done against the AdventureWorks database.

Example 1

This first example is a straight query using a parameter without the OPTIMIZE FOR hint.

DECLARE @Country VARCHAR(20) SET @Country = 'US' SELECT * FROM Sales.SalesOrderHeader h, Sales.Customer c, Sales.SalesTerritory t WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @Country

The following is the actual execution plan that is generated to execute this query.

The overall cost for this query is 1.31871.

Example 2

In this example we are specifying the OPTIMIZE FOR hint which is shown in the last line of this query. The first part is identical to the query in Example 1. In this example we are telling SQL Server to optimize this execution plan for this query using "CA" as the parameter value.

DECLARE @Country VARCHAR(20) SET @Country = 'US' SELECT * FROM Sales.SalesOrderHeader h, Sales.Customer c, Sales.SalesTerritory t WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @Country OPTION (OPTIMIZE FOR (@Country = 'CA'))

The following is the actual execution plan that is generated to execute this query. For the most part this query plan looks the same as the query plan above, except that the percentages in some tasks are a bit different.

The overall cost for this query is 1.1805 which is better then example 1.

Example 3

In this example we have changed the OPTIMIZE FOR value to be "US" instead of "CA", everything else is the same.

DECLARE @Country VARCHAR(20) SET @Country = 'US' SELECT * FROM Sales.SalesOrderHeader h, Sales.Customer c, Sales.SalesTerritory t WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @Country OPTION (OPTIMIZE FOR (@Country = 'US'))

The following is the actual execution plan that is generated to execute this query. As you can see SQL Server has changed this quite a bit from examples 1 and 2.

The overall cost for this query is 1.160652 which is not as good as the first two examples.

Client Stats

In addition to the above, we also captured the client statistics with each execution. The trial # corresponds with the example #. In the below grid we can see the Total Execution time increased with each run. For example1 the total time was 328, for example2 was 406 and for example3 it was 468. This shows that for this query SQL Server seems to be picking the best query plan.

Note: the procedure cache was not cleared between each run.

Summary

As you can see from this simple test when using parameters, using the OPTIMIZE FOR hint can change the query plan. This may have a positive impact or a negative impact, but this gives you another option to adjust how your queries execute especially if things start performing poorly when using parameters in your queries.

As I mentioned above, the dataset I was working on had tables with 20+ million rows. Here are the query plan costs that I had for each option:

• hard coded values - 4.44821
• parameters with same values - 2722.9
• using OPTIMIZE hint - 4.44649

Without the OPTIMIZE hint some executions were fast and others took a long time. So depending on your dataset and the task at hand this option may or may not help.

Using Solutions and Projects to manage your SQL Server code

When developing code for a project there is often more than one component that you need to work with for the entire project. The project may include new tables, table changes, new stored procedures, changes to stored procedures, etc... Keeping all of these components straight as well as logically grouped together is sometimes a challenge in itself. Are there are any built-in tools that allow you better manage project components vs. one big file?

Solution
In SQL Server Management Studio a new component has been added that allows you to logically group your project code together in one place.

To access this you need to create a new project which you can do one of two ways:

• File -> New -> Project or (Ctrl + Shift + N)

When you create a new project you have several choices such as:

• SQL Server Scripts
• Analysis Services Scripts
• SQL Server Compact Edition Scripts

At this point you need to select the type of project and a new solution will be created for that project.

After you create the new project the following screen will appear in Management Studio. At this point you can begin creating connections, queries and any other miscellaneous files that you need for this project. In addition, you can have multiple projects within one solution.

The following gives you an idea of what a solution could look like with multiple projects and multiple components within each project.

This is a pretty simplistic concept of keeping your code together, but for complex projects where there are a lot of components this could become a big help for keeping your project code straight.

SQL : Using Views to Simplify Data Access

Problem
One challenge that just about everyone is faced with is ever changing database schemas. From the onset of a project the database schema might be perfect on day one, but as the application evolves and the business needs change database table structures have to change. In addition, as database become more and more complex there is often the challenge of having to join several tables together on an ongoing basis which is time consuming and also creates the possibility of mistakes. So what other options are there to ensure your applications do not break when your database schema changes and what is an easier way to handle multi-join queries that are used over and over again?

Solution
The simple solution here is to use Views. Views allow you to predefine what query results will look like, enable you to pre-join your tables as well as allow you to mask any sensitive data that you don't want people to access. Basically a view is a defined and saved query that can be used over and over again.

So in the AdventureWorks database there are several tables that allow us to get employee information. In order to get the following query elements:
EmployeeID
Title
FirstName
MiddleName
LastName
Suffix
Job Title
Phone
EmailAddress
EmailPromotion
AddressLine1
AddressLine2
City
StateProvinceName
PostalCode
CountryRegionName
AdditionalContactInfo

we need to join the following tables.
HumanResources.Employee
Person.Contact
HumanResources.EmployeeAddress
Person.Address
Person.StateProvince
Person.CountryRegion

Writing this query over and over again becomes time consuming and also there are potential issues that the query will not be constructed the same way each time.

Here is what this query would look like. It is not that complex to write, but it would be easier to save this once and reuse the view each time instead of writing this query every time you needed this data.SELECT
e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionName]
,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode];


To create the view for the above query the syntax is as simple as the below code.CREATE VIEW [HumanResources].[vEmployee]
AS
SELECT
e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionName]
,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode];


The only difference between the first set of code and the second set of code is:

CREATE VIEW [HumanResources].[vEmployee] AS

This statement is telling SQL Server to create a view called HumanResouces.vEmployee as the query that follows. Once this view has been created instead of writing this query every time we can now use the view as follows or any query as long as the columns exist in the query.SELECT * FROM HumanResources.vEmployee


This is much easier than having to rewrite the query every time. In addition, we are now guaranteed that the result set and the joins will always be consistent.

Another advantage of using views is that the underlying tables can change and the view will still work, granted you did not drop columns that the view was using. But even if you did drop a column in a table you could change the view to return a static value and your application would continue to work without having to make any changes to the application. Although, this depends on the data and the application.

As mentioned above you can grant access to the view and not the table and therefore you can mask certain columns that have sensitive data that you need to store, but do not want everyone to access.

Views can also be joined to other tables or views. But be careful with this, because things can get quite complex to track down issues if you have a bunch of views and start joining views to views.

In addition, you can update views directly and also create indexed views for better performance. There are some constraints as to what you can update in a view, but this can be done.

Next Steps
Take advantage of all the features that SQL Server offers. Views are nothing new, but can simplify tasks that are repeated over and over again.
Look for areas in your database where views can be useful
Enterprise Manager and Management Studio provide query builds that you can use to create these queries and then save them as views.

Building SQL Server Indexes in Ascending vs Descending Order

Building SQL Server Indexes in Ascending vs Descending Order

Written By: Edgewood Solutions Engineers -- 9/25/2007

Problem
When building indexes often the default options are used to create an index which creates the index in ascending order. This is usually the most logical way of creating an index, so the newest data or smallest value is at the top and the oldest or biggest value is at the end. Although searching an index works great by creating an index this way, but have you ever thought about the need to always return the most recent data first and ways you can create an index in descending order so the most recent data is always at the top of the index? Let's take a look at how this works and the advantages of creating an index in descending vs ascending order.

Solution
When creating an index you have the option to specify whether the index is created in ascending or descending order. This can be done simply by using the key word ASC or DESC when creating the index as follows. The following examples all use the AdventureWorks sample database.

Create index in descending order:

CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate] ON [Purchasing].[PurchaseOrderHeader] ( [OrderDate] DESC )

Create index in ascending order:

CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate] ON [Purchasing].[PurchaseOrderHeader] ( [OrderDate] ASC )

Let's take a look at a couple of queries and query plans to see how this differs and if there is any advantage.

Example 1

In this example we are using the PurchaseOrderHeader table to select the top 10 records and just the OrderDate column from the table sorted by OrderDate in ascending order. There is no index on the OrderDate column.

This query does a Clustered Index Scan and has a cost of 0.124344.

Example 2

In this example we are using the PurchaseOrderHeader table to select the top 10 records and just the OrderDate column from the table sorted by OrderDate in descending order. There is no index on the OrderDate column.

This query does a Clustered Index Scan and has a cost of 0.124344 which is the same as above.

Example 3

In this example we create an index on the OrderDate in ascending order.

We are using the PurchaseOrderHeader table to select the top 10 records and just the OrderDate column from the table sorted by OrderDate in ascending order.

This query does an Index Scan on the new index and has a cost of 0.0033056 which is much better than our previous value of 0.124344.

So from this we can see that adding the index does make this query much faster.

Example 4

In this example we created an index on the OrderDate in ascending order.

We are using the PurchaseOrderHeader table to select the top 10 records and just the OrderDate column from the table sorted by OrderDate in descending order.

This query does an Index Scan on the new index and has a cost of 0.0033056 which is the same as Example 3.

Take note that though the index was created in ascending order, getting the data in descending order is just as fast as getting the data in ascending order.

Example 5

Even though we already showed that selecting the data in descending order against an ascending index does not make a difference, let's just do a check to make sure.

In this example we created an index on the OrderDate in descending order.

We are using the PurchaseOrderHeader table to select the top 10 records and just the OrderDate column from the table sorted by OrderDate in ascending order.

This query does an Index Scan on the new index and has a cost of 0.0033056 which is the same as above, so there is no difference at all.

Another thing to look at is having the need to sort some of the columns in ascending order and other columns in descending order.

Example 6

In this example we created an index on the OrderDate in ascending order and SubTotal in ascending order.

CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate] ON [Purchasing].[PurchaseOrderHeader] ( [OrderDate] ASC, [SubTotal] ASC )

We are using the PurchaseOrderHeader table to select the top 10 records and the OrderDate and SubTotal column from the table sorted by OrderDate in ascending order and the SubTotal in ascending order.

This query does an Index Scan and has a cost of 0.0033123.

Example 7

In this example we created an index on the OrderDate in ascending order and SubTotal in ascending order..

We are using the PurchaseOrderHeader table to select the top 10 records and the OrderDate and SubTotal column from the table sorted by OrderDate in ascending order and the SubTotal in descending order.

This query does an Index Scan and has a cost of 0.102122. Having this index this way does not help much, since 84% of the work is done in the Sort operation.

Example 8

In this example we created an index on the OrderDate in ascending order and SubTotal in descending order.

CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate] ON [Purchasing].[PurchaseOrderHeader] ( [OrderDate] ASC, [SubTotal] DESC )

We are using the PurchaseOrderHeader table to select the top 10 records and the OrderDate and SubTotal column from the table sorted by OrderDate in ascending order and the SubTotal in descending order.

This query does an Index Scan and has a cost of 0.0033123. Having this index created this way helps out in a a big way. The Sort operation has now disappeared.

Summary
As we have shown creating an index in ascending or descending order does not make a big difference when there is only one column, but when there is a need to sort data in two different directions one column in ascending order and the other column in descending order the way the index is created does make a big difference.

Next Steps

• Next time you create indexes keep the above in mind. Don't worry so much about creating your single column index in either ascending or descending order
• If you have the need to create a multi-column index keep in mind the improvements that can be made if you create the index based on the way the data is accessed.