Feeds:
Posts
Comments

SQL SERVER – Weekly Series – Memory Lane – #026

April 27, 2013 by pinaldave

Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.

2007

SQL Server Interview Questions and Answers Complete List Download
SQL Server interview questions and answers is very crucial for any beginners. Some use this as a reference for future and some use it for refreshing the technology. Well, anyway, this is one of the most popular download on this blog.

@@DATEFIRST and SET DATEFIRST Relations and Usage
The master database’s syslanguages table has a DateFirst column that defines the first day of the week for a particular language. SQL Server with US English as default language, SQL Server sets DATEFIRST to 7 (Sunday) by default. We can reset any day as the first day of the week using DATEFIRST.

Understanding new Index Type of SQL Server 2005 Included Column Index along with Clustered Index and Non-clustered Index
In SQL Server 2005, the functionality of non-clustered indexes is extended by adding non-key columns to the leaf level of the non-clustered index. Non-key columns, can help to create cover indexes.By including non-key columns, you can create non-clustered indexes that cover more queries. The Database Engine does not consider non-key columns when calculating the number of index key columns or index key size.

Query to Find Seed Values, Increment Values and Current Identity Column value of the table
Script in the blog will return all the tables which has identity column. It will also return the Seed Values, Increment Values and Current Identity Column value of the table.

TRIM() Function – UDF TRIM()
A very interesting blog post how we can trim any column value in database.

Take Off Line or Detach Database
Using the alter database statement (SQL Server 2k and beyond) is the preferred method. The rollback after statement will force currently executing statements to rollback after N seconds.

Difference Between Unique Index vs Unique Constraint
There is no difference between Unique Index and Unique Constraint. Even though their syntax are different the effect is the same. Unique Constraint creates Unique Index to maintain the constraint to prevent duplicate keys.

SELECT vs. SET Performance Comparison
SET is the ANSI standard for variable assignment, SELECT is not. SET can only assign one variable at a time, SELECT can make multiple assignments at once – that gives SELECT slight speed advantage over SET.

Query to Retrieve the Nth Maximum Value
A very popular script on my blog. I am sure you have faced the similar situation in future.

2008

Better Performance – LEFT JOIN or NOT IN?
First of all answer this question : Which method of T-SQL is better for performance LEFT JOIN or NOT IN when writing a query? The answer is : It depends! It all depends on what kind of data is and what kind query it is etc. In that case just for fun guess one option LEFT JOIN or NOT IN.

Optimization Rules of Thumb – Best Practices
There are few rules for optimizing slow running query. Let us look at them one by one to see how it can help. I started with first six suggestions and later on I asked users to come up with the seventh suggestion. One of the readers actually came up with an entire blog post based on my earlier article. Read that here: Optimization Rules of Thumb – Best Practices – Reader’s Article

2009

Starting the SQL Journey – How Did I Get Started With SQL?
This is one of the most interesting blog post, I keep on reading it again and again. I started my career from Las Vegas and currently I am in Bangalore. My journey has been extremely long and it is almost 7 years old journey. I strongly suggest that everyone who is interested to know how I get here and what I have been doing, please read this blog post about me.

  

2010

Create Primary Key with Specific Name when Creating Table
Often primary keys are created with a default name and it is a good idea that we create primary keys with specific name so it helps readability and user can directly know lots of information if he/she is familiar with the naming convention.

Update Statistics are Sampled By Default
Question: Are the statistics sampled by default?
Answer: Yes. The sampling rate can be specified by the user and it can be anywhere between a very low value to 100%.

Attach mdf file without ldf file in Database
If you have only MDF file of the database it is absolutely possible to restore it without LDF file as well. Read this interesting story where I explain how we can do it.

2011

Prevent Constraint to Allow NULL
Here is a quick script which describes how to create a constraint which allows NULL.

Using Decode in SQL Server
There is no DECODE function in SQL Server, one has to use a CASE statement to simulate this one.

How to ALTER CONSTRAINT
Very simple straight to the script blog post.

2012

Working with FileTables in SQL Server 2012
This is a very interesting subject and I have written a three part blog series on this subject. I recommend everyone to read each of these three parts to understand the subject. Part 1 | Part 2 | Part 3

Do you want to learn SQL Server? Here are three excellent links about this subject where we have taken five different articles from Joes 2 Pros book series.

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

About these ads

Posted in Technology, SQL Server, SQL, SQL Query, SQL Tips and Tricks, SQL Authority, T SQL, PostADay, Memory Lane | Leave a Comment »

SQL SERVER – UNION ALL and UNION are Different Operation

April 26, 2013 by pinaldave

I have previously written about the difference between UNION ALL and UNION multiple times over this blog but it seems like this question never gets old and I keep on getting the question again and again.

Recently I wrote a blog post about how to Return Specific Row to at the Bottom of the Resultset – T-SQL Script where I demonstrated how to use the CASE statement in the ORDER BY clause, lots of people asked me if we can do this using UNION ALL clause. I followed up this blog post with Return Specific Row to at the Bottom of the Resultset – T-SQL Script – Part 2 where I demonstrated the same script with UNION ALL.

Now after the blog post, I got so many questions that why not use UNION instead of UNION ALL. Well the answer is simple; it would not work. The matter of the fact, UNION will result will return totally different result because when UNION returns results it removes the redundant data and sorts the data.

Let us see run following two queries and observe how we are getting different result when we use UNION and UNION ALL.

-- UNION ALL
USE AdventureWorks2012
GO
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE (ProductID BETWEEN 707 AND 716)
AND ProductID <> 715 AND ProductID <> 712
GROUP BY [ProductID]
UNION ALL
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 715
GROUP BY [ProductID]
UNION ALL
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 712
GROUP BY [ProductID]
GO
-- UNION
USE AdventureWorks2012
GO
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE (ProductID BETWEEN 707 AND 716)
AND ProductID <> 715 AND ProductID <> 712
GROUP BY [ProductID]
UNION
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 715
GROUP BY [ProductID]
UNION
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 712
GROUP BY [ProductID]
GO

You can see the result of the UNION and UNION ALL in following result. With the use of the UNION we will not get the same result. Honestly I will use my first method where I used the CASE statement in the ORDER BY clause.

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

Posted in PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology | Tagged , | Leave a Comment »

SQL SERVER – Return Specific Row to at the Bottom of the Resultset – T-SQL Script – Part 2

April 25, 2013 by pinaldave

“How do I return a  few of my resultset rows at the bottom of the entire resultset?”

I was previously asked this question and my response was that we can do this by using the CASE statement in the ORDER BY clause and I wrote a blog post describing the same over here SQL SERVER – Return Specific Row to at the Bottom of the Resultset – T-SQL Script. In the blog post I had mentioned that there is an alternative method of UNION ALL. There have been few emails and comments regarding how to use UNION ALL in this situation hence I decided to write this blog post demonstrating the same.

Let us see the scenario one more time. In this following diagram you can notice that there are two rows which are with ProductID 712 and 715. The entire resultset is ordered by column ProductID. Now our final requirement is that we want row 715 to be the second last row in the resultset and 712 as a very last row in the resultset. Looking from outside the entire thing looks very simple however, in reality it is not as simple as it looks.

First look at the image below and see if you can come up with the solution to this problem.

Here is the script on AdventureWorks database which I have written generates the result as we have displayed in the image below.

USE AdventureWorks2012
GO
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID BETWEEN 707 AND 716
GROUP BY [ProductID]
GO

Now instead of writing CASE statement in ORDER BY clause we will now write UNION ALL clause. In this case if you see there are two different values which we want at the bottom of the resultset.

USE AdventureWorks2012
GO
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE (ProductID BETWEEN 707 AND 716)
AND ProductID <> 715 AND ProductID <> 712
GROUP BY [ProductID]
UNION ALL
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 715
GROUP BY [ProductID]
UNION ALL
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 712
GROUP BY [ProductID]
GO

Here is the resultset which we expected. We have to use two different ORDER BY clause to get the desired result. In our case if we have more than 2 special cases like these we will need keep on adding more and more UINON clauses and that will make this script more confusing and not easy to read at all.

Now let us compare the performance of the two different queries one from earlier blog post and one from current blog post. Execute following two queries together and check their execution plan. In the execution plan can be enabled by using CTRL + M keyword.

-- Method 1 - CASE and ORDER BY
USE AdventureWorks2012
GO
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID BETWEEN 707 AND 716
GROUP BY [ProductID]
ORDER BY CASE WHEN [ProductID] = 715 THEN 1
WHEN [ProductID] = 712 THEN 2 ELSE 0 END
GO
-- Method 2 - UNION ALL
USE AdventureWorks2012
GO
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE (ProductID BETWEEN 707 AND 716)
AND ProductID <> 715 AND ProductID <> 712
GROUP BY [ProductID]
UNION ALL
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 715
GROUP BY [ProductID]
UNION ALL
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 712
GROUP BY [ProductID]
GO

You will clearly notice that the solution with CASE and ORDER BY is a much better scenario than using UNION ALL clause.

So far we have seen two examples 1) CASE and ORDER BY clause and 2) UNION ALL clause. If you know any other trick to get the similar result, please leave a comment and I will post this on my blog with due credit.

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

Posted in PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | Tagged | 3 Comments »

SQL SERVER – Interview Questions and Answers Sample Chapter Free Download – SQL in Sixty Seconds #050

April 24, 2013 by pinaldave

This journey of SQL in Sixty Seconds we started almost a year ago and today we are at very interesting milestone where I am recording 50th episode. Thought I wanted to keep the length of each video to sixty seconds, sometimes it went up by a few seconds. Due to this we are also at very interesting milestone as well – today’s 50th episode also accumulates the play time for entire playlist to 60 minutes (complete 1 hour).

There are two different milestones to celebrate today.

  1. This is the 50th Episode of SQL in Sixty Seconds
  2. Total play time for SQL in Sixty Seconds is One hour complete

This journey of SQL in Sixty Seconds we started almost a year ago and today we are at very interesting milestone where I am recording 50th episode. Thought I wanted to keep the length of each video to sixty seconds, sometimes it went up by a few seconds. Due to this we are also at very interesting milestone as well – today’s 50th episode also accumulates the play time for entire playlist to 60 minutes (complete 1 hour).

You can watch the entire SQL in Sixty Seconds series over here

On SQLAuthority.com one of the most viewed and popular articles are SQL Server Interview Questions and Answers. It has been consistently asked and referred again and again. Earlier I also had a book released on this subject which is also very popular in the industry. Here in today’s SQL in Sixty second I explain how you can download the sample chapters of the popular SQL Server Interview Questions and Answers book in PDF for FREE.

Let us see the same concept in following SQL in Sixty Seconds Video:

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

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

Posted in Database, Pinal Dave, PostADay, SQL, SQL Authority, SQL in Sixty Seconds, SQL Interview Questions and Answers, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology, Video | Tagged | 2 Comments »

SQL SERVER – Return Specific Row to at the Bottom of the Resultset – T-SQL Script

April 23, 2013 by pinaldave

“How do I return a  few of my resultset rows at the bottom of the entire resultset?”

This is a very simple question – let me explain this with following diagram. In this following diagram you can notice that there are two rows which are with ProductID 712 and 715. The entire resultset is ordered by column ProductID. Now our final requirement is that we want row 715 to be the second last row in the resultset and 712 as a very last row in the resultset. Looking from outside the entire thing looks very simple however, in reality it is not as simple as it looks.

First look at the image below and see if you can come up with the solution to this problem.

Here is the script on AdventureWorks database which I have written generates the result as we have displayed in the image below.

USE AdventureWorks2012
GO
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID BETWEEN 707 AND 716
GROUP BY [ProductID]
GO

Now what we want is that we want only two records which are 715 and 712 at the bottom of the resultset. There are two different ways we can achieve that, one with the method which I have demonstrated below where I write ORDER BY clause and include the CASE statement there and second method is where I use UNION clause. I prefer to use the method displayed below as it always works efficiently and consistantly.

USE AdventureWorks2012
GO
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID BETWEEN 707 AND 716
GROUP BY [ProductID]
ORDER BY CASE WHEN [ProductID] = 715 THEN 1
WHEN [ProductID] = 712 THEN 2 ELSE 0 END
GO

Here is the resultset which we expected. In the ORDER BY clause we have use CASE statement to achieve following result. We can add more case statement if we have more than one rows to treat differently.

Let me know what other solution which you would like to propose.

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

Posted in PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 18 Comments »

SQL SERVER – Discussion – Scale-up vs Scale-out Architectures

April 22, 2013 by pinaldave

Note: NuoDB is a complete re-think of relational databases with innovative support for the Cloud’s dynamic, asynchronous nature. Download NuoDB to experience the Scaling Out scenario discussed in this blog post.

Scaling Up

There are many different ways of scaling SQL Server to accommodate more transactions and throughput. The general scale-up approach includes:

  • Adding more CPU to increase computational performance
  • Adding RAM to increase query and data caching
  • Adding more storage such as SSDs and partitioning various I/O processes to different physical disks

As long as larger machines are available and your organization has the means to purchase them, then scaling up your database to increase performance is the best route. However, even if you have the means to scale up your infrastructure you will always be bound by the availability of bigger and faster hardware. Given this limitation on Moore’s Law and other organizational concerns such variable workloads and applications contending for the same dataset, additional approaches are required.

Common examples of variable workloads and applications are OLTP systems that need to be reported on by various departmental stakeholders. Other examples include financial applications that are aggregating financial transactions, marketing systems that measure segmentation performance, and other CRM/ERP systems. Developers and administrators have come up with some standard, generally accepted methods for supporting these demands on data. They include:

  • Replication techniques to enable various applications to connect to replica databases and eliminate computational and I/O constraints on the master
  • Clustering configurations to handle failover and availability concerns
  • Vertical and horizontal database partitioning to optimize query performance

Scaling Out

The introduction of SQL Azure and its ability to be deployed on to the cloud makes scaling out an option. Many instances of SQL Azure databases can be federated to support a single scaled out application. However, this approach is really no different than what developers have been doing with MySQL for many years. Federation is simply a managed method of sharding your database that comes at a cost.  Developers begin with database sharding and then that leads into application level sharding.  More sharding/federating means more complexity in your application architecture. Things databases are very good at such as query processing and managing constraints now become the developer’s responsibility.

Scale Everything

Databases have come a long way and what Microsoft is doing with SQL Azure is amazing. Their integrated development and deployment architecture gives them a clear advantage among .NET developers, administrators, and architects. However, a major shift in database technology is underway. New players are emerging that are changing how applications are built and how they are scaled. Some of the new entrants include NoSQL and NewSQL databases. NoSQL technologies might be outside the scope of this article mainly for their lack of SQL and ACID support, so let’s focus on a new database called NuoDB. Although the company might not agree that they are a NewSQL database, I will broadly characterize them as such for the sake of simplicity.

NuoDB is a database company in Cambridge, MA that I have been following for sometime. Unlike many NewSQL technologies on the market, they have decided to take a fresh approach to building a cloud database. They call it an emergent architecture, roughly modeled around how emergence manifests itself in nature. The example they use is a flock of birds and how birds flock and migrate from point A to B without any individual bird responsible for the flock as a whole. Let me break it down in more salient terms.

NuoDB is a distributed cloud database that supports SQL and ACID transactions. A single logical NuoDB database can be deployed on one or many cloud machines to support the scale-out model without the need to partition, shard, or replicate.

Brokers act as load balancers that connect clients to transaction engines. Transactions engines are responsible for executing SQL and dynamically cache data for read/write purposes. Transaction engines are a key component of NuoDB that enable it operate in a distributed environment without partitioning or sharding. These processes are able to asynchronously communicate with each other to fetch data that might be cached in memory, eliminating the need for sharding and replication. As updates are processed, transaction engines asynchronously broadcast changes to their peers to maintain consistency. Storage managers are responsible for serving up data to transaction engines and serializing data to disk for durability. A typical scale out architecture might look like this:

Each process in the NuoDB architecture represents a virtual or physical cloud resource but the database as a whole continues to represent itself as a single logical database to any client that’s connected to it.

This article was a brief introduction to one of many new database architectures that are making their way into mainstream applications. I like NuoDB because they are actively pursuing the Microsoft community with their public .NET Beta program. If you’re like me, and are interested in new approaches to solving database problems, I’d recommend you check out NuoDB and their .NET program.

Download NuoDB to experience the Scaling Out scenario discussed in this blog post.

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

Posted in PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology | Tagged | 2 Comments »

SQLAuthority News – Webinar SQL Server Locking and Concurrency by Rick Morelan

April 22, 2013 by pinaldave

My book co-author Rick Morelan is giving away SQL Queries Book and DVD set (valued at over $200) in his next webinar.

SQL Server is known for handing many requests at once and updating as new data comes in. If concurrency allows many things to happen at once, and locking prevents multiple users from changing the same piece of data at the same time… how do they work together?

Register for the webinar now to learn:

  • The different types of concurrency
  • How to identify blocking locks and choose between competing processes
  • Tools to profile the database, finding locks and wait times

Date and Time:
Tuesday April 23, 2013
7:00 AM PDT/10:00 AM EDT
11:00 AM PDT/2:00 PM EDT

Additionally DB Optimizer is an interesting tool and I personally use it for performance tuning experiments, do let me know if you are using it as well. I would like to know your feedback about the product.

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

Posted in PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority News, T SQL, Technology | 2 Comments »

« Newer Posts - Older Posts »