Feeds:
Posts
Comments

Archive for the ‘SQL Tips and Tricks’ Category

SQL SERVER – Disable All the Foreign Key Constraint in Database – Enable All the Foreign Key Constraint in Database

Posted in PostADay, SQL, SQL Authority, SQL Constraint and Keys, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on April 29, 2013 | 2 Comments »

Here is an email I received during the weekend.

“Hi Pinal,

I am a senior tester in the leading organization and we have two different environments 1) Testing 2) Production.

As a part of the testing we want to insert garbage data into the database system and see how the application behaves in this scenario. However, there is a small problem. Everytime when I try to insert garbage data in the database system the tables start giving me error that due to constraints on the table, I need to populate data in certain order and it has to be correct. Actually, we want to populate the bad data and see how application reacts to it. Upon talking to development team regarding this they suggested that we should skip this test as due to contraints there will be never bad data. Though, I understand their viewpoint, I must document how the application behaves when there are bad data and data integrity is compromised.

Is there any way I can disable all the Constraint temporarily and load the random data, test my system and later delete all the inserted data and enable the Constraint back?”

This is indeed a great question, I often come across this question again and again. Here is a quick script I have written in my early career which I still use it when I need to do something similar.

-- Disable all the constraint in database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- Enable all the constraint in database
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Remember above script when executed right away enable or disable constraints so be extremely careful to execute on production server.

There is one more thing, when you have disabled the constraint, you can delete the data from the table but if you attempt to truncate the table, it will still give you an error. If you need to truncate the table you will have to actually drop all the constraints. Do you use similar script in your environment? If yes, please leave a comment along with the script and I will post it on blog with due credit.

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

About these ads

Read Full Post »

SQLAuthority News – Download PowerPivot or PowerView enabled Workbook Optimizer – Download SQL Server Connector for Apache Hadoop

Posted in PostADay, SQL, SQL Authority, SQL Download, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority News, T SQL, Technology, tagged , on April 28, 2013 | 1 Comment »

Earlier this week Microsoft have released two very interesting downloads which got my attention. Haddop, PowerPivot and PowerView all three are not directly related to traditional RDBMS but their important is growing in the industry as big data is taking over market.

The Microsoft SQL Server SQOOP Connector for Hadoop is now part of Apache SQOOP 1.4 and we are not providing a separate download anymore. Please note that Microsoft’s HDInsight service includes the connector as well. Linux (for Hadoop setup) and Windows (with SQL Server 2008 R2 installed). Both are required to use the SQL Server-Hadoop Connector.

With SQL Server-Hadoop Connector, you import data from:

  • tables in SQL Server to delimited text files on HDFS
  • tables in SQL Server to SequenceFiles files on HDFS
  • tables in SQL Server to tables in Hive
  • result of queries executed on SQL Server to delimited text files on HDFS
  • result of queries executed on SQL Server to SequenceFiles files on HDFS
  • result of queries executed on SQL Server to tables in Hive

With SQL Server-Hadoop Connector, you can export data from:

  • delimited text files on HDFS to SQL Server
  • sequenceFiles on HDFS to SQL Server
  • hive Tables

Download instruction for SQL Server Connector for Apache Hadoop

The Workbook Size optimizer for Excel can better compress data inside workbooks that use PowerPivot or PowerView if this data comes from external data sources. The best size compression can be achieved for workbooks based on SQL Server databases and there are a few tricks we can do for other SQL datasources as well. The optimizer will install as an add in to excel and will provide you with a nice wizard to better compress the size of your workbook. Using the optimizer you can often get more than 1,000,000 rows datasets in a workbook under 10 MB, share it in SharePointOnline and interact with it using the Excel Web App in any browser.

Download PowerPivot or PowerView enabled Workbook Optimizer

(Courtesy Microsoft Downloads)

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

 

Read Full Post »

SQL SERVER – Weekly Series – Memory Lane – #026

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

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)

Read Full Post »

SQL SERVER – UNION ALL and UNION are Different Operation

Posted in PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology, tagged , on April 26, 2013 | Leave a Comment »

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)

Read Full Post »

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

Posted in PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology, tagged on April 25, 2013 | 3 Comments »

“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)

Read Full Post »

« Newer Posts - Older Posts »