Feeds:
Posts
Comments

Archive for the ‘SQL Scripts’ Category

SQL SERVER – Understanding Table Hints with Examples

Posted in Pinal Dave, SQL, SQL Authority, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on November 19, 2009 | 4 Comments »

Introduction
Today we have a very interesting subject to look at. I tried to look for help online but have not found any other documentation besides what we have from the Book Online.
Let us try to understand what are the different kinds of hints available in SQL Server and how they are helpful.
What is a Hint?
Hints [...]

Read Full Post »

SQL SERVER – Size of Index Table – A Puzzle to Find Index Size for Each Index on Table

Posted in Pinal Dave, Readers Question, SQL, SQL Authority, SQL Index, SQL Puzzle, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQLServer, T SQL, Technology on November 18, 2009 | 7 Comments »

It is very easy to find out some basic details of any table using the following Stored Procedure.
USE AdventureWorks
GO
EXEC sp_spaceused [HumanResources.Shift]
GO
Above query will return following resultset

The above SP provides basic details such as rows, data size in table, and Index size of all the indexes on the table.
If we look at this carefully, a total [...]

Read Full Post »

SQL SERVER – Policy Based Management – Create, Evaluate and Fix Policies

Posted in Database, Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, Software Development, T SQL, Technology, tagged on November 13, 2009 | Leave a Comment »

Introduction
This article will cover the most spectacular feature of SQL 2008 – Policy-based management and how the configuration of SQL Server with policy-based management architecture can make a powerful difference. Policy based management is loaded with several advantages. It can help you implement various policies for reliable configuration of the system. It also provides additional [...]

Read Full Post »

SQL SERVER – Disable CHECK Constraint – Enable CHECK Constraint

Posted in Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Index, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on November 12, 2009 | 4 Comments »

Foreign Key and Check Constraints are two types of constraints that can be disabled or enabled when required. This type of operation is needed when bulk loading operations are required or when there is no need to validate the constraint. The T-SQL Script that does the same is very simple.
USE AdventureWorks
GO
– Disable the constraint
ALTER TABLE HumanResources.Employee
NOCHECK CONSTRAINT CK_Employee_BirthDate
GO
– Enable the constraint
ALTER TABLE HumanResources.Employee
WITH CHECK CHECK CONSTRAINT CK_Employee_BirthDate
GO
It [...]

Read Full Post »

SQL SERVER – Removing Key Lookup – Seek Predicate – Predicate – An Interesting Observation Related to Datatypes

Posted in Pinal Dave, SQL, SQL Authority, SQL Index, SQL Optimization, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on November 9, 2009 | 3 Comments »

Recently, I have been working on Query Optimization project. While working on it, I found the following interesting observation. This entire concept may appear very simple, but if you are working in the area of query optimization and server tuning, you will find such useful hints.
Before we start, let us understand the difference between Seek [...]

Read Full Post »

SQL SERVER – Stored Procedure are Compiled on First Run – SP taking Longer to Run First Time

Posted in Pinal Dave, SQL, SQL Authority, SQL Interview Questions and Answers, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology, tagged on November 8, 2009 | 5 Comments »

During the PASS summit, one of the attendees asked me the following question.
Why the Stored Procedure takes long time to run for first time?
The reason for the same is because Stored Procedures are compiled when it runs first time. When I answered the same, he replied that Stored Procedures are pre-compiled, and this should not [...]

Read Full Post »

SQL SERVER – How to Enable Index – How to Disable Index – Incorrect syntax near ‘ENABLE’

Posted in Pinal Dave, SQL, SQL Authority, SQL Index, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 27, 2009 | 6 Comments »

Many times I have seen that the index is disabled when there is large update operation on the table. Bulk insert of very large file updates in any table using SSIS is usually preceded by disabling the index and followed by enabling the index. I have seen many developers running the following query to disable [...]

Read Full Post »

SQL SERVER – Difference Between Candidate Keys and Primary Key

Posted in Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 22, 2009 | 15 Comments »

Introduction
Not long ago, I had an interesting and extended debate with one of my friends regarding which column should be primary key in a table. The debate instigated an in-depth discussion about candidate keys and primary keys. My present article revolves around the two types of keys.
Let us first try to grasp the definition of [...]

Read Full Post »

SQL SERVER – Change Collation of Database Column – T-SQL Script – Consolidating Collations – Extention Script

Posted in Readers Contribution, Readers Question, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology, tagged on October 19, 2009 | Leave a Comment »

This document is created by Brian Cidern, he has written this excellent extension to SQL Expert who SQL SERVER – Change Collation of Database Column – T-SQL Script. His scripts are not only extremely helpful to achieve the task of consolidating collations in quick script. His script not only works perfectly but excellent piece [...]

Read Full Post »

SQL SERVER – Recently Executed T-SQL Query

Posted in Pinal Dave, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology, tagged on October 16, 2009 | Leave a Comment »

About a year ago, I wrote blog post about SQL SERVER – 2005 – Last Ran Query – Recently Ran Query.  Since, then I have received many question regarding how this is better than fn_get_sql() or DBCC INPUTBUFFER.
The Short Answer in is both of them will be deprecated.
Please refer to following update query to [...]

Read Full Post »

Older Posts »