Today’s post is a very simple tip based on a question I received from one of my colleagues. They had dates stored in their database and wanted to extract day of the week and day of the year information from the date column.

Now, this would have been a very easy task for the team had this requirement been related to displaying the day of the week and day of the year information on the UI. But, in this particular case, they had to dump it out to a text file (using SQL Server BCP) for exchanging with a legacy reader-board feeding system (If you are unfamiliar with the term, the display boards on railway stations and airports that inform travellers about the schedule and other stages of the boarding process and the associated data stores are reader-board systems). When they approached me, they told me it was a big problem and were wondering if I could spend a day with them to help them figure things out. When I said it won’t even take more than 10 minutes, they were awestruck!

The DATEPART function

Most of us have worked with T-SQL date manipulation functions in Microsoft SQL Server, with the most common use being extracting parts like date, month and year from the date or a date/time value. However, there are many more options, which are what I shared with the team.

Besides extracting parts of a date, the DATEPART function can also help us determine the following for a given date or date/time value:

• The Day of the Week
• The Day of the Year
• Week number
• ISO Week Number

Here’s the associated query:

USE tempdb
GO
DECLARE @currentDate DATE = GETDATE()

SELECT @currentDate                      AS CurrentDate,
       DATEPART(DAYOFYEAR, @currentDate) AS DayOftheYear,
       DATEPART(WEEKDAY  , @currentDate) AS DayOftheWeek,
       DATEPART(WEEK     , @currentDate) AS WeekNumber,
       DATEPART(ISO_WEEK , @currentDate) AS ISOWeekNumber
GO

/*
DAYOFYEAR --Equivalent interval identifiers: DY
WEEKDAY   --Equivalent interval identifiers: DW
WEEK      --Equivalent interval identifiers: W
ISO_WEEK  --Equivalent interval identifiers: ISOWK, ISOWW
*/

For a test value of ‘2012-08-25’, the results are:

All the team had to do was to incorporate the DATEPART function call in their T-SQL queries. There – did it take more than 10 minutes?

Further Reading

If you would like to explore all the options of the DATEPART function, I would redirect you to the Books-On-Line page: http://msdn.microsoft.com/en-us/library/ms174420.aspx

Also, if you would like to read more about how the team exported bulk data from their SQL Server tables into a text/comma-separated file, you may refer my previous posts:

• BCP & Bulk Inserts – Underappreciated features of Microsoft SQL Server
• SSMS – Query Result configuration – Generate comma separated output without BCP or query changes

Until we meet next time,

Be courteous. Drive responsibly.

One of my favourite interview questions at an intermediate level has always been: “Can I have a primary key on a table that does not contribute to the clustered index?”. Of course, it’s not always the same question – there are multiple variants, such as :

• Can a table have a primary key that does not participate in a clustered index?
• Does designating a column(s) as a primary key automatically create a clustered index on the table based on the key column(s)?
• Can a clustered index be created using a column(s) other than the primary key of the table?

The answer that most candidates come up with is that there is a direct dependency between the primary key and the clustered index – the primary key has to be the set of columns on which one wishes to create a clustered index.

Contrary to popular belief, the correct answer is “Yes, a table may have a clustered index  defined on a column other than the primary key of the table”. It’s just that most developers are so used to the default design and coding mechanisms that generally the thought of going against the flow may not come to the mind.

Please note that this post is not about the recommended practices for creation of clustered index and primary keys, but is intended to achieve an understanding of the fact that having a primary key does not automatically imply a clustered index in all situations.

Demo

As supporting proof, allow me to create a simple scenario. As part of the demonstration, I would be executing the following steps:

1. Create a test table without any keys or indexes defined
2. Define a primary key on the test table, with an explicit NONCLUSTERED keyword added to the definition
3. Define a CLUSTERED index on the table on a column that’s not the primary key
4. Check the details of the indexes created using sys.indexes catalog view

USE tempdb
GO

--Safety Check
IF OBJECT_ID('pkTest') IS NOT NULL
    DROP TABLE pkTest
GO

--Create test table definition
CREATE TABLE pkTest (Id INT NOT NULL IDENTITY(1,1),
                     DummyId INT NOT NULL,
                     KeyName VARCHAR(20)
                    )
GO

--Notice the use of NONCLUSTERED 
ALTER TABLE pkTest
    ADD CONSTRAINT pk_pkTest_pkTestId PRIMARY KEY NONCLUSTERED (Id)
GO

--Now create a clustered index on a column other than the Primary Key
CREATE CLUSTERED INDEX idx_pkTest_DummyId ON pkTest (DummyId)
GO

As you can see, we created the Primary Key with a keyword – NONCLUSTERED. This should create a non-clustered index for the primary key and a clustered index for the DummyId column. Let’s run the following query to check:

--Check the types of Indexes that have been created
SELECT si.object_id,
       OBJECT_NAME(si.object_id) AS ObjectName,
       si.name,
       si.index_id,
       si.type,
       si.type_desc,
       si.is_unique,
       si.is_unique_constraint,
       si.is_primary_key
FROM sys.indexes AS si
WHERE si.object_id = OBJECT_ID('pkTest')
GO

Query execution yields the following results:

Conclusion

A clustered index can be defined on any column of a table as long as the column satisfies the criteria for a clustered index. Having a primary key does not imply that clustered index will exist on the primary key under all situations.

Reference

• Clustered Index Design Guidelines (BOL/MSDN): http://msdn.microsoft.com/en-us/library/ms190639(v=sql.105).aspx

Until we meet next time,

Be courteous. Drive responsibly.

Today’s an impromptu post, and the intention is to draw attention to a “hidden” feature of the SQL Server Management Studio (SSMS).

Recently, we were working on designing changes that were required in an XML that was required to be exchanged between the application and the database. The team had gathered in a meeting room for a technical design session – a couple of application developers, a database developer and a database administrator.

We had the SQL Server Management Studio running to look at the table design and create/query test data. To edit the existing XML, the application developers launched an instance of the Visual Studio IDE. The database developer and administrator immediately mentioned that there was no need to launch Visual Studio. The question that came from the application team was – “Why?” The simple answer is

SSMS comes with it’s own XML editor!

Here’s the problem though – it’s not obvious. The XML editor in the SSMS is an extension to the query editor window, which is probably why it’s not obvious. The tools and options related to XML editing are only available if an XML document is actually opened within the SSMS. I prepared a sample XML document for this post, and simply opened it using the SSMS. This is how the SSMS interface changes.

Notice that in the Text Editor toolbar, we now have the additional operations enabled (from Left to Right):

1. Create XML schema based on given XML document
   
2. Reformat Selection
   
3. Format the whole Document
   
4. Show XSLT output
   
5. Debug XSLT
   
6. Cancel XSLT output

The reason one can edit XML documents within the SSMS is because the SSMS is ultimately a Visual Studio shell, inheriting call features common at the shell.

Do you use the XML editor within SSMS? Do leave a note as you go.

Until we meet next time,

Be courteous. Drive responsibly.

Earlier this week, I asked a question regarding the locks that are acquired by SQL Server during Index Maintenance. Before I proceed with answering the question, let’s revisit the types of index maintenance operations:

1. Index Rebuild
   
   
   
   • During a rebuild, the entire index (from the root to the leaf nodes) is rebuilt using the same columns, index type, uniqueness attribute, and sort order
     
   • The rebuild of an index effectively recreates the entire B-tree structure
   
   
2. Index Reorganize
   
   
   
   • Index reorganization, on the other hand, only removes fragmentation at the leaf level
     
   • Intermediate-level pages and the root page are not defragmented

From an availability perspective, Reorganization is better than a rebuild, because reorganization is always an ONLINE process, i.e. it acquires only short-term locks whereas REBUILD prevents any changes to the underlying table until the operation completes, i.e. it acquires long-term locks.

What kinds of locks are acquired during Index Maintenance?

To answer this question, let us perform a small test. We will rebuild an index on one of the table within the AdventureWorks sample database, and perform a reorganize on another table, while monitoring the locks acquired.

Locks during Index Rebuild

In one session to the SQL Server, run the following query to rebuild the index on the table HumanResources.Employee within the AdventureWorks sample database. As you can see, we are not using the ONLINE clause.

USE AdventureWorks2012

GO

ALTER INDEX ALL

    ON HumanResources.Employee REBUILD

GO

While the rebuild is being executed, execute the following query using another query editor window/session to the same SQL Server instance.

USE AdventureWorks2012

GO

SELECT tl.resource_associated_entity_id AS ObjectId,

       OBJECT_NAME(tl.resource_associated_entity_id) AS ObjectName,

       tl.request_mode AS LockRequestMode

FROM sys.dm_tran_locks AS tl

WHERE tl.resource_database_id = DB_ID()

  AND tl.resource_associated_entity_id = OBJECT_ID(‘HumanResources.Employee’)

GO

Here’s the output:

We can see that during a Rebuild, the SQL server requests a Schema Modification Lock (Sch-M) on the underlying object (i.e. “table”). The Sch-M lock prevents concurrent access to the table, meaning that it blocks all outside operations until the locks are released.

Locks during Index ReOrganize

In one session to the SQL Server, run the following query to rebuild the index on the table Sales.SalesOrderDetail within the AdventureWorks sample database. Note that we are not using the ONLINE clause.

USE AdventureWorks2012

GO

ALTER INDEX ALL

    ON Sales.SalesOrderDetail REORGANIZE

GO

While the reorganize is being executed, execute the following query using another query editor window/session to the same SQL Server instance.

USE AdventureWorks2012

GO

SELECT tl.resource_associated_entity_id AS ObjectId,

       OBJECT_NAME(tl.resource_associated_entity_id) AS ObjectName,

       tl.request_mode AS LockRequestMode

FROM sys.dm_tran_locks AS tl

WHERE tl.resource_database_id = DB_ID()

  AND tl.resource_associated_entity_id = OBJECT_ID(‘HumanResources.Employee’)

GO

Here’s the output:

We can see that during a Reorganize, the SQL Server requests effectively an Intent Exclusive lock on the table. The table is therefore locked for definition changes (DDL), but is still available for SELECT operations. This is expected because REORGANIZE is always an ONLINE process.

Conclusions – The correct answer to the question

• Locks acquired on the underlying table during Index Rebuild: Schema Modification Lock (Sch-M)
  
• Locks acquired on the underlying table during Index Reorganize: Intent Exclusive lock (IX)

Who came up with the correct answers?

First of all, I would like to thank everyone who responded. But, this is a competition, and hence, below are the people whose responses were correct (partially correct answers also considered here):

• Olga Medvedeva
  
• Nirav Gandhi
  
• Gaurang Patel
  
• Roji Thomas

And now, for the winners…

The following are the winners, randomly selected from those who participated in the quiz.

• Olga Medvedeva
  
• Roji Thomas

CONGRATULATIONS TO ALL THE WINNERS! Expect my DM on your twitter accounts this week-end (or earlier) with more information on your prize – a Pluralsight training code!

Check out: http://www.pluralsight-training.net/microsoft for a detailed list of the courses available. Did you know that Pluralsight has courses spanning almost every Microsoft technology currently available in the Market? If you are a blogger, do write about how you used the Pluralsight trainings – which trainings you took, and what is your feedback.

Interesting Reading/Reference:

Locking and Index Maintenance within SQL Server is a very interesting topic, and here are some resources to help you read/know more:

• Lock Modes in SQL Server (MSDN/BOL): http://msdn.microsoft.com/en-us/library/ms175519(SQL.105).aspx
  
• Locks Occupied by the WITH (NOLOCK) hint: http://blog.sqlauthority.com/2011/05/08/sql-server-what-kind-of-lock-with-nolock-hint-takes-on-object/
  
• “SQL Server Performance: Indexing Basics” (Pinal Dave, Vinod Kumar) – Pluralsight Online courses: http://www.pluralsight.com/training/Courses/TableOfContents/sql-server-indexing

Until we meet next time,

Be courteous. Drive responsibly.

Edit: August 17, 2012: Statement “The table is therefore locked for changes” has been changed to “The table is therefore locked for definition changes (DDL)” for clarity.

What is the first word that comes to mind whenever one mentions the need for optimizing search or for increasing the performance of a database? Index.

Indexes play a vital role in SQL Server performance tuning, but they too need periodic maintenance. An index is logically implemented as a B-tree.

Over time as data is accumulated by the table, fragmentation may set in and the distribution of data within the index may become unbalanced, with some pages becoming full (as governed by the fill factor) leading to “page splits” whereas others might be sparsely filled. If data is deleted from the table, it leaves behind a “hole” in the index page – this free space is not reclaimed, which contributes to the bloating of the index. This reduces the efficiency of the indexes, while possibly consuming more than required disk space. At this stage, administrators need to perform maintenance operations on the index.

Index Maintenance operations can be classified into two:

1. Index Rebuild
   
   
   
   • During a rebuild, the entire index (from the root to the leaf nodes) is rebuilt using the same columns, index type, uniqueness attribute, and sort order
     
   • The rebuild of an index effectively recreates the entire B-tree structure
   
   
2. Index Reorganize
   
   
   
   • Index reorganization, on the other hand, only removes fragmentation at the leaf level
     
   • Intermediate-level pages and the root page are not defragmented

From an availability perspective, Reorganization is better than a rebuild, because reorganization is always an ONLINE process, i.e. it acquires only short-term locks whereas REBUILD prevents any changes to the underlying table until the operation completes, i.e. it acquires long-term locks. The question therefore is:

What types of locks do index REBUILD and index REORGANIZE operations occupy on the underlying table, by default (i.e. the “ONLINE” option is not specified)?

Bonus Exercise

As a bonus exercise, you may want to share any queries or tests that you may have conducted to arrive at the answer to the question above. Please note that the bonus exercise does not carry any rewards this time around – it is only as an exercise for the brain cells.

Rules

The rules are very simple:

1. You can answer these questions till Wednesday 22:00hrs (IST)/Wednesday 12:30hrs (US-EDT) – August 15, 2012
   
2. Answers will be shared and winners declared in my post on Thursday, August 16, 2012
   
3. You can answer multiple times – however, at the end of the day, only one entry would be considered
   
4. Follow the following on Twitter!
   
   
   
   • Pluralsight (Handle is: @pluralsight)
     
   • BeyondRelational.com (Handle: @beyondrel)
     
   • Me! (Handle is: @nakulv_sql )
   
   
5. Winners will be chosen randomly
   
6. Prizes will be given out through Twitter – hence, it is important that you follow me

Remember: Participation is always more important! Make sure you reply to the quiz even if it’s just a guess!

Until we meet next time,

Be courteous. Drive responsibly.