SQL Server Date-Time Formats

Here's a summary of the different date formats that come standard in SQL Server as part of the CONVERT function. Following the standard date formats are some extended date formats

Standard Date Formats
Date Format	Standard	SQL Statement	Sample Output
Mon DD YYYY HH:MIAM (or PM)	Default	SELECT CONVERT(VARCHAR(20), GETDATE(), 100)	Jan 1 2011 11:29PM
MM/DD/YY	USA	SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]	11/23/98
MM/DD/YYYY	USA	SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]	11/23/2011
YY.MM.DD	ANSI	SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]	93.01.01
YYYY.MM.DD	ANSI	SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]	2011.01.01
DD/MM/YY	British/French	SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]	19/02/93
DD/MM/YYYY	British/French	SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]	19/02/2011
DD.MM.YY	German	SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]	25.12.05
DD.MM.YYYY	German	SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]	25.12.2011
DD-MM-YY	Italian	SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]	24-01-92
DD-MM-YYYY	Italian	SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]	24-01-2011
DD Mon YY	-	SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]	04 Jul 11
DD Mon YYYY	-	SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]	04 Jul 2011
Mon DD, YY	-	SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]	Jan 24, 11
Mon DD, YYYY	-	SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]	Jan 24, 2011
HH:MM:SS	-	SELECT CONVERT(VARCHAR(8), GETDATE(), 108)	03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM)	Default + milliseconds	SELECT CONVERT(VARCHAR(26), GETDATE(), 109)	Apr 28 2011 12:32:29:253PM
MM-DD-YY	USA	SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]	01-02-11
MM-DD-YYYY	USA	SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]	01-01-2011
YY/MM/DD	-	SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]	98/11/23
YYYY/MM/DD	-	SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]	2011/11/23
YYMMDD	ISO	SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]	920124
YYYYMMDD	ISO	SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]	20110124
DD Mon YYYY HH:MM:SS:MMM(24h)	Europe default + milliseconds	SELECT CONVERT(VARCHAR(24), GETDATE(), 113)	28 Apr 2011 00:34:55:190
HH:MI:SS:MMM(24H)	-	SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]	11:34:23:013
YYYY-MM-DD HH:MI:SS(24h)	ODBC Canonical	SELECT CONVERT(VARCHAR(19), GETDATE(), 120)	2011-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h)	ODBC Canonical (with milliseconds)	SELECT CONVERT(VARCHAR(23), GETDATE(), 121)	2011-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM	ISO8601	SELECT CONVERT(VARCHAR(23), GETDATE(), 126)	2011-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM	Kuwaiti	SELECT CONVERT(VARCHAR(26), GETDATE(), 130)	28 Apr 2011 12:39:32:429AM
DD/MM/YYYY HH:MI:SS:MMMAM	Kuwaiti	SELECT CONVERT(VARCHAR(25), GETDATE(), 131)	28/04/2011 12:39:32:429AM

Some more date formats that does not come standard in SQL Server as part of the CONVERT function.

Extended Date Formats
Date Format	SQL Statement	Sample Output
YY-MM-DD	SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]	92-01-24
YYYY-MM-DD	SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]	2011-01-24
MM/YY	SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]	08/92
MM/YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]	12/2011
YY/MM	SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]	92/08
YYYY/MM	SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]	2011/12
Month DD, YYYY	SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]	July 04, 2011
Mon YYYY	SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]	Apr 2011
Month YYYY	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]	February 2011
DD Month	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month]	11 September
Month DD	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]	September 11
DD Month YY	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]	19 February 92
DD Month YYYY	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]	11 September 2011
MM-YY	SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]	12/92
MM-YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]	05-2011
YY-MM	SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]	92/12
YYYY-MM	SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]	2011-05
MMDDYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]	122506
MMDDYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]	12252011
DDMMYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]	240702
DDMMYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]	24072011
Mon-YY	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]	Sep-02
Mon-YYYY	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]	Sep-2011
DD-Mon-YY	SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]	25-Dec-05
DD-Mon-YYYY	SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]	25-Dec-2011

Read More

SQL Server 2008 Auditing, Change Data Capture and Tracking

When I started working on SQL Server 2008, I was truly amazed with the new features of SQL Server 2008, one of that cool features is auditing in SQL Server 2008. First I thought to give here step by step Walkthrough, but I thought instead of reinventing the wheel, why don't I just give you some really good links only, which will guide you in much better way and also save some of my time :)

Please find below the links which I followed and found very useful.

Introduction and Step by Step Walkthrough
http://blogs.msdn.com/manisblog/archive/2008/07/21/sql-server-2008-auditing.aspx

MSDN Link
http://msdn.microsoft.com/en-us/library/dd392015.aspx

But again I would like to mention that Auditing is the feature which will give you the Audit in Object, Schema and Database Level So my next challenge was to Keep track of the Database at the Field/Column level which Auditing does not provide (Correct me if I am wrong),

So I found one new features of SQL Server 2008 known as Change Data Capture/ Change Data Tracking, which is again a very cool feature,

Change data capture enables SQL Server administrators and developers to capture insert, update and delete events in a sql server table as well as the details of the event which caused data change on the relevant database table.

When you apply Change Data Capture feature on a database table, a mirror of the tracked table is createad which reflects the same column structure of the original table and additional columns that include metadata which is used to summarize what is the change in the database table row.

So enabling the Change Data Capture feature on a database table, you can track the activity on modified rows or records in the related table.

Change Data Capture (CDC) can be considered as Microsoft solution for data capture systems in SQL Server 2008 and next versions.

There were samples of data capture solutions implemented for Microsoft SQL Server 2000 and SQL Server 2005 by using after update/insert or
after delete triggers. But CDC enables SQL Server developers to build sql server data archiving without a necessity to create triggers on
tables for logging. SQL Server database administrators or programmers can also easily monitor the activity for the logged tabled.

You can find more details of how to configure and enable the Change Data Source on Tables
http://www.databasejournal.com/features/mssql/article.php/3720361/Microsoft-SQL-Server-2008----Change-Data-Capture--Part-I.htm

Difference Between Change Data Capture and Change Data Tracking
http://msdn.microsoft.com/en-us/library/cc280519.aspx

MSDN Link
http://msdn.microsoft.com/en-us/library/bb522489.aspx

I hope you will find these useful.

~Brij

Read More

Problem Starting SQL Server Analysis Services.

For those who are facing problem in Starting SQL Server Analysis Services, please follow the following steps to fix the issue.

1. Open Control Panel
2. Go to Regional and Language Options Locale Settings
3. Change the Format, Location and Administrative from English(India) to English(United States)
4. Restart the System
5. Once your system is Restarted open the Registry Editor (from Start = > Run => regedit)
6. Navigate to HKEY_USERS
7. Look for LSA entry @ S-1-5-18
8. Search for LocaleName
9. Update the following keys
   1. Locale = 00000409
   2. LocaleName = en-US
   3. sCountry = United States

      10. Open the SQL Server Configuration Manager, and start the SQL Server Analysis Services.

FYI: This is caused because Windows Vista is supporting en-IN whereas SQL Server 2005 does not provide support for en-IN, so while doing the SQL Server Surface Area configuration, if you try to run SQL Server Analysis Services it will give the following exception (can be found in Event Viewer)

The service cannot be started: Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. Errors in the metadata manager. LOG file extension can be only .LOG. Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. Message-handling subsystem: The message manager for the 16393 locale cannot be found. Internal error: Failed to generate a hash string.

Read More

Microsoft launches DreamSpark: Indian students get access to technical software at no charge

- Access to software developer and designer tools to students at no charge

- DreamSpark includes:

o Visual Studio 2005/2008 Professional Edition,

o Expression Studio (includes Web, Blend, Media and Design),

o SQL Server 2005 Express, SQL Server 2005 Developer Edition,

o Windows Server 2008 Standard Edition,

o XNA Game Studio 2.0 and

o 12-month trial academic subscription to the XNA Creators Club

- Available online on www.dreamsparkindia.com; and via DVDs at NIIT, Aptech and Hughes Net Fusion Centers

Read More

SQL Server Coding Standards

ASP.NET, C# or VB.NET often goes hands in hands, In my previous post I have given the C# Coding Standards, but without the correct SQL Server Coding standards, my previous post was almost incomplete, so in this post I am giving you the Link to SQL Server Coding Standards.

You can get more details in Pinal Dave SQLAuthority.com, http://blog.sqlauthority.com/2007/06/06/sql-server-database-coding-standards-and-guidelines-complete-list-download/

This post by Pinal Dave consists of Series of Database Coding Standards and Guidelines.

SQL SERVER Database Coding Standards and Guidelines - Introduction
SQL SERVER - Database Coding Standards and Guidelines - Part 1
SQL SERVER - Database Coding Standards and Guidelines - Part 2

SQL SERVER Database Coding Standards and Guidelines Complete List Download

Read More

Difference between NVARCHAR and VARCHAR in SQL Server

The broad range of data types in SQL Server can sometimes throw people through a loop, especially when the data types seem to be highly interchangeable. Two in particular that constantly spark questions are VARCHAR and NVARCHAR: what's the difference between the two, and how important is the difference?

VARCHAR is an abbreviation for variable-length character string. It's a string of text characters that can be as large as the page size for the database table holding the column in question. The size for a table page is 8,196 bytes, and no one row in a table can be more than 8,060 characters. This in turn limits the maximum size of a VARCHAR to 8,000 bytes.

The "N" in NVARCHAR means uNicode. Essentially, NVARCHAR is nothing more than a VARCHAR that supports two-byte characters. The most common use for this sort of thing is to store character data that is a mixture of English and non-English symbols — in my case, English and Japanese.

The key difference between the two data types is how they're stored. VARCHAR is stored as regular 8-bit data. But NVARCHAR strings are stored in the database as UTF-16 — 16 bits or two bytes per character, all the time — and converted to whatever codepage is being used by the database connection on output (typically UTF-8). That said, NVARCHAR strings have the same length restrictions as their VARCHAR cousins — 8,000 bytes. However, since NVARCHARs use two bytes for each character, that means a given NVARCHAR can only hold 4,000 characters (not bytes) maximum. So, the amount of storage needed for NVARCHAR entities is going to be twice whatever you'd allocate for a plain old VARCHAR.

Because of this, some people may not want to use NVARCHAR universally, and may want to fall back on VARCHAR — which takes up less space per row — whenever possible.

Here's an example of how to mix and match the use of the two types. Let's say we have a community website where people log in with a username, but can also set a public "friendly" name to be more easily identified by other users. The login name can be a VARCHAR, which means it must be 8-bit ASCII (and it can be constrained further to conventional alphanumerics with a little more work, typically on the front end). The friendly name can be an NVARCHAR to allow Unicode entities. This way you're allowing support for Unicode, but only in the place where it matters most — both for the users, and where the extra storage space is going to be put to the best possible use.

VARCHAR and NVARCHAR in SQL Server 2005

One fairly major change to both VARCHAR and NVARCHAR in SQL Server 2005 is the creation of the VARCHAR(MAX) and NVARCHAR(MAX) data types. If you create a VARCHAR(MAX) column, it can hold up to 2^31 bytes of data, or 2,147,483,648 characters; NVARCHAR(MAX) can hold 2^30 bytes, or 1,073,741,823 characters.

These new data types are essentially replacements for the Large Object or LOB data types such as TEXT and NTEXT, which have a lot of restrictions. They can't be passed as variables in a stored procedure, for instance. The (MAX) types don't have those restrictions; they just work like very large string types. Consequently, if you're in the process of re-engineering an existing data design for SQL Server 2005, it might make sense to migrate some (although not all!) TEXT / NTEXT fields to VARCHAR(MAX) / NVARCHAR(MAX) types when appropriate.

The big difference between VARCHAR and NVARCHAR is a matter of need. If you need Unicode support for a given data type, either now or soon enough, go with NVARCHAR. If you're sticking with 8-bit data for design or storage reasons, go with VARCHAR. Note that you can always migrate from VARCHAR to NVARCHAR at the cost of some room -- but you can't go the other way 'round. Also, because NVARCHAR involves fetching that much more data, it may prove to be slower depending on how many table pages must be retrieved for any given operation.

Read More

SQL SERVER - Import CSV File Into SQL Server Using Bulk Insert - Load Comma Delimited File Into SQL Server

This is very common request recently - How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.

CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Create TestTable

USE TestData GO CREATE TABLE CSVTest (ID INT, FirstName VARCHAR(40), LastName VARCHAR(40), BirthDate SMALLDATETIME) GO

Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:csvtest.txt

1,James,Smith,19750101 2,Meggie,Smith,19790122 3,Robert,Smith,20071101 4,Alex,Smith,20040202

Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK INSERT CSVTest FROM 'c:csvtest.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = 'n' ) GO

Check the content of the table.

SELECT * FROM CSVTest GO

Drop the table to clean up database.

DROP TABLE CSVTest GO

Reference : Pinal Dave (http://www.SQLAuthority.com)

Read More

SQL Query Analyzer Keyboard Shortcuts

This table displays the keyboard shortcuts available in SQL Query Analyzer.

Activity	Shortcut
Bookmarks: Clear all bookmarks.	CTRL-SHIFT-F2
Bookmarks: Insert or remove a bookmark (toggle).	CTRL+F2
Bookmarks: Move to next bookmark.	F2
Bookmarks: Move to previous bookmark.	SHIFT+F2
Cancel a query.	ALT+BREAK
Connections: Connect.	CTRL+O
Connections: Disconnect.	CTRL+F4
Connections: Disconnect and close child window.	CTRL+F4
Database object information.	ALT+F1
Editing: Clear the active Editor pane.	CTRL+SHIFT+DEL
Editing: Comment out code.	CTRL+SHIFT+C
Editing: Copy. You can also use CTRL+INSERT.	CTRL+C
Editing: Cut. You can also use SHIFT+DEL.	CTRL+X
Editing: Decrease indent.	SHIFT+TAB
Editing: Delete through the end of a line in the Editor pane.	CTRL+DEL
Editing: Find.	CTRL+F
Editing: Go to a line number.	CTRL+G
Editing: Increase indent.	TAB
Editing: Make selection lowercase.	CTRL+SHIFT+L
Editing: Make selection uppercase.	CTRL+SHIFT+U
Editing: Paste. You can also use SHIFT+INSERT.	CTRL+V
Editing: Remove comments.	CTRL+SHIFT+R
Editing: Repeat last search or find next.	F3
Editing: Replace.	CTRL+H
Editing: Select all.	CTRL+A
Editing: Undo.	CTRL+Z
Execute a query. You can also use CTRL+E (for backward compatibility).	F5
Help for SQL Query Analyzer.	F1
Help for the selected Transact-SQL statement.	SHIFT+F1
Navigation: Switch between query and result panes.	F6
Navigation: Switch panes.	Shift+F6
Navigation: Window Selector.	CTRL+W
New Query window.	CTRL+N
Object Browser (show/hide).	F8
Object Search.	F4
Parse the query and check syntax.	CTRL+F5
Print.	CTRL+P
Results: Display results in grid format.	CTRL+D
Results: Display results in text format.	CTRL+T
Results: Move the splitter.	CTRL+B
Results: Save results to file.	CTRL+SHIFT+F
Results: Show Results pane (toggle).	CTRL+R
Save.	CTRL+S
Templates: Insert a template.	CTRL+SHIFT+INSERT
Templates: Replace template parameters.	CTRL+SHIFT+M
Tuning: Display estimated execution plan.	CTRL+L
Tuning: Display execution plan (toggle ON/OFF).	CTRL+K
Tuning: Index Tuning Wizard.	CTRL+I
Tuning: Show client statistics	CTRL+SHIFT+S
Tuning: Show server trace.	CTRL+SHIFT+T
Use database.	CTRL+U

Source MSDN

Read More

The Cost of Function Use In A Where Clause

A common mistake made in writing SQL statements is to wrap filtering columns of a WHERE clause inside of a function. SQL server performance is very dependant on SQL's ability to properly use indexes when it retrieves records. The lifeblood of proper query plan creation is using very deterministic filtering logic on indexed columns (Some refer to this as Sargable Where clauses). The biggest problem we see is when a filtering column is wrapped inside of a function, the query optimizer does not see the column and if an index exists on that column, the index likely will not be used (of course, just like anything else in our hi-tech world, this is not always the case). Take this example In the CadencedEventCustomer table, we have a nonclustered index on FullName1. The simple query below should use this index and should perform an index seek to find the qualifying records instantly (Seek = FAST). Select * FROM CadencedEventCustomer WHERE isNull(FullName1,'') = 'Ed Jones' Our intention here (albeit incorrect) is to correctly handle records in which the FullName1 field is null. Our concern is that Null records will be missed (more on this later). Since we are wrapping the FullName1 column in the IsNull function, the query optimizer doesn't see it and the index is not used. Here is the query plan produced by SQL Server's query engine: |--Index Scan(OBJECT:([CIPv3].[dbo].[CadencedEventCustomer].[nci_CadencedEventCustomer_FullName1]), WHERE:(isnull([CIPv3].[dbo].[CadencedEventCustomer].[FullName1],'bob')='Ed Jones')) We see an Index Scan on the FullName1 index (the index exists, but a seek cannot be performed due to the function wrapping the indexed column). A Scan means the entire table is being searched from beginning to end until SQL happens to find all the records satisfying the condition. On a table with millions of records, this obviously takes a long time. The subtree cost of this query is 10.58. Subtree cost is a relative cost to SQL Server indicating how difficult this query is for SQL Server to retrieve the data. The higher the relative cost, the more of a hit on performance (CPU, IO, RAM, etc). From my experience, anything in the double digit range is getting pretty spendy and will not perform well in a production environment. In this case, the cost is attributed to the inability of the optimizer to use an index which means SQL Server is having to do a full table scan to return all the data for the query result...OUCH. If we remove the IsNull() function our query looks like this: Select * FROM CadencedEventCustomer WHERE FullName1 = 'Ed Jones' This is a much cleaner WHERE clause and SQL Server will now see the indexed column and will use the index correctly: |--Index Seek(OBJECT:([CIPv3].[dbo].[CadencedEventCustomer].[nci_CadencedEventCustomer_FullName1]), SEEK:([CIPv3].[dbo].[CadencedEventCustomer].[FullName1]='Ed Jones') ORDERED FORWARD) Notice we now have a SEEK (Fast). The query subtree cost is now at 0.0093 ! Keep in mind, this behavior is true for ALL Functions (Not just IsNull). We should try very hard to not wrap filtering WHERE clause columns within a function. Now....as for IsNull. The reason we are using IsNull() in the first place is to return records where the value is null in the column (I recommend not allowing nulls in the database, but that topic is for another article). However, if we look closely at this WHERE clause, we can immediately see that the net result of our query (in this case) is identical whether we use the IsNull function or not. Select * FROM CadencedEventCustomer WHERE isNull(FullName1,'') = 'Ed Jones' What this query says is return all records where the FullName1 = 'Ed Jones' and IF the FullName1 column in the database holds a Null Value assume that value is '' (an empty string instead of a Null value). So....Null values will be replaced with '' which still does NOT Equal 'Ed Jones'. The net effect is that records with NULL will NOT be returned. So if we change our query to this: Select * FROM CadencedEventCustomer WHERE FullName1 = 'Ed Jones' We would get exactly the same result. So the question is, when DO we need IsNull? Well....if the IsNull function is replacing nulls with a value that satisfies the comparison and returns TRUE, then the IsNull function is more useful (still not needed as we'll see below). Take this example: Select * FROM CadencedEventCustomer WHERE isNull(FullName1,'Ed Jones') = 'Ed Jones' In this case, if we find Null values in the FullName1 column, we will replace them with 'Ed Jones'. Notice that 'Ed Jones' satisfies our comparison and will return true. So in a sense, this logic is forcing the Query to return Null values instead of filtering them out. In this case, we can argue that the IsNull function is useful. Now, having said that, although the IsNull() logic is now useful, the problem is that the column is being hidden from the optimizer and we lose the index relief provided by the index. We can rewrite this SQL statement to get the same result AND to still get index relief for the optimizer. Essentially what we want is this: Select All CadencedEventCustomers Where the FullName1 is 'Ed Jones' OR where the FullName1 is a null value. So the correct rewrite to still obtain index relief is as follows: Select * FROM CadencedEventCustomer WHERE ((FullName1 = 'Ed Jones') OR (FullName1 IS NULL)) Now we get the best of both worlds. We get the correct logic producing the results we want and SQL servers query engine can see the column so we get index relief. A few examples other than IsNull() I often see the use of Substring() and other string functions used in WHERE Clauses. Take the following WHERE SUBSTRING(MasterDealer.Name,4) = 'Ford' This is effectively hiding the MasterDealer.Name from the optimizer and is negating the use of any index on the Name column. The fix WHERE MasterDealerName Like 'Ford%' This produces the exact same result, yet also provides index relief. Another example working with dates WHERE DateDiff(mm,PlacedOnQueue,GetDate()) >= 30 Again, this is a common technique used when working with DateTime data. This is, again, hiding the PlacedOnQueue column from the optimizer. The fix WHERE PlacedOnQueue < DateAdd(mm,-30,GetDate()) The exact same results, yet this time we get index relief. Keep in mind, this behavior holds true for all functions wrapping columns in your WHERE clause. So remember anytime you feel a need to wrap a WHERE clause filtering column within a function, try really hard of a way to rewrite the statement without function use. If you need help rewriting these types of queries, please feel free to email me. By Gregory Jackson

Read More

Query Analyzer Tips and Tricks

By Mike Gunderloy

If you've worked with Microsoft SQL Server at all, you've run across SQL Query Analyzer. Of course, this tool is essential for running ad hoc queries and executing SQL statements. But have you ever taken the time to really investigate its capabilities? The SQL Server developers built a lot of functionality into Query Analyzer, not all of which is obvious to the casual user. In this article, I'll offer you ten bits of Query Analyzer that you might not have looked at already.

1. Getting Database Object Information

You probably know that SQL Server stores metadata about all of the objects in a database. The system tables contain a wealth of information about column names, data types, identity seeds, and so on. But did you know that you can get that information with a single keystroke via Query Analyzer? Highlight the object name in any SQL statement and press Alt+F1. Figure 1 shows the results for a SQL Server table. If you don't have anything highlighted, Alt+F1 will give you information about the database itself. For an equally neat trick, highlight a SQL keyword and press Shift+F1; you'll go straight to the Books Online page that describes that keyword.

2. Executing Part of a SQL Statement

Sometimes it's convenient to execute only part of a complex SQL statement that you're developing. For example, you might be working on a stored procedure that batches many statements together, or a query that contains a subquery. No problem! Just highlight the part that you want to execute, and press F5 (or press the Execute Query toolbar button if you're a mouse sort of person). Query Analyzer will only execute the highlighted text. You can parse the highlighted text without executing it by pressing Ctrl+F5.

3. Alter Objects Fast with the Object Browser

I can't possibly be the only one who's ever needed to fix an existing stored procedure. Fortunately, using Query Analyzer means never having to write the ALTER PROC statement by hand. First, display the Object Browser by pressing F8, if it's not already showing. Expand the tree to show the object that you're interested in (this tip works with any object, not just stored procedures). Right-click on the object and select Script Object to New Window As -> Alter. Query Analyzer will open a new query window, and build the necessary ALTER PROC statement for you.

If you've never looked at them, take a few minutes to explore the Object Browser shortcut menus. You can send object scripts to the clipboard or to a file, execute stored procs, or build CREATE or DROP SQL statements, among other options.

4. Drag and Drop from the Object Browser

The Object Browser is also a drag and drop source. Drag a table to a query window, and you get the table's name. Drag the Columns node under a table, and you get all of the columns from the table, separated by commas. Drag a single column and you get the column name. Judicious use of this technique can make fast work of building things like INSERT INTO statements, as well as avoid spelling errors.

5. Templates are Your Friend

Query Analyzer supports templates - boilerplate files containing SQL statements - to help you build tricky SQL more quickly. These files have the extension .tql, and they're stored in folders underneath the Templates\SQL Query Analyzer directory if you've done a full client tools install. Use Ctrl+Shift+Ins or Edit -> Insert Template to open a template into the current query window. Many templates contain parameters, which are delimited by angle brackets. Press Ctrl+Shift+M and you'll get the Replace Template Parameters dialog box, as shown in Figure 2.

Templates are just plain text files. If you find yourself needing to frequently insert some complex chunk of SQL, you can create and save your own template file to make life simpler in the future.

6. What's Up With This Query?

If you're faced with a query that has a performance issue, query analyzer is your first stop for gathering information. You have easy access to four different ways of looking at query performance:

• Ctrl+L will show you the estimated execution plan before you run the query.
• Ctrl+K will show you the actual execution plan after you run the query.
• Ctrl+Shift+T will open the trace pane, showing you the trace events on the server as you run the query.
• Ctrl+Shift+S will show you client-side statistics after you run the query.

There are items on the Query menu for each of these panes, just in case your brain is already too full to hold new shortcut keys.

7. Customize Connection Properties

When you fire up Query Analyzer and connect it to a database, it sets a few defaults - for instance, ANSI style null handling. If you'd like to tweak the connection properties for your own server environment, select Connection Properties from the Edit menu to open the dialog box shown in Figure 3, and click to your heart's content.

8. Get Back Wide Results

If you run a query that returns a column with lots of data, you'll discover that Query Analyzer truncates the results after the first 256 characters. This is especially annoying when you're working with FOR XML queries, which can return thousands of characters in an XML document format. Fortunately, this limitation is easy to modify. Select Options from the Tools menu, and navigate to the Results tab of the Options dialog box. Enter a new value for the Maximum Characters Per Column property and you're all set. While you're there, take the time to click around the rest of the Options dialog box; Query Analyzer lets you tweak quite a few things to match your own preferences.

9. Query Debugging

Query Analyzer contains a complete debugger. You can single step through stored procedures, inspect the value of local and global variables, supply values for parameters, inspect the callstack when multiple procedures are nested, and so on. The easiest way to get started is to right-click a stored procedure in the Object Browser window and select Debug . Figure 4 shows the debugging environment in action. This can be a real lifesaver when you're trying to figure out what's wrong with a complex stored procedure.

10. The Tools Menu the Way You Want It

The Query Analyzer Tools menu is extensible. Select Customize from the Tools menu to open the Customize dialog box, and switch to the Tools tab. Here you can enter new values to appear on the menu, and specify the executable file to call for each entry. You can also pass the server name, database name, or user name to the external utility.

And Yes, There's More

I hope this article has convinced you that Query Analyzer can be a powerful tool for creating, executing, and debugging SQL statements. There are plenty of features that I didn't cram into this top ten. For example, the Query Analyzer editor supports bookmarks, forcing case, adjusting tabs, and more. If this is a product that you use on a frequent basis, you owe it to yourself to keep digging and find out how to use it more efficiently. The time spent learning will be amply repaid.

About the Author

Mike Gunderloy is the author of over 20 books and numerous articles on development topics, and the lead developer for Larkware. Check out his latest book, Coder to Developer from Sybex. When he's not writing code, Mike putters in the garden on his farm in eastern Washington state.

Read More

INF: Cross-Database Ownership Chaining Behaviour Changes in SQL Server 2000 Service Pack 3

SUMMARY

Microsoft SQL Server Service Pack 3 (SP3) provides a new security enhancement related option for configuring cross-database ownership chaining, Enable cross-database ownership chaining for all databases during setup. This article discusses the cross-database ownership chaining behavior in SQL Server 2000 SP3. With this new option, you can control whether or not you permit cross-database ownership chaining. By default, this option is disabled. Microsoft recommends that you use the default option, because it makes your database server more secure.

MORE INFORMATION

Ownership Chaining

By default, all database objects have owners. When an object such as a view, a stored procedure, or a user-defined function references another object, an ownership chain is established. For example, a table that is owned by the same user. When the same user owns the source object, the view, stored procedure, or user-defined function, and all target objects (underlying tables, views, or other objects), the ownership chain is said to be unbroken. When the ownership chain is unbroken, SQL Server checks permissions on the source object but not on the target objects.

Cross-Database Ownership Chaining

Cross-database ownership chaining occurs when the source object depends on objects in another database. A cross-database ownership chain works in the same way as ownership chaining in a database, except that an unbroken ownership chain is based on all the object owners being mapped to the same login account. Therefore, in a cross-database ownership chain, if the source object in the source database and the target objects in the target databases are owned by the same login account, SQL Server does not check permissions on the target objects.
If you have more than one database used by an application, and that application calls stored procedures or views in a database that is based on objects in another database, then cross-database ownership chaining is used. Applications that rely on cross-database ownership chaining may generate permission denied errors if cross-database ownership chaining option is turned off.

Risks Associated with Cross-Database Ownership Chaining

Microsoft recommends that you disable the cross-database ownership chaining option because of the actions that highly-privileged users can perform:

Database owners and members of the db_ddladmin or the db_owners database roles can create objects that are owned by other users. These objects can potentially target objects in other databases. This means that if you enable cross-database ownership chaining, you must fully trust these users with data in all databases. To identify the members of the db_ddladmin and the db_owners roles in the current database, execute the following Transact-SQL commands:

exec sp_helprolemember 'db_ddladmin' exec sp_helprolemember 'db_owner'

Users with CREATE DATABASE permission can create new databases and attach existing databases. If cross-database ownership chaining is enabled, these users can access objects in other databases from newly created or attached databases.

Even though Microsoft recommends that you turn off cross-database ownership chaining for maximum security, there are some environments where you can fully trust your highly-privileged users; therefore, you can enable cross database ownership for specific databases to meet the requirements of specific applications.

How to Configure Cross-Database Ownership Chaining During Setup

In Microsoft SQL Server Service Pack 3 (SP3) Setup, a new dialog box has been added to allow the system administrator to control whether or not cross database ownership chaining will be permitted. If you select Enable cross-database ownership chaining for all databases during the SQL Server 2000 SP3 setup, you are enabling this option across all databases. This was the default behavior before SQL Server 2000 SP3. Regardless of the option that you select during setup, you can later modify server and database support for cross-database ownership chaining either by using Transact-SQL commands or from SQL Server Enterprise Manager.

How to Configure Cross-Database Ownership Chaining After Installation

To change the cross-database ownership chaining configuration, use the new options in the sp_configure and the sp_dboption stored procedures.
Note If you detach and then reattach a database, you must re-enable cross-database ownership chaining.

Configuring cross-database ownership chaining by using Transact-SQL commands:

Configure cross-database ownership chaining support for the instance of SQL Server with the new Cross DB Ownership Chaining option for sp_configure. When this option is set to 0, you can control cross-database ownership chaining at the database level by using sp_dboption. When this option is set to 1, you cannot restrict cross-database ownership chaining. This is the pre-SQL Server 2000 SP3 behavior. If you change this option, include the RECONFIGURE option to reconfigure the instance without having to restart it. For example, use the following command to allow cross-database ownership chaining in all databases:

EXEC sp_configure 'Cross DB Ownership Chaining', '1'; RECONFIGURE 

Configure cross-database ownership chaining at the database level with the new db chaining option for sp_dboption. When this option is set to false, the database cannot participate in cross-database ownership chaining as either the source or the target database. When this option is set to true, the database can participate in a cross-database ownership chain. By default, this option is false for all user databases after you apply SQL Server 2000 SP3. The following command enables cross-database ownership chaining for the Northwind database:

EXEC sp_dboption 'Northwind', 'db chaining', 'true'

The effects of sp_dboption are manifested only when the sp_configure Cross DB Ownership Chaining option is set to 0. Also, to enable cross-database ownership chaining at the database level, you must enable this option on both the source and the target database.

Configuring cross-database ownership chaining by using SQL Enterprise Manager:

To set this option for all databases, follow these steps:

1. Right-click <server>.

2. Click to select Properties.

3. Click Security.

4. Click to select Allow cross-database ownership chaining in the Ownership chaining section.
5. Click OK. You are prompted to stop and restart the SQL Server services.

6. Click OK .

To enable this option at the database level, follow these steps:

1. Right-click the <database>.

2. Click to select Properties.

3. Click Options.
4. Click to select Allow Cross Database Ownership Chaining in the Settings section

Document Source http://support.microsoft.com/kb/810474

Read More

Ownership Chains

When multiple database objects access each other sequentially, the sequence is known as a chain. Although such chains do not independently exist, when SQL Server 2005 traverses the links in a chain, SQL Server evaluates permissions on the constituent objects differently than it would if it were accessing the objects separately. These differences have important implications for managing security.

Ownership chaining enables managing access to multiple objects, such as multiple tables, by setting permissions on one object, such as a view. Ownership chaining also offers a slight performance advantage in scenarios that allow for skipping permission checks.

How Permissions Are Checked in a Chain

When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.

Example of Ownership Chaining

In the following illustration, the July2003 view is owned by Mary. She has granted to Alex permissions on the view. He has no other permissions on database objects in this instance. What happens when Alex selects the view?

1. Alex executes SELECT * on the July2003 view. SQL Server checks permissions on the view and confirms that Alex has permission to select on it.
2. The July 2003 view requires information from the SalesXZ view. SQL Server checks the ownership of the SalesXZ view. Because this view has the same owner (Mary) as the view that calls it, permissions on SalesXZ are not checked. The required information is returned.
3. The SalesXZ view requires information from the InvoicesXZ view. SQL Server checks the ownership of the InvoicesXZ view. Because this view has the same owner as the previous object, permissions on InvoicesXZ are not checked. The required information is returned. To this point, all items in the sequence have had one owner (Mary). This is known as an unbroken ownership chain.
4. The InvoicesXZ view requires information from the AcctAgeXZ view. SQL Server checks the ownership of the AcctAgeXZ view. Because the owner of this view is different from the owner of the previous object (Sam, not Mary), full information about permissions on this view is retrieved. If the AcctAgeXZ view has permissions that allow access by Alex, information will be returned.
5. The AcctAgeXZ view requires information from the ExpenseXZ table. SQL Server checks the ownership of the ExpenseXZ table. Because the owner of this table is different from the owner of the previous object (Joe, not Sam), full information about permissions on this table is retrieved. If the ExpenseXZ table has permissions that allow access by Alex, information is returned.
6. When the July2003 view tries to retrieve information from the ProjectionsXZ table, the server first checks to see whether cross-database chaining is enabled between Database 1 and Database 2. If cross-database chaining is enabled, the server will check the ownership of the ProjectionsXZ table. Because this table has the same owner as the calling view (Mary), permissions on this table are not checked. The requested information is returned.

Cross-Database Ownership Chaining

SQL Server can be configured to allow ownership chaining between specific databases or across all databases inside a single instance of SQL Server. Cross-database ownership chaining is disabled by default, and should not be enabled unless it is specifically required.

Potential Threats

Ownership chaining is very useful in managing permissions on a database, but it does assume that object owners anticipate the full consequences of every decision to grant permission on a securable. In the previous illustration, Mary owns most of the underlying objects of the July2003 view. Because Mary has the right to make objects that she owns accessible to any other user, SQL Server behaves as though whenever Mary grants access to the first view in a chain she has made a conscious decision to share the views and table it references. In real life, this might not be a valid assumption. Production databases are far more complex than the one in the illustration, and the permissions that regulate access to them rarely map perfectly to the administrative structures of the organizations that use them.

You should understand that members of highly privileged database roles can use cross-database ownership chaining to access objects in databases external to their own. For example, if cross-database ownership chaining is enabled between database A and database B, a member of the db_owner fixed database role of either database can spoof her way into the other database. The process is simple: Diane (a member of db_owner in database A) creates user Stuart in database A. Stuart already exists as a user in database B. Diane then creates an object (owned by Stuart) in database A that calls any object owned by Stuart in database B. Because the calling and called objects have a common owner, permissions on the object in database B will not be checked when Diane accesses it through the object she has created.

More details can be found at

Cross Database Chaining  MSDN Online Library

Read More

Tips for Writing High-performance SQL

These tips apply broadly when writing high-performance stored procedures. Unfortunately, unlike some tips, you can't simply apply most of them without first considering the nature and schema of the data you're querying.

1. Avoid using cursors (as well as other looping structures) as much as possible.

Cursors are inefficient, and database engines usually don't have the best loop implementations in terms of performance.

On the database side, you can usually replace code involving cursors with aggregate SQL statements (SELECT, INSERT, and UPDATE) that use vector tables. All database engines are heavily optimized for aggregate statements, so even if a loop is unavoidable, it is always better to execute a few aggregate statements in a loop with a small number of iterations, than to create a cursor and execute simple statements over a large number of iterations.

Even if initial performance tests, especially with a small amount of data, show cursors to be more efficient than a complex aggregate statement, it is worthwhile to try to optimize the operation by breaking it into smaller portions or using other approaches—unless you can guarantee that the data value will stay small. Cursor approaches will not scale.

2 . Filter data wisely.

One alternative to using cursors uses a fall-through approach, filtering and aggregating data in multiple steps via a set of data storages, which could be physical tables, temporary tables, or table variables. It is usually best to include some aggregate filters into aggregate statements to filter out the majority of data in one simple shot whenever necessary, working on smaller amounts of data. Then you can proceed with joining and filtering, making sure to keep the number of join permutations under control at all times.



3. It is usually more efficient to execute multiple statements with one condition than a single statement with multiple OR conditions when executing UPDATE and DELETE statements against permanent database tables that can be accessed by multiple users simultaneously. This tip is especially important from the scalability point of view; from the performance point of view the difference is usually marginal. The major reason for the tip is the locking of the database records and the lock escalations that occur behind the scenes.

4. Make wise distinctions between temp tables and table variables.

Table variables are in-memory structures that may work from 2-100 times faster than temp tables. But keep in mind that access to table variables gets slower as the volume of data they contain grows. At some point, table variables will overflow the available memory and that kills the performance. Therefore, use table variables only when their data content is guaranteed not to grow unpredictably; the breaking size is around several thousand records. For larger data volumes, I recommend temp tables with clustered indexes. Interestingly, I've found that a temp table with one clustered index is often faster than having multiple simple indexes. In contrast, multiple simple indexes with physical tables are often faster than one clustered index.

5. Make careful distinctions between hard rules and assumptions.

This is more of a business design tip, which applies more to code design than to performance and scalability design in general. In real life however, performance and scalability are generally the first things to suffer from improper design. When rules are implemented as assumptions, they usually cause unnecessary calculations to be performed, affecting performance. However, when assumptions are implemented as rules they tend to cause errors and algorithm failures, which usually requires an urgent redesign. That, in turn, is usually performed with business constraints and results in inefficient final algorithms. That's because bad design decisions are often corrected in a rush and without sufficient resources—sometimes under pressure from customers whose businesses are usually in a critical stage when problems are uncovered, but must continue operating during the process.

6. Pay attention to join order.

Using proper join order sometimes lets the database engine generate hints that execute joins with an optimal amount of records. Most database engines also support hard hints, but in most cases you should avoid using hard hints and let the database engine figure out the best way to do its job on its own.

7. Be careful when joining complex views to other views and database tables in complex SELECT statements.

When the database contains a significant amount of data, SQL Server engine tends to recalculate the execution plan of the resulting statement, which often results in an inefficient execution plan and may kill the performance. The most difficult part is that the behavior of SQL Server engine is inconsistent in that respect, and heavily depends on the database size, indexes, foreign keys, and other database structures and constraints. The consistent work-around is to pre-select data from the view into a temp table with the reasonable pre-filters, and then use that temp table in place of the underlying view.

8. Create indexes on temp tables wisely.

As mentioned in Tip 4, clustered indexes are usually the best in terms of performance for temp tables; however, there is a difference between creating the index before or after inserting data into the temp table. Creating the index before the insert complicates the insert, because the database engine must order the selection. For complex selections such as those mentioned in Tip 7, the extra ordering may overcomplicate the overall statement and drastically degrade the performance. On the other hand, creating the index after the insert forces the database engine to recalculate the execution plan of the stored procedure every time it is called. Therefore, the decision is always a trade-off and you should make it based on the relative costs of the two possibilities.

9. In general, try to avoid execution plan recalculation.

One common cause of recalculation occurs when the stored procedure contains several paths that depend on values passed in parameters. However, whether avoiding recalculation is possible depends on the complexity of the stored procedure and on other circumstances, such as those described in tip 8. When the engine does recalculate execution, performance always suffers; however, recalculating the execution plan of the caller does not force the execution plan recalculation of the called procedure (or view or function). Therefore, the workaround is to divide one stored procedure into multiple procedures (depending on the passed-in parameters), and then call the children from the parent conditionally. You should perform this subdivision very carefully though, because it can be a maintenance nightmare—but sometimes it seems to be the only way to achieve acceptable database performance and scalability.

Finally, although this isn't either a performance or a scalability tip, I urge you to format your stored procedure scripts legibly. It's best to agree on common practices such as clause order and formatting rules with your coworkers in advance. Not only does that help avoid errors, it also clearly shows the logical structure of the statements and often aids in figuring out faulty filters and joins.

This list of tips is certainly not exhaustive, but they probably cover the most important performance and scalability factors. Still, there's nothing like an example to drive home the point. The Sudoku solution described in the rest of this article illustrates the techniques in the first six tips.

Read More

Passing datetime parameter to SQL Stored procedure in Query Analyzer

Yesterday I faced problem passing datatime parameter in sql server queryanalyzer, where I was trying to run a procedure UP_ORDER_LOG which expects parameter @co_id, @Prog_id, @event and @event_date

So I tried executing the procedure like

exec UP_ORDER_LOG @co_id='XYZ', @prog_id='ABC', @event='Some event', @event_date=getdate()

exec UP_ORDER_LOG @co_id='XYZ', @prog_id='ABC', @event='Some event', @event_date=getdate

exec UP_ORDER_LOG @co_id='XYZ', @prog_id='ABC', @event='Some event', @event_date=CURRENT_TIMESTAMP

But everytime I got the error

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.

So finally I cracked the above mentioned problem with the solution given below :

declare @eventdate datetime
select @passdate = CURRENT_TIMESTAMP
exec UP_ORDER_LOG @co_id='XYZ', @prog_id='ABC', @event='Some event', @event_date=@eventdate

I hope this will help.
Please do add comments if you get some other solution also.

Read More