Here is the script, which I executed.

USE TempDB
GO
-- Create sequence
CREATE SEQUENCE dbo.SequenceID AS BIGINT
START WITH 3
INCREMENT BY 1
MINVALUE 1
MAXVALUE 5
CYCLE
NO CACHE;
GO
-- Following will return 3
SELECT next value FOR dbo.SequenceID;
-- Following will return 4
SELECT next value FOR dbo.SequenceID;
-- Following will return 5
SELECT next value FOR dbo.SequenceID;
-- Following will return which number
SELECT next value FOR dbo.SequenceID;
-- Clean up
DROP SEQUENCE dbo.SequenceID;
GO

Above script gave me following resultset.

3 is the starting value and 5 is the maximum value. Once Sequence reaches to maximum value what happens? and WHY?

Bonus question: If you use UNION between 2 SELECT statement which uses UNION, it also throws an error. What is the reason behind it?

Can you attempt to answer this question without running this code in SQL Server 2012. I am very confident that irrespective of SQL Server version you are running you will have great learning. I will follow up of the answer in comments below.

Reference: Pinal Dave (http://blog.sqlauthority.com)

What is NULL?

It’s always fun to ask this question during interviews, because in every interview, I get a different answer. NULL is often confused with false, absence of value or infinite value. Honestly, NULL is a very interesting subject as it bases its behavior in server settings. There are a few properties of NULL that are universal, but the knowledge about these properties is not known in a universal sense.

Let us run this simple puzzle. Run the following T-SQL script:

SELECT SUM(data)
FROM (SELECT NULL AS data) t


It will return the following error:

Msg 8117, Level 16, State 1, Line 1
Operand data type NULL is invalid for sum operator.

Now the error makes it very clear that NULL is invalid for sum Operator. Frequently enough, I have showed this simple query to many folks whom I came across. I asked them if they could modify the subquery and return the result as NULL. Here is what I expected:

Even though this is a very simple looking query, so far I’ve got the correct answer from only 10% of the people to whom I have asked this question. It was common for me to receive this kind of answer – convert the NULL to some data type. However, doing so usually returns the value as 0 or the integer they passed.

SELECT SUM(data)
FROM (SELECT ISNULL(NULL,0) AS data) t

I usually see many people modifying the outer query to get desired NULL result, but that is not allowed in this simple puzzle. This small puzzle made me wonder how many people have a clear understanding about NULL.

Well, here is the answer to my simple puzzle. Just CAST NULL AS INT and it will return the final result as NULL:

SELECT SUM(data)
FROM (SELECT CAST(NULL AS INT) AS data) t


Now that you know the answer, don’t you think it was very simple indeed? This blog post is especially dedicated to my friend Madhivanan who has written an excellent blog post about NULL. I am confident that after reading the blog post from Madhivanan, you will have no confusion regarding NULL in the future. Read: NULL, NULL, NULL and nothing but NULL.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Server management Studio 2012 is wonderful tool and has many different features. Many times, an average user does not use them as they are not aware about these features. Today, we will learn one such feature. SSMS comes with many inbuilt performance and activity reports, but we do not use it to the full potential.

Connect to SQL Server Node >> Right Click on it >> Go to Reports >> Click on Standard Reports >> Pick Any Report.

Please note that some of the reports can be IO intensive and not suggested to run during business hours!

More on Standard Reports:

SQL SERVER – Out of the Box – Activity and Performance Reports from SSSMS
SQL SERVER – Generate Report for Index Physical Statistics – SSMS
SQL SERVER – Configure Management Data Collection in Quick Steps

I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.

If we like your idea we promise to share with you educational material.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Let me start with the question: What is the precision of the SMALLDATETIME datatypes?

What is your answer? Write it down on your notepad.

Now if you do not want to continue reading the blog post, head to my previous blog post over here: SQL SERVER – Precision of SMALLDATETIME.

A Social Media Question

Since the increase of social media conversations, I noticed that the amount of the comments I receive on this blog is a bit staggering. I receive lots of questions on facebook, twitter or Google+. One of the very interesting questions yesterday was asked on Facebook by Raghavendra. I am re-organizing his script and asking all of the questions he has asked me. Let us see if we could help him with his question:

CREATE TABLE #temp
(name VARCHAR(100),registered smalldatetime)
GO
DECLARE @test smalldatetime
SET @test=GETDATE()
INSERT INTO #temp
VALUES ('Value1',@test)
INSERT INTO #temp
VALUES ('Value2',@test)
GO
SELECT *
FROM #temp
ORDER BY registered DESC
GO
DROP TABLE #temp
GO

Now when the above script is ran, we will get the following result:

Well, the expectation of the query was to have the following result. The row which was inserted last was expected to return as first row in result set as the ORDER BY descending.

Side note: Because the requirement is to get the latest data, we can’t use any  column other than smalldatetime column in order by. If we use name column in the order by, we will get an incorrect result as it can be any name.

My Initial Reaction

My initial reaction was as follows:

1) DataType DateTime2: If file precision of the column is expected from the column which store date and time, it should not be smalldatetime. The precision of the column smalldatetime is One Minute (Read Here) for finer precision use DateTime or DateTime2 data type.

Here is the code which includes above suggestion:

CREATE TABLE #temp
(name VARCHAR(100), registered datetime2)
GO
DECLARE @test datetime2
SET @test=GETDATE()
INSERT INTO #temp
VALUES ('Value1',@test)
INSERT INTO #temp
VALUES ('Value2',@test)
GO
SELECT *
FROM #temp
ORDER BY registered DESC
GO
DROP TABLE #temp
GO

2) Tie Breaker Identity: There are always possibilities that two rows were inserted at the same time. In that case, you may need a tie breaker. If you have an increasing identity column, you can use that as a tie breaker as well.

CREATE TABLE #temp
(ID INT IDENTITY(1,1), name VARCHAR(100),registered datetime2)
GO
DECLARE @test datetime2
SET @test=GETDATE()
INSERT INTO #temp
VALUES ('Value1',@test)
INSERT INTO #temp
VALUES ('Value2',@test)
GO
SELECT *
FROM #temp
ORDER BY ID DESC
GO
DROP TABLE #temp
GO

Those two were the quick suggestions I provided. It is not necessary that you should use both advices. It is possible that one can use only DATETIME datatype or Identity column can have datatype of BIGINT or have another tie breaker.

An Alternate NO Solution

In the facebook thread this was also discussed as one of the solutions:

CREATE TABLE #temp
(name VARCHAR(100),registered smalldatetime)
GO
DECLARE @test smalldatetime
SET @test=GETDATE()
INSERT INTO #temp
VALUES ('Value1',@test)
INSERT INTO #temp
VALUES ('Value2',@test)
GO
SELECT name, registered,
ROW_NUMBER() OVER(ORDER BY registered DESC) AS "Row Number"
FROM #temp ORDER BY 3 DESC
GO
DROP TABLE #temp
GO

However, I believe it is not the solution and can be further misleading if used in a production server. Here is the example of why it is not a good solution:

CREATE TABLE #temp
(name VARCHAR(100) NOT NULL,registered smalldatetime)
GO
DECLARE @test smalldatetime
SET @test=GETDATE()
INSERT INTO #temp
VALUES ('Value1',@test)
INSERT INTO #temp
VALUES ('Value2',@test)
GO
-- Before Index
SELECT name, registered,
ROW_NUMBER() OVER(ORDER BY registered DESC) AS "Row Number"
FROM #temp ORDER BY 3 DESC
GO
-- Create Index
ALTER TABLE #temp
ADD CONSTRAINT [PK_#temp] PRIMARY KEY CLUSTERED
(name DESC)
GO
-- After Index
SELECT name, registered,
ROW_NUMBER() OVER(ORDER BY registered DESC) AS "Row Number"
FROM #temp ORDER BY 3 DESC
GO
DROP TABLE #temp
GO


Now let us examine the resultset. You will notice that an index which is created on the base table which is (indeed) schema change the table but can affect the resultset. As you can see, an index can change the resultset, so this method is not yet perfect to get the latest inserted resultset.

No Schema Change Requirement

After giving these two suggestions, I was waiting for the feedback of the asker. However, the requirement of the asker was there can’t be any schema change because the application was used by many other applications. I validated again, and of course, the requirement is no schema change at all. No addition of the column of change of datatypes of any other columns. There is no further help as well.

This is indeed an interesting question. I personally can’t think of any solution which I could provide him given the requirement of no schema change. Can you think of any other solution to this?

Need of Database Designer

This question once again brings up another ancient question:  “Do we need a database designer?” I often come across databases which are facing major performance problems or have redundant data. Normalization is often ignored when a database is built fast under a very tight deadline. Often I come across a database which has table with unnecessary columns and performance problems. While working as Developer Lead in my earlier jobs, I have seen developers adding columns to tables without anybody’s consent and retrieving them as SELECT *.  There is a lot to discuss on this subject in detail, but for now, let’s discuss the question first. Do you have any suggestions for the above question?

Reference: Pinal Dave (http://blog.sqlauthority.com)

Background Story

Recently I came across system where users have changed the name of the few of the table to match their new standard naming convention. The name of the table should be self explanatory and they should have explain their purpose without either opening it or reading documentations. Well, not every time this is possible but again this should be the goal of any database modeler. Well, I no way encourage the name of the tables to be too long like ‘ContainsDetailsofNewInvoices’. May be the name of the table should be ‘Invoices’ and table should contain a column with New/Processed bit filed to indicate if the invoice is processed or not (if necessary). Coming back to original story, the database had several tables of which the name were changed.

Story Continues…

To continue the story let me take simple example. There was a table with the name  ’ReceivedInvoices’, it was changed to new name as ‘TblInvoices’. As per their new naming standard they had to prefix every talbe with the words ‘Tbl’ and prefix every view with the letters ‘Vw’. Personally I do not see any need of the prefix but again, that issue is not here to discuss.  Now after changing the name of the table they faced very interesting situation. They had few indexes on the table which had name of the table. Let us take an example.

Old Name of Table: ReceivedInvoice
Old Name of Index: Index_ReceivedInvoice1

Here is the new names

New Name of Table: TblInvoices
New Name of Index: ???

Well, their dilemma was what should be the new naming convention of the Indexes. Here is a quick proposal of the Index naming convention. Do let me know your opinion.

If Index is Primary Clustered Index: PK_TableName
If Index is  Non-clustered Index: IX_TableName_ColumnName1_ColumnName2…
If Index is Unique Non-clustered Index: UX_TableName_ColumnName1_ColumnName2…
If Index is Columnstore Non-clustered Index: CL_TableName

Here ColumnName is the column on which index is created. As there can be only one Primary Key Index and Columnstore Index per table, they do not require ColumnName in the name of the index. The purpose of this new naming convention is to increase readability. When any user come across this index, without opening their properties or definition, user can will know the details of the index.

T-SQL script to Rename Indexes

Here is quick T-SQL script to rename Indexes

EXEC sp_rename N'SchemaName.TableName.IndexName', N'New_IndexName', N'INDEX';
GO

Your Contribute Please

Well, the organization has already defined above four guidelines, personally I follow very similar guidelines too. I have seen many variations like adding prefixes CL for Clustered Index and NCL for Non-clustered Index. I have often seen many not using UX prefix for Unique Index but rather use generic IX prefix only.

Now do you think if they have missed anything in the coding standard. Is NCI and CI prefixed required to additionally describe the index names. I have once received suggestion to even add fill factor in the index name – which I do not recommend at all.

What do you think should be ideal name of the index, so it explains all the most important properties? Additionally, you are welcome to vote if you believe changing the name of index is just waste of time and energy. 

Note: The purpose of the blog post is to encourage all to participate with their ideas. I will write follow up blog posts in future compiling all the suggestions.

Reference: Pinal Dave (http://blog.sqlauthority.com)

I have noticed that when I talk about Codeplex is user community, not everybody knows about it. The quickest way I explain what is codeplex is that I start naming few of the projects which are available there and suddenly I start noticing a few hands going up knowing the projects. This is indirect way to prove that many of us know CodePlex usability but do not pay special attention to what it is actually.

Let me name a few popular projects of the CodePlex here.

1. SQL Server Sample Database [link]
2. Image Resizer for Windows [link]
3. Ajax Control Toolkit [link]
4. Skype Voice Changer [link]
5. Silverlight Toolkit [link]
6. Windows 7 USB/DBD Download Tool [link]
7. Orchard Project [link]

There are very interesting SQL Server projects available on Codeplex as well. I am listing few of them here for reference in listed in no particular order.

1. SQL Server Sample Database [link]
2. SQL Server Compact ToolBox [link]
3. Microsoft Drivers for PHP for SQL Server [link]
4. Internals Viewer for SQL Server [link]
5. SQL Server Spatial Tooks [link]
6. SQL Monitor – managing sql server performance [link]
7. SQL Server 2008 Extended Events SSMS Addin [link]

Reference: Pinal Dave (http://blog.sqlauthority.com)

Here is what we are going to do. We will run the script one at time instead of running them all together and try to guess the answer. I am confident that many will get it correct but if you do not get correct, you learn something new.

Let us create database and sample table.

CREATE DATABASE DB2012
GO
USE DB2012
GO
CREATE TABLE TableDT
(DT1 VARCHAR(100), DT2 DATETIME2,
DT1C AS DT1, DT2C AS DT2);
INSERT INTO TableDT (DT1, DT2)
SELECT GETDATE(), GETDATE()
GO

There are four columns in the table. The first column DT1 is regular VARCHAR and second DT2 is DATETIME2. Both of the column are been populated with the same data as I have used the function GETDATE(). Now let us do the SELECT statement and get the result from both the columns.

Before running the query please guess the answer and write it down on the paper or notepad.

Question 1: Guess the resultset

SELECT DT1, DT2
FROM TableDT
GO

Now once again run the select statement on the same table but this time retrieve the computed columns only. Once again I suggest you write down the result on the notepad.

Question 2: Guess the resultset

SELECT DT1C, DT2C
FROM TableDT
GO

Now here is the best part. Let us use the CAST function over the computed columns. Here I do want you to stop and guess the answer for sure. If you have not done it so far, stop do it, believe me you will like it.

Question 3: Guess the resultset

SELECT CAST(DT1C AS DATETIME2) CDT1C,
CAST(DT2C AS DATETIME2) CDT1C
FROM TableDT
GO


Now let us inspect all the answers together and see how many of you got it correct.

Answer 1:

Answer 2:

Answer 3: 

If you have not tried to run the script so far, you can execute all the three of the above script together over here and see the result together.

SELECT CAST(DT1C AS DATETIME2) CDT1C,
CAST(DT2C AS DATETIME2) CDT1C
FROM TableDT
GO

Here is the Saturday Fun question to you – why do we get same result from both of the expressions in Question 3, where as in question 2 both the expression have different answer. I will publish the valid answer with explanation in future blog posts.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Blog | LinkedIn | Twitter | Google+

Let us hear the thoughts of Nakul in first person -

Those who have been following my blogs would be aware that I am recently running a series on the database engine features that have been deprecated in Microsoft SQL Server 2012. Based on the response that I have received, I was quite surprised to know that most of the audience found these to be breaking changes, when in fact, they were not! It was then that I decided to write a little piece on how to plan your database upgrade such that it works with the next version of Microsoft SQL Server.

Please note that the recommendations made in this article are high-level markers and are intended to help you think over the specific steps that you would need to take to upgrade your database.

Refer the documentation – Understand the terms

Change is the only constant in this world. Therefore, whenever customer requirements, newer architectures and designs require software vendors to make a change to the keywords, functions, etc; they ensure that they provide their end users sufficient time to migrate over to the new standards before dropping off the old ones. Microsoft does that too with it’s Microsoft SQL Server product. Whenever a new SQL Server release is announced, it comes with a list of the following features:

1. Breaking changes
   1. These are changes that would break your currently running applications, scripts or functionalities that are based on earlier version of Microsoft SQL Server
   2. These are mostly features whose behavior has been changed keeping in mind the newer architectures and designs
   3. Lesson: These are the changes that you need to be most worried about!
2. Discontinued features
   1. These features are no longer available in the associated version of Microsoft SQL Server
   2. These features used to be “deprecated” in the prior release
   3. Lesson: Without these changes, your database would not be compliant/may not work with the version of Microsoft SQL Server under consideration
3. Deprecated features
   1. These features are those that are still available in the current version of Microsoft SQL Server, but are scheduled for removal in a future version. These may be removed in either the next version or any other future version of Microsoft SQL Server
   2. The features listed for deprecation will compose the list of discontinued features in the next version of SQL Server
   3. Lesson: Plan to make necessary changes required to remove/replace usage of the deprecated features with the latest recommended replacements

Once a feature appears on the list, it moves from bottom to the top, i.e. it is first marked as “Deprecated” and then “Discontinued”. We know of “Breaking change” comes later on in the product life cycle.

What this means is that if you want to know what features would not work with SQL Server 2012 (and you are currently using SQL Server 2008 R2), you need to refer the list of breaking changes and discontinued features in SQL Server 2012.

Use the tools!

There are a lot of tools and technologies around us, but it is rarely that I find teams using these tools religiously and to the best of their potential. Below are the top two tools, from Microsoft, that I use every time I plan a database upgrade.

The SQL Server Upgrade Advisor

Ever since SQL Server 2005 was announced, Microsoft provides a small, very light-weight tool called the “SQL Server upgrade advisor”. The upgrade advisor analyzes installed components from earlier versions of SQL Server, and then generates a report that identifies issues to fix either before or after you upgrade. The analysis examines objects that can be accessed, such as scripts, stored procedures, triggers, and trace files. Upgrade Advisor cannot analyze desktop applications or encrypted stored procedures.

Refer the links towards the end of the post to know how to get the Upgrade Advisor.

The SQL Server Profiler

Another great tool that you can use is the one most SQL Server developers & administrators use often – the SQL Server profiler. SQL Server Profiler provides functionality to monitor the “Deprecation” event, which contains:

• Deprecation announcement – equivalent to features to be deprecated in a future release of SQL Server
• Deprecation final support – equivalent to features to be deprecated in the next release of SQL Server

You can learn more using the links towards the end of the post.

A basic checklist

There are a lot of finer points that need to be taken care of when upgrading your database. But, it would be worth-while to identify a few basic steps in order to make your database compliant with the next version of SQL Server:

1. Monitor the current application workload (on a test bed) via the Profiler in order to identify usage of features marked as Deprecated
   1. If none appear, you are all set! (This almost never happens)
   2. Note down all the offending queries and feature usages
2. Run analysis sessions using the SQL Server upgrade advisor on your database
3. Based on the inputs from the analysis report and Profiler trace sessions,
   1. Incorporate solutions for the breaking changes first
   2. Next, incorporate solutions for the discontinued features
4. Revisit and document the upgrade strategy for your deployment scenarios
5. Revisit the fall-back, i.e. rollback strategies in case the upgrades fail
   1. Because some programming changes are dependent upon the SQL server version, this may need to be done in consultation with the development teams
6. Before any other enhancements are incorporated by the development team, send out the database changes into QA
   1. QA strategy should involve a comparison between an environment running the old version of SQL Server against the new one
   2. Because minimal application changes have gone in (essential changes for SQL Server version compliance only), this would be possible
7. As an ongoing activity, keep incorporating changes recommended as per the deprecated features list
8. As a DBA, update your coding standards to ensure that the developers are using ANSI compliant code – this code will require a change only if the ANSI standard changes

Remember this: Change management is a continuous process. Keep revisiting the product release notes and incorporate recommended changes to stay prepared for the next release of SQL Server.

May the power of SQL Server be with you!

Links Referenced in this post

• Breaking changes in SQL Server 2012: Link
• Discontinued features in SQL Server 2012: Link
• Get the upgrade advisor from the Microsoft Download Center at: Link
• Upgrade Advisor page on MSDN: Link
• Profiler: Review T-SQL code to identify objects no longer supported by Microsoft: Link
• Upgrading to SQL Server 2012 by Vinod Kumar: Link

Reference: Pinal Dave (http://blog.sqlauthority.com)

“If you want to upgrade just one database from a legacy instance of SQL Server and not upgrade the other databases on the server, use the side-by-side upgrade method instead of the in-place method.”

There are so many trivia, tips and tricks that make creating the list seems humanly impossible given a short period of time. My friend Vinod Kumar, an SQL Server expert, wrote a very interesting article on SQL Server 2012 Upgrade before. In that article, Vinod addressed the most interesting and practical questions related to upgrades. He started with the fundamentals of how to start backup before upgrade and ended with fail-safe strategies after the upgrade is over. He covered end-to-end concepts in his blog posts in simple words in extremely precise statements.

A successful upgrade uses a cycle of: planning, document process, testing, refine process, testing, planning upgrade window, execution, verifying of upgrade and opening for business. If you are at Vinod’s blog post, I suggest you go all the way down and collect the gold mine of most important links. I have bookmarked the blog by blogging about it and I suggest that you bookmark it as well with the way you prefer.

Vinod Kumar’s blog post on SQL Server 2012 Upgrade Technical Guide

SQL Server 2012 Upgrade Technical Guide is a detailed resource that’s also available online for free. Each chapter was carefully crafted and explained in detail. Here is a quick list of the chapters included in the whitepaper. Before downloading the guide, beware of its size of 9 MB and 454 pages.

Here’s the list of chapters:

Chapter 1: Upgrade Planning and Deployment
Chapter 2: Management Tools
Chapter 3: Relational Databases
Chapter 4: High Availability
Chapter 5: Database Security
Chapter 6: Full-Text Search
Chapter 7: Service Broker
Chapter 8: SQL Server Express
Chapter 9: SQL Server Data Tools
Chapter 10: Transact-SQL Queries
Chapter 11: Spatial Data
Chapter 12: XML and XQuery
Chapter 13: CLR
Chapter 14: SQL Server Management Objects
Chapter 15: Business Intelligence Tools
Chapter 16: Analysis Services
Chapter 17: Integration Services
Chapter 18: Reporting Services
Chapter 19: Data Mining
Chapter 20: Other Microsoft Applications and Platforms
Appendix 1: Version and Edition Upgrade Paths
Appendix 2: SQL Server 2012: Upgrade Planning Checklist

Download SQL Server 2012 Upgrade Technical Guide [454 pages and 9 MB]

Reference: Pinal Dave (http://blog.sqlauthority.com)

This five part series is written with keeping SQL Server Exam 70-433. Instead of just focusing on what will be there in exam, this series is focusing on learning the important concepts thoroughly. This book no way take short cut to explain any concepts and at times, will go beyond the topic at length. The best part is that all the books has many companion videos explaining the concepts and videos. Every Wednesday I like to post a video which explains something in quick few seconds. Today we will go over five videos which I posted in my earlier posts related to Joes 2 Pros series.

Introduction to XML Data Type Methods – SQL in Sixty Seconds #015

The XML data type was first introduced with SQL Server 2005. This data type continues with SQL Server 2008 where expanded XML features are available, most notably is the power of the XQuery language to analyze and query the values contained in your XML instance.

There are five XML data type methods available in SQL Server 2008:

query() – Used to extract XML fragments from an XML data type.
value() – Used to extract a single value from an XML document.
exist() – Used to determine if a specified node exists. Returns 1 if yes and 0 if no.
modify() – Updates XML data in an XML data type.
node() – Shreds XML data into multiple rows (not covered in this blog post).

Introduction to SQL Error Actions – SQL in Sixty Seconds #014

Most people believe that when SQL Server encounters an error severity level 11 or higher the remaining SQL statements will not get executed. In addition, people also believe that if any error severity level of 11 or higher is hit inside an explicit transaction, then the whole statement will fail as a unit. While both of these beliefs are true 99% of the time, they are not true in all cases. It is these outlying cases that frequently cause unexpected results in your SQL code.

To understand how to achieve consistent results you need to know the four ways SQL Error Actions can react to error severity levels 11-16:

Statement Termination – The statement with the procedure fails but the code keeps on running to the next statement. Transactions are not affected.
Scope Abortion – The current procedure, function or batch is aborted and the next calling scope keeps running. That is, if Stored Procedure A calls B and C, and B fails, then nothing in B runs but A continues to call C. @@Error is set but the procedure does not have a return value.
Batch Termination – The entire client call is terminated.
XACT_ABORT – (ON = The entire client call is terminated.) or (OFF = SQL Server will choose how to handle all errors.)

[Detailed Blog Post] | [Quiz with Answer]

Introduction to Basics of a Query Hint – SQL in Sixty Seconds #013

Query hints specify that the indicated hints should be used throughout the query. Query hints affect all operators in the statement and are implemented using the OPTION clause. Cautionary Note: Because the SQL Server Query Optimizer typically selects the best execution plan for a query, it is highly recommended that hints be used as a last resort for experienced developers and database administrators to achieve the desired results.

[Detailed Blog Post] | [Quiz with Answer]

Introduction to Hierarchical Query – SQL in Sixty Seconds #012

A CTE can be thought of as a temporary result set and are similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. A CTE is generally considered to be more readable than a derived table and does not require the extra effort of declaring a Temp Table while providing the same benefits to the user. However; a CTE is more powerful than a derived table as it can also be self-referencing, or even referenced multiple times in the same query. A recursive CTE requires four elements in order to work properly:

Anchor query (runs once and the results ‘seed’ the Recursive query)
Recursive query (runs multiple times and is the criteria for the remaining results)
UNION ALL statement to bind the Anchor and Recursive queries together.
INNER JOIN statement to bind the Recursive query to the results of the CTE.

[Detailed Blog Post] | [Quiz with Answer]

Introduction to SQL Server Security – SQL in Sixty Seconds #011

Let’s get some basic definitions down first. Take the workplace example where “Tom” needs “Read” access to the “Financial Folder”. What are the Securable, Principal, and Permissions from that last sentence?

A Securable is a resource that someone might want to access (like the Financial Folder).
A Principal is anything that might want to gain access to the securable (like Tom).
A Permission is the level of access a principal has to a securable (like Read).

[Detailed Blog Post] | [Quiz with Answer]

Please leave a comment explain which one was your favorite video as that will help me understand what works and what needs improvement.

Reference: Pinal Dave (http://blog.sqlauthority.com)

I was going over forums and I noticed that at one place user has used following code to get Schema Name from ObjectID.

USE AdventureWorks2012
GO
SELECT s.name AS SchemaName, t.name AS TableName,
s.schema_id, t.OBJECT_ID
FROM sys.Tables t
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE t.name = OBJECT_NAME(46623209)
GO

Before I continue let me say I do not see anything wrong with this script. It is just fine and one of the way to get SchemaName from Object ID.

However, I have been using function OBJECT_SCHEMA_NAME to get the schema name. If I have to write the same code from the beginning I would have written the same code as following.

SELECT OBJECT_SCHEMA_NAME(46623209) AS SchemaName, t.name AS TableName,
t.schema_id, t.OBJECT_ID
FROM sys.tables t
WHERE t.name = OBJECT_NAME(46623209)
GO

Now, both of the above code give you exact same result. If you remove the WHERE condition it will give you information of all the tables of the database.

Now the question is which one is better – honestly – it is not about one is better than other. Use the one which you prefer to use. I prefer to use second one as it requires less typing. Let me ask you the same question to you – which method to get schema name do yo use? and Why?

Reference: Pinal Dave (http://blog.sqlauthority.com)

The first script which I had written was with DMV - sys.column_store_index_stats. This DMV was displaying the statistics of the columnstore indexes. When I attempted to run it on SQL Server 2012 RTM it gave me error suggesting that this DMV does not exists.

Here is the script which I ran:

SELECT *
FROM sys.column_store_index_stats;

It generated following error:

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘column_store_index_stats’.

I was pretty confident that this DMV was available when I had written the scripts. The next reaction was to type ‘sys.’ only in SSMS and wait for intelisense to popup DMV list. I scrolled down and noticed that above said DMV did not exists there as well.

Now this is not bug or missing feature. This was indeed something can happen because the version which I was practicing was early CTP version. If you go to the page of the DMV here, it clearly stats notice on the top of the page.

This documentation is for preview only, and is subject to change in later releases.

Now this was not alarming but my next thought was if this DMV is not there where can I find the information which this DMV was providing. Well, while I was thinking about this, I noticed that my another friend Balmukund Lakhani was online on personal messenger. Well, Balmukund is “Know All” kid. I have yet to find situation where I have not got my answers from him. I immediately pinged him and asked the question regarding where can I find information of ‘column_store_index_stats’. His answer was very abrupt but enlightening for sure. Here is our conversation:

Pinal: Where can I find information of column_store_index_stats?
Balmukund: Assume you have never worked with CTP before and now try to find the information which you are trying to find.

Honestly  it was fantastic response from him. I was confused as I have played extensively with CTP versions of SQL Server 2012. Now his response give me big hint. I should have not looked for DMV but rather should have focused on what I wanted to do. I wanted to retrieve the statistics related to the index. In SQL Server 2008/R2, I was able to retrieve the statistics of the index from the DMV - sys.dm_db_index_usage_stats. I used the same DMV on SQL Server 2012 and it did retrieved the necessary information for me.

Here is the updated script which gave me all the necessary information I was looking for. Matter of the fact, if I have used my earlier SQL Server 2008 R2 script this would have just worked fine.

SELECT DB_NAME(Database_ID) DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(ius.OBJECT_ID) ObjName,
i.type_desc, i.name,
user_seeks, user_scans,
user_lookups, user_updates,*
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i
ON i.index_id = ius.index_id
AND ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.tables t ON t.OBJECT_ID = i.OBJECT_ID
GO

Let us see the resultset of above query.

You will notice that column Type_desc describes the type of the index. You can additionally write WHERE condition on the column and only retrieve only selected type of Index.

Reference: Pinal Dave (http://blog.sqlauthority.com)

• Multidimensional modeling – enables BI professionals to create sophisticated multidimensional cubes using traditional online analytical processing (OLAP).
• Tabular modeling – provides self-service data modeling capabilities to business and data analysts.

Do let me know what do is your opinion as a comment. In simple word – I would like to know when will you use Tabular modeling and when Multidimensional modeling?

Download Choosing a Tabular or Multidimensional Modeling Experience in SQL Server 2012 Analysis Services

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

The SQL Server 2012 Developer Training Kit includes technical content including labs, demos and presentations designed to help you learn how to develop SQL Server 2012 database and BI solutions. New and updated content will be released periodically and can be downloaded on-demand using the Web Installer.

Download SQL Server 2012 Developer Training Kit Web Installer.

This training kit was available earlier this year but it is never late to explore it if you have not referred it earlier. Additionally, if you do not want to download complete kit all together I suggest you refer to Wiki here. This wiki contains all the same presentations and demo notes which web installer contains.

Refer to SQL Server 2012 Developer Training Kit Wiki

Wiki contains following module and details about Hands On Labs
Module 1: Introduction to SQL Server 2012
Module 2: Introduction to SQL Server 2012 AlwaysOn
Module 3: Exploring and Managing SQL Server 2012 Database Engine Improvements
Module 4: SQL Server 2012 Database Server Programmability
Module 5: SQL Server 2012 Application Development
Module 6: SQL Server 2012 Enterprise Information Management
Module 7: SQL Server 2012 Business Intelligence
Hands-On Labs: SQL Server 2012 Database Engine
Hands-On Labs: Visual Studio 2010 and .NET 4.0
Hands-On Labs: SQL Server 2012 Enterprise Information Management
Hands-On Labs: SQL Server 2012 Business Intelligence
Hands-On LabsHands-On Labs: Windows Azure and SQL Azure

As I said, if you have not downloaded this so far, it is never late to explore it. Trust me you will atleast learn one thing if you just explore the content.

Reference: Pinal Dave (http://blog.sqlauthority.com)

This blog post is inspired from SQL Interoperability Joes 2 Pros: A Guide to Integrating SQL Server with XML, C#, and PowerShell – SQL Exam Prep Series 70-433 – Volume 5.

[Amazon] | [Flipkart] | [Kindle] | [IndiaPlaza]

This is follow up blog post of my earlier blog post on the same subject - SQL SERVER – Introduction to Discovering XML Data Type Methods – A Primer. In the article we discussed various basics terminology of the XML. The article further covers following important concepts of XML.

• What are XML Data Type Methods
• The query() Method
• The value() Method
• The exist() Method
• The modify() Method

Above five are the most important concepts related to XML and SQL Server. There are many more things one has to learn but without beginners fundamentals one can’t learn the advanced  concepts. Let us have small quiz and check how many of you get the fundamentals right.

Quiz

1.) Which method returns an XML fragment from the source XML?

1. query( )
2. value( )
3. exist( )
4. modify( )
5. All of them
6. Only query( ) and value( )

2.) Which XML data type method returns a “1” if found and “0” if the specified XPath is not found in the source XML?

1. query( )
2. value( )
3. exist( )
4. modify( )
5. All of them
6. Only query( ) and value( )

3.) Which XML data type method allows you to pick the data type of the value that is returned from the source XML?

1. query( )
2. value( )
3. exist( )
4. modify( )
5. All of them
6. Only query( ) and value( )

4.) Which method will not work with a SQL SELECT statement?

1. query( )
2. value( )
3. exist( )
4. modify( )
5. All of them
6. Only query( ) and value( )

Now make sure that you write down all the answers on the piece of paper.

Watch following video and read earlier article over here. If you want to change the answer you still have chance.

Solution

1) 1

2) 3

3) 2

4) 4

Now compare let us check the answers and compare your answers to following answers. I am very confident you will get them correct.

Reference: Pinal Dave (http://blog.sqlauthority.com)

This blog post is inspired from SQL Programming Joes 2 Pros: Programming and Development for Microsoft SQL Server 2008 – SQL Exam Prep Series 70-433 – Volume 4.

[Amazon] | [Flipkart] | [Kindle] | [IndiaPlaza]

This is follow up blog post of my earlier blog post on the same subject - SQL SERVER – Introduction to SQL Error Actions – A Primer. In the article we discussed various basics terminology of the error handling. The article further covers following important concepts of error handling.

• Introduction to SQL Error Actions
• Statement Termination
• Scope Abortion
• Batch Termination

Above three are the most important concepts related to error handling and SQL Server.  There are many more things one has to learn but without beginners fundamentals one can’t learn the advanced concepts. Let us have small quiz and check how many of you get the fundamentals right.

Quiz

1.) Which SQL Server error action happens for errors with a severity of 11-16 when you set the XACT_ABORT setting to ON?

1. You will get Statement Termination.
2. You will get Scope Abortion.
3. You will get Batch Abortion.
4. You will get Connection Termination.
5. SQL Server will pick the error action.

2.) Which SQL Server error action happens for errors with a severity of 11-16 when you set the XACT_ABORT setting to OFF?

1. You will get Statement Termination
2. You will get Scope Abortion
3. You will get Batch Abortion
4. You will get Connection Termination
5. SQL Server will pick the error action

Now make sure that you write down all the answers on the piece of paper.

Watch following video and read earlier article over here. If you want to change the answer you still have chance.

Solution

1) 3

2) 5

Now compare let us check the answers and compare your answers to following answers. I am very confident you will get them correct.

Reference: Pinal Dave (http://blog.sqlauthority.com)

This blog post is inspired from SQL Architecture Basics Joes 2 Pros: Core Architecture concepts – SQL Exam Prep Series 70-433 – Volume 3.

[Amazon] | [Flipkart] | [Kindle] | [IndiaPlaza]

This is follow up blog post of my earlier blog post on the same subject - SQL SERVER – Introduction to Basics of a Query Hint – A Primer. In the article we discussed various basics terminology of the query hints. The article further covers following important concepts of query hints.

• Expecting Seek and getting a Scan
• Creating an index for improved optimization
• Implementing the query hint

Above three are the most important concepts related to query hint and SQL Server.  There are many more things one has to learn but without beginners fundamentals one can’t learn the advanced  concepts. Let us have small quiz and check how many of you get the fundamentals right.

Quiz

1) You have the following query:

DECLARE @UlaChoice TinyInt
SET @Type = 1
SELECT *
FROM LegalActivity
WHERE UlaChoice = @UlaChoice

You have a nonclustered index named IX_Legal_Ula on the UlaChoice field. The Primary key is on the ID field and called PK_Legal_ID 99% of the time the value of the @UlaChoice is set to ‘YP101′. What query will achieve the best optimization for this query?

1. SELECT *
   FROM LegalActivity
   WHERE UlaChoice = @UlaChoice
   WITH(INDEX(X_Legal_Ula))
2. SELECT *
   FROM LegalActivity
   WHERE UlaChoice = @UlaChoice
   WITH(INDEX(PK_Legal_ID))
3. SELECT *
   FROM LegalActivity
   WHERE UlaChoice = @UlaChoice
   OPTION (Optimize FOR(@UlaChoice = ‘YP101′))

2) You have the following query:

SELECT *
FROM CurrentProducts
WHERE ShortName = ‘Yoga Trip’

You have a nonclustered index on the ShortName field and the query runs an efficient index seek. You change your query to use a variable for ShortName and now you are using a slow index scan.

What query hint can you use to get the same execution time as before?

1. WITH
2. LOCK
3. FAST
4. OPTIMIZE FOR
5. MAXDOP
6. READONLY

Now make sure that you write down all the answers on the piece of paper.

Watch following video and read earlier article over here. If you want to change the answer you still have chance.

Solution

1) 3

2) 4

Now compare let us check the answers and compare your answers to following answers. I am very confident you will get them correct.

Reference: Pinal Dave (http://blog.sqlauthority.com)

This is follow up blog post of my earlier blog post on the same subject - SQL SERVER – Introduction to Hierarchical Query using a Recursive CTE – A Primer. In the article we discussed various basics terminology of the CTE. The article further covers following important concepts of common table expression.

• What is a Common Table Expression (CTE)
• Building a Recursive CTE
• Identify the Anchor and Recursive Query
• Add the Anchor and Recursive query to a CTE
• Add an expression to track hierarchical level
• Add a self-referencing INNER JOIN statement

Above six are the most important concepts related to CTE and SQL Server.  There are many more things one has to learn but without beginners fundamentals one can’t learn the advanced  concepts. Let us have small quiz and check how many of you get the fundamentals right.

Quiz

1) You have an employee table with the following data.

You need to write a recursive CTE that shows the EmpID, FirstName, LastName, MgrID, and employee level. The CEO should be listed at Level 1. All people who work for the CEO will be listed at Level 2. All of the people who work for those people will be listed at Level 3. Which CTE code will achieve this result?

1. WITH EmpList AS
   (SELECT Boss.EmpID, Boss.FName, Boss.LName, Boss.MgrID,
   1 AS Lvl
   FROM Employee AS Boss WHERE Boss.MgrID IS NULL
   UNION ALL
   SELECT E.EmpID, E.FirstName, E.LastName, E.MgrID, EmpList.Lvl + 1
   FROM Employee AS E INNER JOIN EmpList
   ON E.MgrID = EmpList.EmpID)
   SELECT * FROM EmpList
2. WITH EmpListAS
   (SELECT EmpID, FirstName, LastName, MgrID, 1 as Lvl
   FROM Employee WHERE MgrID IS NULL
   UNION ALL
   SELECT EmpID, FirstName, LastName, MgrID, 2 as Lvl )
   SELECT * FROM BossList
3. WITH EmpList AS
   (SELECT EmpID, FirstName, LastName, MgrID, 1 as Lvl
   FROM Employee WHERE MgrID is NOT NULL
   UNION
   SELECT EmpID, FirstName, LastName, MgrID, BossList.Lvl + 1
   FROM Employee INNER JOIN EmpList BossList
   ON Employee.MgrID = BossList.EmpID)
   SELECT * FROM EmpList

2) You have a table named Employee. The EmployeeID of each employee’s manager is in the ManagerID column. You need to write a recursive query that produces a list of employees and their manager. The query must also include the employee’s level in the hierarchy. You write the following code segment:

WITH EmployeeList (EmployeeID, FullName, ManagerName, Level)
AS (
–PICK ANSWER CODE HERE
)

1. SELECT EmployeeID, FullName, ” AS [ManagerID], 1 AS [Level]
   FROM Employee
   WHERE ManagerID IS NULL
   UNION ALL
   SELECT emp.EmployeeID, emp.FullName mgr.FullName, 1 + 1 AS [Level]
   FROM Employee emp JOIN Employee mgr
   ON emp.ManagerID = mgr.EmployeeId
2. SELECT EmployeeID, FullName, ” AS [ManagerID], 1 AS [Level]
   FROM Employee
   WHERE ManagerID IS NULL
   UNION ALL
   SELECT emp.EmployeeID, emp.FullName, mgr.FullName, mgr.Level + 1
   FROM EmployeeList mgr JOIN Employee emp
   ON emp.ManagerID = mgr.EmployeeId

Now make sure that you write down all the answers on the piece of paper.

Watch following video and read earlier article over here. If you want to change the answer you still have chance.

Solution

1) 1

2) 2

Now compare let us check the answers and compare your answers to following answers. I am very confident you will get them correct.

Reference: Pinal Dave (http://blog.sqlauthority.com)

This is follow up blog post of my earlier blog post on the same subject - SQL SERVER – Introduction to SQL Server Security – A Primer. In the article we discussed various basics terminology of the security. The article further covers following important concepts of security.

• Granting Permissions
• Denying Permissions
• Revoking Permissions

Above three are the most important concepts related to security and SQL Server.  There are many more things one has to learn but without beginners fundamentals one can’t learn the advanced  concepts. Let us have small quiz and check how many of you get the fundamentals right.

Quiz

1) If you granted Phil control to the server, but denied his ability to create databases, what would his effective permissions be?

1. Phil can do everything.
2. Phil can do nothing.
3. Phil can do everything except create databases.

2) If you granted Phil control to the server and revoked his ability to create databases, what would his effective permissions be?

1. Phil can do everything.
2. Phil can do nothing.
3. Phil can do everything except create databases.

3) You have a login named James who has Control Server permission. You want to elimintate his ability to create databases without affecting any other permissions. What SQL statement would you use?

1. ALTER LOGIN James DISABLE
2. DROP LOGIN James
3. DENY CREATE DATABASE To James
4. REVOKE CREATE DATABASE To James
5. GRANT CREATE DATABASE To James

Now make sure that you write down all the answers on the piece of paper.

Watch following video and read earlier article over here. If you want to change the answer you still have chance.

Solution

1) 3

2) 1

3) 3

Now compare let us check the answers and compare your answers to following answers. I am very confident you will get them correct.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Multidimensional Modeling (Adventure Works Tutorial)
This tutorial teaches you how to develop and deploy an Analysis Services project that enables the employees of Adventure Works Cycles to analyze various aspects of their business.

Tabular Modeling (Adventure Works Tutorial)
This tutorial teaches you how to create a SQL Server 2012 Analysis Services tabular model that enable sales and marketing teams to easily analyze internet sales data in the AdventureWorksDW2012 data warehouse. You will build the tabular model in SQL Server Data Tools.

Tutorials and Demos for Power View
Create Power View reports and explore Power View features. View demos, videos, and tutorials that help you get started quickly with Power View and successfully build reports with interactive filters and visualizations such as bubble charts, tiles, and cards.

Tutorial: Using the hierarchyid Data Type
This tutorial is intended for users who are experienced with Transact-SQL, but are new to the hierarchyid data type. In this tutorial, you convert an existing table to a hierarchical structure, and you also create a new table to store and manage hierarchical data efficiently.

Note: The description of the course is taken from original course description. You will need to install SQL Server 2012 AdventureWorks for all this tutorial.

Reference: Pinal Dave (http://blog.sqlauthority.com)

You can migrate it using any of the following methods.

1. Command-line tool (RSMigrationTool.exe)
2. GUI tool (RSMigrationUI.exe)

Either of the tool will generate a powershell script which will migrate the reports. This tool also generates the log file which provides the additional details regarding the migration.

Here is the link to Download the SQL Server Reporting Migration Tool.

Reference: Pinal Dave (http://blog.sqlauthority.com)

The question I received at http://twitter.com/pinaldave is as follows:

“How do I know if columnstore index is used by query through execution plan?“

Well, my reaction was simple – “From Operators“. See, I managed to answer this in two words only. However, I was not satisfied with my own answer. The questioner did not follow up after this question but I personally did not like my answer. I felt that if he was talking about execution plan, he is indeed aware of operators. If he was aware of operators, my answer was no use to him. Suddenly, I thought I was arrogant (or at least not clear). I sent him a direct message that I would write a blog post tomorrow to explain how to figure out if columnstore index is used or not via execution plan.

If you are interested in Columnstore Index, read following related posts on my blog  for additional details:

• SQL SERVER – Fundamentals of Columnstore Index
• SQL SERVER – How to Ignore Columnstore Index Usage in Query
• SQL SERVER – Updating Data in A Columnstore Index
• SQL SERVER – Video – Performance Improvement in Columnstore Index

To demonstrate this scenario, I have created the following script. I am using AdventureWorks (note: AdventureWorks Installation 60 Seconds Tutorial) for this sample database. Here are the steps which are to be followed:

• Create a sample table
• Insert some data
• Create clustered index on it
• Create nonclustered Columnstore Index on it
• Enable execution plan in SSMS
• Run two SELECT statement together with using clustered indexand columnstore index (use hint if needed)

Let us create environment and populate tables.

CREATE DATABASE CLAdventureWorks
GO
USE CLAdventureWorks
GO
-- Create New Table
CREATE TABLE [dbo].[MySalesOrderDetail](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[LineTotal] [numeric](38, 6) NOT NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
-- Create clustered index
CREATE CLUSTERED INDEX [CL_MySalesOrderDetail] ON [dbo].[MySalesOrderDetail]
( [SalesOrderDetailID])
GO
-- Create Sample Data Table
-- WARNING: This Query may run upto 2-10 minutes based on your systems resources
INSERT INTO [dbo].[MySalesOrderDetail]
SELECT S1.*
FROM AdventureWorks.Sales.SalesOrderDetail S1
GO 10
-- Create ColumnStore Index
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_MySalesOrderDetail_ColumnStore]
ON [MySalesOrderDetail]
(UnitPrice, OrderQty, ProductID)
GO

Now enable the actual execution plan in SSMS.

Run following two SELECT statement together.

-- Select Table with Clustered Index (Not Columnstore)
SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,
SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty
FROM [dbo].[MySalesOrderDetail] WITH(INDEX([CL_MySalesOrderDetail]))
GROUP BY ProductID
ORDER BY ProductID
GO
-- Select Table with Columnstore Index
SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,
SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty
FROM [dbo].[MySalesOrderDetail]
GROUP BY ProductID
ORDER BY ProductID
GO

Both will return the same result set. Click on Execution Plan Window.

You will see that there is different execution plan operator for regular clustered index and non-clustered columnstore index.

When you hover your mouse on both operators, they open operator tip which also clearly indicates if it is a regular clustered index and non-clustered columnstore index.

In case of the columnstore index, you will notice that the execution mode is also Batch instead of Row execution mode.

I guess now this completes the answer for the question asked to me on Twitter. Let us quickly clean up.

-- Cleanup
DROP INDEX [IX_MySalesOrderDetail_ColumnStore] ON [dbo].[MySalesOrderDetail]
GO
TRUNCATE TABLE dbo.MySalesOrderDetail
GO
DROP TABLE dbo.MySalesOrderDetail
GO

Feel free to ask me any question on social media – twitter or facebook.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Here is the question: Write a select statement using a single table, using single table single time only without using join keywords, which generate execution plan with 2 join operators. Use AdventureWorks as a sample database.

I got many interesting answers to the question I posted. I must say that I learned something new from all these answers. Before I discuss my own answer, let me show the answer provided by Alphonso Jones.

Here is his answer:

SELECT Row_number() OVER (ORDER BY OBJECT_ID) num, Rank() OVER (ORDER BY OBJECT_ID DESC) num2
INTO #tmp
FROM sys.columns
-- Enable Execution Plan with CTRL+M
SELECT num, SUM(num2) OVER (Partition BY num)
FROM #tmp

When I saw this answer – I was very happy because I did not visualize it as a solution when I was asking the question. Here is the execution plan of the T-SQL code above. It’s easy to see that there are multiple joins because of the Partition Function used in the query. What an excellent participation by Alphonso Jones.

Click to Enlarge

Here is the answer which I had visualized when I asked the question. I was running the query on AdventureWorks database and executed the following query, which in turn, generated an execution plan with multiple joins:

USE AdventureWorks2012
GO
SELECT *
FROM [Purchasing].[PurchaseOrderHeader]
WHERE [EmployeeID] = 258 AND [VendorID] = 1580
GO

Look at the execution plan of the above query. You can see the joins even though I am using single table and there is no join syntax in the query.

Click to Enlarge

Personally, I liked the solution of Alphonso Jones as his solution will always generate multiple joins due to Partition Function. On the other hand, my solution is a bit tricky for it requires Indexes on the table [Purchasing].[PurchaseOrderHeader], which generates index intersection. Index Intersection is a technique which utilizes more than one index on a table to satisfy a given query.

Thanks Alphonso Jones.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Server 2012 was available on April 1, 2012 for General Availability.

Recently I have received quite a few queries that they are facing issues with SQL Server 2012 installation. I have tried to solve quite a few problems and I figured out really there is no big problem but most of the problem are faced by people who are attempting to install it first time and have no previous experience about installing SQL Server. I decided to create a quick video with voice instruction regarding how to install SQL Server 2012. It is 3 minute long video and if you are installing SQL Server 2012, you can play this video on another machine and follow the steps.

I have attempted default installation of the SQL Server. However, there are few additional option one can consider as well – collation, default installation folder, and few other server related configurations.

Here are few resources related to SQL Server 2012 I have blogged earlier.

• SQL SERVER – 2012 – All Download Links in Single Page – SQL Server 2012
• SQLAuthority News – SQL Server 2012 – Microsoft Learning Training and Certification
• SQLAuthority News – Download Microsoft SQL Server 2012 RTM Now
• Installing AdventureWorks Sample Database – SQL in Sixty Seconds #010 – Video

Reference: Pinal Dave (http://blog.sqlauthority.com)

Balmukund Lakhani

Here is the guest post by Balmukund Lakhani

Recently Dhananjay (B | T) posted an interesting question on the SQLServerFAQ community page since the answer was new to almost all in the group, I have taken idea to write a blog post to explain this in detail. Here we go.

Question:
All objects can have minimum 1 and maximum of 128 characters in their names in SQL Server. Only exception is local temporary tables that can have maximum of 116 characters. What’s the technical reason behind this? Anyone, why that’s so?

Manas (B | T) posted this link from books online which tells this limitation but I always wanted to explore what happens under the hood.  Here is the simple repro of problem statement

DECLARE @i NVARCHAR(800)
SELECT @i = REPLICATE('A', 116)
SELECT @i = 'CREATE TABLE #'+@i+'(i int)'
PRINT @i
EXEC(@i)

In above script I am trying to create a temp table (starts with #) and you would see below output.

CREATE TABLE #AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA(i int)

Msg 193, Level 15, State 1, Line 1
The object or column name starting with ‘#AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’ is too long. The maximum length is 116 characters.

The URL which was pointed by Manas (B | T) clearly explained this
{
Both regular and delimited identifiers must contain from 1 through 128 characters. For local temporary tables, the identifier can have a maximum of 116 characters.
}

If you try to create a normal table with more than 128 characters then you would see

Msg 103, Level 15, State 4, Line 1
The identifier that starts with ‘#AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’ is too long. Maximum length is 128.

If you read the message closely, there are two error numbers 193 and 103 which have different length.  Now the real question is why there is differentiation between regular table and temp table Let’s create two temp tables now. I am giving name as A and A [repeated 128 times]

DECLARE @i NVARCHAR(800)
SELECT @i = REPLICATE('A', 115)
SELECT @i = 'CREATE TABLE #'+@i+'(i int)'
PRINT @i
EXEC(@i)
GO
DECLARE @i NVARCHAR(800)
SELECT @i = REPLICATE('B', 1)
SELECT @i = 'CREATE TABLE #'+@i+'(i int)'
PRINT @i
EXEC(@i)
GO

Here is the output which you would see. Now execute the same output in the SSMS.

CREATE TABLE #AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA(i INT)
CREATE TABLE #B(i INT)

If you run above piece of code in SSMS, you would see object getting created. Now, let’s find out the name of the object in tempdb database

SELECT LEN(name) 'Length', name
FROM tempdb.sys.objects
WHERE TYPE = 'u'
AND name LIKE '#AAAAAA%'
OR name LIKE '#B%'

Here is the output.

Click to expand

Length of both the object name is 128 even if we create object with length name as 116 and 1. That should give you little clue about the behavior.

Summary:
Whatever object name you give for temp table, SQL is going to pad the value with underscores and few “magic” bit as the end and would cover complete 128 characters. Have a closer look at length of magic bit “000000000058” and “000000000059” in above output [yeah, its 12]. Those are needed because you are allowed to create same name temp table by different sessions.  The length of magical number is 12 so 128 – 12 = 116 is the max length which is allowed for temp objects as SQL adds 12 chars at the end.

In case you are in Facebook, you may want to check the group SQLServerFAQ for other interesting questions.

Reference: Pinal Dave (http://blog.sqlauthority.com)