SelectSql

What's the difference between a temp table and table variable in SQL Server?

2012-12-09T21:09:00.001-08:00

As a rule of thumb, for small to medium volumes of data and simple usage scenarios you should use table variables. (This is an overly broad guideline with of course lots of exceptions - see below and following articles.)

Some points to consider when choosing between them:

Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option.
Table variables can have indexes by using PRIMARY KEY or UNIQUE constraints. (If you want a non-unique index just include the primary key column as the last column in the unique constraint. If you don't have a unique column, you can use an identity column.)
Table variables don't participate in transactions, logging or locking. This means they're faster as they don't require the overhead, but conversely you don't get those features. So for instance if you want to ROLLBACK midway through a procedure then table variables populated during that transaction will still be populated!
Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not.
You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don't need to define your temp table structure upfront.
You can pass table variables back from functions, enabling you to encapsulate and reuse logic much easier (eg make a function to split a string into a table of values on some arbitrary delimiter).
Using Table Variables within user-defined functions enables those functions to be used more widely (see CREATE FUNCTION documentation for details). If you're writing a function you should use table variables over temp tables unless there's a compelling need otherwise.
Both table variables and temp tables are stored in tempdb. This means you should be aware of issues such as COLLATION problems if your database collation is different to your server collation; temp tables and table variables will by default inherit the collation of the server, causing problems if you want to compare data in them with data in your database.
Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.

Introduction to Locking in SQL Server

2012-11-09T03:00:00.001-08:00

Introduction

In this article, I want to tell you about SQL Server 7.0/2000 Transaction Isolation Levels, what kinds of Transaction Isolation Levels exist, and how you can set the appropriate Transaction Isolation Level, about Lock types and Locking optimizer hints, about deadlocks, and about how you can view locks by using the sp_lock stored procedure.

Transaction Isolation Levels

There are four isolation levels:

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

Microsoft SQL Server supports all of these Transaction Isolation Levels and can separateREPEATABLE READ and SERIALIZABLE.

Let me to describe each isolation level.

READ UNCOMMITTED

When it's used, SQL Server not issue shared locks while reading data. So, you can read an uncommitted transaction that might get rolled back later. This isolation level is also called dirty read. This is the lowest isolation level. It ensures only that a physically corrupt data will not be read.

READ COMMITTED

This is the default isolation level in SQL Server. When it's used, SQL Server will use shared locks while reading data. It ensures that a physically corrupt data will not be read and will never read data that another application has changed and not yet committed, but it not ensures that the data will not be changed before the end of the transaction.

REPEATABLE READ

When it's used, the dirty reads and nonrepeatable reads cannot occur. It means that locks will be placed on all data that is used in a query, and another transactions cannot update the data.

This is the definition of nonrepeatable read from SQL Server Books Online:

nonrepeatable read
When a transaction reads the same row more than one time, and between the
two (or more) reads, a separate transaction modifies that row. Because the
row was modified between reads within the same transaction, each read
produces different values, which introduces inconsistency.

SERIALIZABLE

Most restrictive isolation level. When it's used, the phantom values cannot occur. It prevents other users from updating or inserting rows into the data set until the transaction will be completed.

This is the definition of phantom from SQL Server Books Online:

phantom
Phantom behavior occurs when a transaction attempts to select a row that
does not exist and a second transaction inserts the row before the first
transaction finishes. If the row is inserted, the row appears as a phantom
to the first transaction, inconsistently appearing and disappearing.

You can set the appropriate isolation level for an entire SQL Server session by using theSET TRANSACTION ISOLATION LEVEL statement.
This is the syntax from SQL Server Books Online:

SET TRANSACTION ISOLATION LEVEL 
    {
        READ COMMITTED 
        | READ UNCOMMITTED 
        | REPEATABLE READ 
        | SERIALIZABLE
    }

You can use the DBCC USEROPTIONS statement to determine the Transaction Isolation Level currently set. This command returns the set options that are active for the current connection. This is the example:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
DBCC USEROPTIONS
GO

Lock types

There are three main types of locks that SQL Server 7.0/2000 uses:

Shared locks

Update locks

Exclusive locks

Shared locks are used for operations that do not change or update data, such as a SELECT statement.

Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes.

Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE.

Shared locks are compatible with other Shared locks or Update locks.

Update locks are compatible with Shared locks only.

Exclusive locks are not compatible with other lock types.

Let me to describe it on the real example. There are four processes, which attempt to lock the same page of the same table. These processes start one after another, so Process1 is the first process, Process2 is the second process and so on.

Process1 : SELECT
Process2 : SELECT
Process3 : UPDATE
Process4 : SELECT

Process1 sets the Shared lock on the page, because there are no another locks on this page.
Process2 sets the Shared lock on the page, because Shared locks are compatible with other Shared locks.
Process3 wants to modify data and wants to set Exclusive lock, but it cannot make it before Process1 and Process2 will be finished, because Exclusive lock is not compatible with other lock types. So, Process3 sets Update lock.
Process4 cannot set Shared lock on the page before Process3 will be finished. So, there is no Lock starvation. Lock starvation occurs when read transactions can monopolize a table or page, forcing a write transaction to wait indefinitely. So, Process4 waits before Process3 will be finished.
After Process1 and Process2 were finished, Process3 transfer Update lock into Exclusivelock to modify data. After Process3 was finished, Process4 sets the Shared lock on the page to select data.

Locking optimizer hints

SQL Server 7.0/2000 supports the following Locking optimizer hints:

NOLOCK

HOLDLOCK

UPDLOCK

TABLOCK

PAGLOCK

TABLOCKX

READCOMMITTED

READUNCOMMITTED

REPEATABLEREAD

SERIALIZABLE

READPAST

ROWLOCK

NOLOCK is also known as "dirty reads". This option directs SQL Server not to issue shared locks and not to honor exclusive locks. So, if this option is specified, it is possible to read an uncommitted transaction. This results in higher concurrency and in lower consistency.

HOLDLOCK directs SQL Server to hold a shared lock until completion of the transaction in which HOLDLOCK is used. You cannot use HOLDLOCK in a SELECT statement that includes the FOR BROWSE option. HOLDLOCK is equivalent to SERIALIZABLE.

UPDLOCK instructs SQL Server to use update locks instead of shared locks while reading a table and holds them until the end of the command or transaction.

TABLOCK takes a shared lock on the table that is held until the end of the command. If you also specify HOLDLOCK, the lock is held until the end of the transaction.

PAGLOCK is used by default. Directs SQL Server to use shared page locks.

TABLOCKX takes an exclusive lock on the table that is held until the end of the command or transaction.

READCOMMITTED
Perform a scan with the same locking semantics as a transaction running at the READ COMMITTED isolation level. By default, SQL Server operates at this isolation level.

READUNCOMMITTED
Equivalent to NOLOCK.

REPEATABLEREAD
Perform a scan with the same locking semantics as a transaction running at the REPEATABLE READ isolation level.

SERIALIZABLE
Perform a scan with the same locking semantics as a transaction running at the SERIALIZABLE isolation level. Equivalent to HOLDLOCK.

READPAST
Skip locked rows. This option causes a transaction to skip over rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

ROWLOCK
Use row-level locks rather than use the coarser-grained page- and table-level locks.

You can specify one of these locking options in a SELECT statement.
This is the example:

SELECT au_fname FROM pubs..authors (holdlock)

Deadlocks

Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other's object. For example, User1 has a lock on object "A" and wants a lock on object "B" and User2 has a lock on object "B" and wants a lock on object "A". In this case, SQL Server ends a deadlock by choosing the user, who will be a deadlock victim. After that, SQL Server rolls back the breaking user's transaction, sends message number 1205 to notify the user's application about breaking, and then allows the nonbreaking user's process to continue.

You can decide which connection will be the candidate for deadlock victim by using SET DEADLOCK_PRIORITY. In other case, SQL Server selects the deadlock victim by choosing the process that completes the circular chain of locks.

So, in a multiuser situation, your application should check the error 1205 to indicate that the transaction was rolled back, and if it's so, restart the transaction.

Note. To reduce the chance of a deadlock, you should minimize the size of transactions and transaction times.

View locks (sp_lock)

Sometimes you need a reference to information about locks. Microsoft recommends using the sp_lock system stored procedure to report locks information. This very useful procedure returns the information about SQL Server process ID, which lock the data, about locked database, about locked table ID, about locked page and about type of locking (locktype column).

This is the example of using the sp_lock system stored procedure:

spid   locktype                            table_id    page        dbname
------ ----------------------------------- ----------- ----------- ---------------
11     Sh_intent                           688005482   0           master
11     Ex_extent                           0           336         tempdb

The information, returned by sp_lock system stored procedure needs in some clarification, because it's difficult to understand database name, object name and index name by their ID numbers.

Check the link below if you need to get user name, host name, database name, index name object name and object owner instead of their ID numbers:

Retrieve Last Inserted Identity of Record

2012-11-04T22:37:00.001-08:00

SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()
SELECT IDENT_CURRENT(‘TableName’)

All of the abouve three will get the identity value but in different approches.

The variable @@IDENTITY will return the last generated identity value produced on a connection, without based on the table that produced the value. While @@IDENTITY is limited to the current session, it is not limited to the current scope. This means that if we insert some record in Table1 which has a trigger on the insert and the trigger inserts a record in some other table2 then the @@IDENTITY will return the identity value inserted in Table2.

SCOPE_IDENTITY() will return the last IDENTITY value produced on a connection and by a statement in the same scope, without based on the table that produced the value. So we can say that this function is some identical to @@IDENTITY with one exception. like @@IDENTITY will return the last identity value created in the current session, but it will also limit it to your current scope as well . So that means it will return identity value inserted in Table1.

Use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.
IDENT_CURRENT will reutrn returns the last IDENTITY value produced in a table, Without based on the connection that created the value, and Without based on the scope of the statement that produced the value. IDENT_CURRENT is not limited by scope and session., So it will retrieve the last generated table identity value.

CREATE TABLE Parent(id int IDENTITY);

CREATE TABLE Child(id int IDENTITY(100,1));

GO

CREATE TRIGGER Parentins ON Parent FOR INSERT

AS

BEGIN

   INSERT Child DEFAULT VALUES

END;

GO

--End of trigger definition

SELECT id FROM Parent;
--id is empty.

SELECT id FROM Child;
--ID is empty. 

--Do the following in Session 1
INSERT Parent DEFAULT VALUES;
SELECT @@IDENTITY;
/*Returns the value 100. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();
/* Returns the value 1. This was inserted by the
INSERT statement two statements before this query.*/ 

SELECT IDENT_CURRENT('Child');

/* Returns value inserted into Child, that is in the trigger.*/

SELECT IDENT_CURRENT('Parent');

/* Returns value inserted into Parent. 
This was the INSERT statement four statements before this query.*/ 

-- Do the following in Session 2.

SELECT @@IDENTITY;

/* Returns NULL because there has been no INSERT action
up to this point in this session.*/ 

SELECT SCOPE_IDENTITY();

/* Returns NULL because there has been no INSERT action
up to this point in this scope in this session.*/

SELECT IDENT_CURRENT('Child');

/* Returns the last value inserted into Child.*/

Getting started with SQL Server Reporting Services links

2012-10-17T22:07:00.002-07:00

Nice One for startup...

http://www.codeproject.com/Articles/13725/Getting-started-with-SQL-Server-Reporting-Services

http://www.katieandemil.com/ssrs-create-reports-a-simple-example-of-ssrs-report

Constraints sql server

2012-10-11T21:32:00.001-07:00

A Constraint is a property that we can assign to column in table. By assigning constraint property on column we can prevent the users to enter inconsistent of data in columns. We can assign these Constraint properties during the time of creation of table (By Using CREATE TABLE statement) or during the time of changing the existing table structure (By Using ALTER TABLE statement).

In SQL we have different types of constraints are available those are

1. Primary Key Constraint

2. Unique Key Constraint

3. Foreign Key Constraint

4. Not Null Constraint

5. Check Constraint

Now I will explain about each constraint clearly

Primary key constraint is used to uniquely identify each record in database table. It won’t allow repetition or duplication of data. Each table is having only one primary key constraint and it contains only unique values. Primary key constraint doesn’t accept null values.

Example of creating Primary Key constraint during the time of CREATE TABLE

Create Table SampleUserDetail

(

UserID integer PRIMARY KEY,

UserName varchar(50),

FirstName varchar(50),

LastName varchar(50)

)

Example of creating Primary Key constraint during the time of ALTER TABLE

ALTER TABLE SampleUserDetail ADD PRIMARY KEY (UserID)

To Drop Primary Key constraint on table use the below statement

ALTER TABLE SampleUserDetail DROP Constraint UserID

SQL Unique Key Constraint:

Unique key constraint is same as Primary key Constraint it doesn’t allow duplication or repetition of data in column and we can uniquely identify records in table. The main difference is Primary Key constraint won’t allow null values but unique key constraint allows null values. We have a chance to define only one primary key on table but we can define many unique key constraints on table.

Example of creating Unique Key constraint during the time of table creation

Create Table SampleUserDetail

(

UserID integer ,

UserName varchar(50),

FirstName varchar(50),

LastName varchar(50)

CONSTRAINT us_UserId UNIQUE (UserID)

)

Example of creating Unique Key constraint during the time of ALTER TABLE

ALTER TABLE SampleUserDetail ADD CONSTRAINT us_UserId UNIQUE (UserID)

To Drop Unique Key constraint on table use the below statement

ALTER TABLE SampleUserDetail DROP Constraint us_UserId

Example of Foreign key constraint

Create one table with primary key and give name as UserDetails

UserID	UserName	FirstName	LastName
1	SureshDasari	Suresh	Dasari
2	PrasanthiDonthi	Prasanthi	Donthi
3	MaheshDasari	Mahesh	Dasari

After create another table with Foreign Key and give name as SalaryDetails

SalID	Salary	UserID
1	10000	1
2	20000	2
3	30000	3

The column “UserID” is a primary key in UserDetails table

The column “SalID” is a foreign key in SalaryDetails tables

If you observe above two tables UserID in “UserDetails” table points to UserID in “SalaryDetails”

Example of creating Foreign Key constraint during the time of table creation

Create Table SalaryDetails

(

SalaryID integer ,

Salary integer,

UserID varchar(50),

PRIMARY KEY (SalaryID),

CONSTRAINT fk_SalaryID FOREIGN KEY(UserID)

REFERENCES UserDetails(UserID)

)

Example of creating Foreign Key constraint during the time of ALTER TABLE

ALTER TABLE SalaryDetails ADD CONSTRAINT fk_SalaryID FOREIGN KEY (UserID) REFERENCESUserDetails(UserID)

To Drop Foreign Key constraint on table use the below statement

ALTER TABLE SampleUserDetail DROP Constraint fk_SalaryID

SQL Not NULL Constraint:

If we set Not Null constraint property on any column in table that column won’t accept NULL or Empty values. If you want enforce any column not to accept NULL or empty value just set Not NULL Constraint property for that particular column

Example of creating NOT NULL constraint during the time of table creation

Create Table SampleUserDetail

(

UserID integer NOT NULL,

UserName varchar(50) NOT NULL,

FirstName varchar(50),

LastName varchar(50)

)

SQL Check Constraint:

The check constraint is used to limit the value range that can be placed in a column. If we set Check constraint property on particular column the values in particular column must satisfy condition set by check constraint.

Example of creating Check constraint during the time of CREATE TABLE

Create Table SampleUserDetail

(

UserID integer NOT NULL CHECK(UserID > 0),

UserName varchar(50),

FirstName varchar(50),

LastName varchar(50)

)

Example of creating Check constraint during the time of ALTER TABLE

ALTER TABLE SampleUserDetails ADD CONSTRAINT chk_UserID CHECK(UserID > 0)

To Drop Check constraint on table use the below statement

ALTER TABLE SampleUserDetails DROP CONSTRAINT chk_UserID

SQL Server Stored Procedure Coding Standard

2012-08-29T00:14:00.000-07:00

Must read before start writing stored procedure in SQL server 2005

Best practices

capitalize reserved words
main keywords on new line
can't get used to commas before columns
always use short meaningful table aliases
prefix views with v
prefix stored procs with sp (however don't use "sp_" which is reserved for built in procs)
don't prefix tables
table names singular
Use the brackets around objects, so the query engine excplicitly knows a field when it sees it

Use THE SAME CASE as table object names and field names
When calling SPs from application, use the fully qualified [dbo].[procName] with correct owner AND case. Not Kidding! Read the articles above!
Reference the owner of the object so security is explicitly known and doesn't have to be figured out
Use SET NOCOUNT ON and SET NOCOUNT OFF to eliminate the extra overhead to keep track of how many records are updated in the stored proc unless you need them. Normally, you don't and you can gain a huge increase in performance.

Preferences

Prefix stored procs with proc
Suffix every stored proc with SEL, UPD, DEL, INS (or SELECT, UPDATE, DELETE, INSERT)
Capitalize reserved words
Main keywords on new line (scripting)
Use commas before columns (scripting)
Prefix views with vw
Don't prefix tables
Table names singular
Add a suffix to the standard names like "_ByPK", "_OrderByLastName", or "_Top15Orders" for variations on the stock SP

Example

SELECT

      column1
    , column2
    , column3
    , COALESCE(column4,'foo') column4FROM
    tablenameWHERE
    column1 = 'bar'
ORDER BY 
      column1
    , column2

Write SET NOCOUNT ON in top of the procedure
Every SELECT statement should return with WITH NOLOCK key word.
Procedure should be rerunnable, Use IF EXISTS statement …
procedure always should have return value or return parameter
Use output parameter wherever necessary
Each code (for loop, If else etc) should be in BEGIN .. END block. For conditional checks( if else), please follow the same.
Use 'Derived tables' wherever possible, as they perform better. Consider the following query to find the second highest salary from the Employees table:

SELECT MIN(Amount) FROM Department WHERE DeptID IN

( SELECT TOP 2 DeptID FROM Department ORDER BY

Amount Desc)

The same query can be re-written using a derived table, as shown

below, and it performs twice as fast as the above query:

SELECT MIN(Amount) FROM

( SELECT TOP 2 Amount FROM Department ORDER BY

Amount DESC) AS D

Don’t

Do not include any business logics in your stored procedures, build a Business Logic Layer for that. Keep to what SQL was made for, inserting, deleting and selecting data.
Do not use reserved words, if you can’t avoid the use square brackets like: [event]
Do not use ‘SP_’ or ‘SYS_’ as a prefix in the stored procedure name.
Do proper Error handling in the procedure : use sp_addmessage to create user defined messages and raiserror for raising and passing the error code and description back to calling application.
Try to avoid wildcard characters at the beginning of a word while searching using the LIKE keyword, as that results in an index scan, which defeats the purpose of an index. The following statement results in an index scan, while the second statement results in an index seek:

SELECT Ename FROM Employee WHERE Ename LIKE '%han'

SELECT Ename FROM Employee WHERE Ename LIKE 'A%n'

Also avoid searching using not equals operators (<> and NOT) as they result in table and index scans.

Avoid the creation of temporary tables while processing data as much as possible, creating a temporary table means more disk I/O. Consider using advanced SQL, views, SQL Server 2000 table variable, or derived tables, instead of temporary tables.

14. Do not use SELECT * in your queries. Always write the required Column names

after the SELECT statement, like:

SELECT CustomerID, CustomerFirstName, City

This technique results in reduced disk I/O and better performance.

Do not wtite insert statement without defining the column name like below

INSERT INTO LMS_ERROR_LOG VALUES

( @pProspectId, @pMethodName, @pErrorMsg,

getdate() )

Always define the column name in the insert statement like below

INSERT INTO LMS_ERROR_LOG

( PROSPECT_ID,METHOD_NAME, EXCEPTION,DATE_TIME ) VALUES

@ProspectId,@pMethodName,@pErrorMsg,getdate() )

Do not write multiple declare statement inside the procedure like

Declare @Fname nvarchar(100)

Declare @Mname nvarchar(100)

Instead of that you can write like below

Declare

@Fname nvarchar(100),

@Mname nvarchar(100)

I normally follow below guidelines too for consitancy

Stored procedure name should be in CAPS latters for consistancy
Do Proper indenting ( 2 tabs for start and same for next levels)
Stored procedure name should follow some standrad to identify as below PROJECT_NAME_<SELECT OR INSERT OR UPDATE OR DELETE>_<OPERATION or TABLE_NAME>
Global parameter start with "p" and local parameter start with "v" . and for OUT parameter name should @pOut<ParameterName>
Created By/Date and purpose must be in the procedure as comment
All key word should be in the CAPITAL latters

Use Sysobjects in SQL Server to Find Useful Database Information

2012-06-05T22:12:00.001-07:00

Everything about your SQL Server database is stored in its system tables. I doubt that you spend a lot of time inspecting the system tables since you are too busy with your user tables. However, you may need to do something unusual once in a while, such as list all the triggers in your database. You could inspect your tables one by one, but that can become quite labor intensive if you have 500 tables.

This is where knowledge of the sysobjects table comes in handy. While it is not recommended that you update this table, you are certainly allowed to interrogate it.

In most cases, the two columns most useful to you will be sysobjects.name and sysobjects.xtype. The former lists the names of the objects in question, while the latter identifies the type of object, using the following codes:

C: Check constraint
D: Default constraint
F: Foreign Key constraint
L: Log
P: Stored procedure
PK: Primary Key constraint
RF: Replication Filter stored procedure
S: System table
TR: Trigger
U: User table
UQ: Unique constraint
V: View
X: Extended stored procedure

In the case of triggers, three other columns that identify the type of trigger are of interest: deltrig, instrig, and uptrig.

You can list all the objects of any type of interest using the following:

SELECT * FROM sysobjects WHERE xtype = <type of interest>

In the special case of triggers, which are owned by their parent table, you might want to interrogate the database using a self-join, like this:

SELECT 
      Sys2.[name] TableName, 
      Sys1.[name] TriggerName, 
      CASE 
            WHEN Sys1.deltrig > 0 THEN'Delete' 
            WHEN Sys1.instrig > 0 THEN'Insert' 
            WHEN Sys1.updtrig > 0 THEN'Update' 
      END'TriggerType' 
FROM 
      sysobjects Sys1 JOIN sysobjects Sys2 ON Sys1.parent_obj = Sys2.[id] 
WHERE Sys1.xtype='TR'
ORDERBY TableName

In SQL Server 2005, the preferred technique is to use the system views. This approach will insulate your queries from any changes that Microsoft might choose to make to the system tables.

Here is a simple example, using the INFORMATION_SCHEMA_TABLES view:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE 
FROMINFORMATION_SCHEMA.TABLES
ORDERBY TABLE_SCHEMA, TABLE_NAME

Run this query against the AdventureWorks database or any of your own databases to produce a quick list of the tables.

In order to illustrate the power of these schema queries, look at the following statement, which will list all functions and procedures within the selected database:

SELECT*FROMINFORMATION_SCHEMA.ROUTINES
ORDERBY ROUTINE_TYPE, ROUTINE_NAME

sys.procedure in sql server

2012-06-05T22:07:00.000-07:00

if you want to search for specific information about stored procedures in your database. Rather than look one by one through the code, you can query the SQL Server sys.objects and sys.procedures tables to find exactly what you are looking for.
To search for stored procedures that were created or modified on a specific date, you can directly search either the sys.objects and sys.procedures tables.

You can run exactly the same query against the sys.procedures table. Since this table is dedicated to storing detailed information about your database’s stored procedures you do not need to include a type filter with your query.

USE DatabaseName
GO 
SELECT modify_date,name,create_date
FROM sys.procedures
ORDER BY modify_date DESC
GO

As you can see, the sys.procedures table is quite useful, but you can use it to query more than just the name and create/modify dates.

For example, you can query the sys.prodedures table for stored procedures that contain specific text. This is really a handy function that can be useful if you are combing through an unfamiliar database or want to find very specific code quickly. You can even return the text of the procedure directly in your query so that you can quickly analyze each of the returned results quickly without having to find and open the procedure in SSMS.

Here is an example query that filters stored procedures to return only ones that contain the SQL CHARINDEX function:

USE DatabaseName
GO
SELECT modify_date,name,create_date
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%charindex%'
ORDER BY modify_date DESC
GO

INFORMATION_SCHEMA.COLUMNS

2012-05-28T22:49:00.002-07:00

The INFORMATION_SCHEMA.COLUMNS view allows you to get information about all columns for all tables and views within a database. By default it will show you this information for every single table and view that is in the database.

ExplanationThis view can be called from any of the databases in an instance of SQL Server and will return the results for the data within that particular database.

The columns that this view returns are as follows:

Column name	Data type	Description
TABLE_CATALOG	nvarchar(128)	Table qualifier.
TABLE_SCHEMA	nvarchar(128)	Name of schema that contains the table.
TABLE_NAME	nvarchar(128)	Table name.
COLUMN_NAME	nvarchar(128)	Column name.
ORDINAL_POSITION	int	Column identification number. Note: In SQL Server 2005, these column IDs are consecutive numbers.
COLUMN_DEFAULT	nvarchar(4000)	Default value of the column.
IS_NULLABLE	varchar(3)	Nullability of the column. If this column allows for NULL, this column returns YES. Otherwise, NO is returned.
DATA_TYPE	nvarchar(128)	System-supplied data type.
CHARACTER_MAXIMUM_LENGTH	int	Maximum length, in characters, for binary data, character data, or text and image data. -1 for xml and large-value type data. Otherwise, NULL is returned. For more information, see Data Types (Transact-SQL).
CHARACTER_OCTET_LENGTH	int	Maximum length, in bytes, for binary data, character data, or text and image data. -1 for xml and large-value type data. Otherwise, NULL is returned.
NUMERIC_PRECISION	tinyint	Precision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
NUMERIC_PRECISION_RADIX	smallint	Precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
NUMERIC_SCALE	int	Scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.
DATETIME_PRECISION	smallint	Subtype code for datetime and SQL-92 interval data types. For other data types, NULL is returned.
CHARACTER_SET_CATALOG	nvarchar(128)	Returns master. This indicates the database in which the character set is located, if the column is character data ortext data type. Otherwise, NULL is returned.
CHARACTER_SET_SCHEMA	nvarchar(128)	Always returns NULL.
CHARACTER_SET_NAME	nvarchar(128)	Returns the unique name for the character set if this column is character data or text data type. Otherwise, NULL is returned.
COLLATION_CATALOG	nvarchar(128)	Always returns NULL.
COLLATION_SCHEMA	nvarchar(128)	Always returns NULL.
COLLATION_NAME	nvarchar(128)	Returns the unique name for the collation if the column is character data or text data type. Otherwise, NULL is returned.
DOMAIN_CATALOG	nvarchar(128)	If the column is an alias data type, this column is the database name in which the user-defined data type was created. Otherwise, NULL is returned.
DOMAIN_SCHEMA	nvarchar(128)	If the column is a user-defined data type, this column returns the name of the schema of the user-defined data type. Otherwise, NULL is returned.
DOMAIN_NAME	nvarchar(128)	If the column is a user-defined data type, this column is the name of the user-defined data type. Otherwise, NULL is returned.

(Source: SQL Server 2005 Books Online)

Here is an example of data that was pulled from the AdventureWorks database. This data was pulled using this query:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

To be able to show the output the results were broken into multiple pieces.

To query for just one table you can use a query like this:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Address'

INFORMATION_SCHEMA.TABLES

2012-05-28T22:47:00.002-07:00

The INFORMATION_SCHEMA.TABLES view allows you to get information about all tables and views within a database. By default it will show you this information for every single table and view that is in the database.

ExplanationThis view can be called from any of the databases in an instance of SQL Server and will return the results for the data within that particular database.

The columns that this view returns are as follows:

Column name	Data type	Description
TABLE_CATALOG	nvarchar(128)	Table qualifier.
TABLE_SCHEMA	nvarchar(128)	Name of schema that contains the table.
TABLE_NAME	sysname	Table name.
TABLE_TYPE	varchar(10)	Type of table. Can be VIEW or BASE TABLE.

(Source: SQL Server 2005 Books Online)

Here is an example of data that was pulled from the AdventureWorks database. This data was pulled using this query:

SELECT * FROM INFORMATION_SCHEMA.TABLES

To only show a list of tables you would use this query:

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

To only show a list of only the view you would use this query:

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'

INFORMATION_SCHEMA VIEW

2012-05-28T22:08:00.001-07:00

The INFORMATION_SCHEMA views allow you to retrieve metadata about the objects within a database. These views can be found in the master database under Views / System Views and be called from any database in your SQL Server instance. The reason these were developed was so that they are standard across all database platforms. In SQL 2005 and SQL 2008 these Information Schema views comply with the ISO standard.

Following is a list of each of the views that exist.

INFORMATION_SCHEMA.CHECK_CONSTRAINTS
INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
INFORMATION_SCHEMA.COLUMN_PRIVILEGES
INFORMATION_SCHEMA.COLUMNS
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
INFORMATION_SCHEMA.DOMAINS
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
INFORMATION_SCHEMA.PARAMETERS
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
INFORMATION_SCHEMA.ROUTINE_COLUMNS
INFORMATION_SCHEMA.ROUTINES
INFORMATION_SCHEMA.SCHEMATA
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INFORMATION_SCHEMA.TABLE_PRIVILEGES
INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
INFORMATION_SCHEMA.VIEW_TABLE_USAGE
INFORMATION_SCHEMA.VIEWS

These views can be used from any of your databases. So if you want to gather data about Tables from the AdventureWorks database you would issue the following in that database.

USE AdventureWorks
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES

FOR MORE DETAILS GO...

http://www.mssqltips.com/sqlservertutorial/179/sql-server-informationschema-views/

Temporary table vs Table variable vs derived Table

2012-04-26T01:23:00.000-07:00

Four options available for temporary matrix storage: Derived Table and Temporary Table are the oldest, followed by Table Variable and the latest is CTE which can also be recursive.

Temp tables,
IO Operation - HIGH
Explicit Indexing is allowed
Constraints are allowed
Need not create or declare, we can use the INTO clause to create this object.
Reusable across the batch but with current session
Recommended when the data is huge & if there is any manipulation on the result
Supports concurrency users
Bit slower than table variables.

Table variables,
IO Operation – LOW or NA
Explicit Indexing not allowed – (Primary/Unique key can create index)
Constraints are allowed
Have to declare the variable
Only used by the current batch scope
Recommended when the data is less & if there is any manipulation on the result
Supports concurrency users
Faster when the number of records is less

Derived tables,
IO Operation – NA
Declaration – NA
Explicit Indexing – NA
Constraints - NA
Only on the current query(line)
If it is not reusable data then we can go for it
Recommended for inline quires
Supports concurrency users

What is Derived Tables in SQL

2012-04-26T00:08:00.000-07:00

The power of SQL Server never fails to amaze me; it literally seems that you can do just about anything in SQL. All you need is a little creativity and knowledge of the syntax, and you can put the power of SQL behind your web application. One of the neatest things I've yet done with SQL Server is using derived tables.

If you've used a VIEW before, you've used a more formal, more correct form of a derived table. For example, we could do the following:

 CREATE VIEW vwEmployeesFromNewYork AS
  SELECT * FROM Employee
  WHERE State = "NY"
 GO

Then if we wanted to see all of the Employees from New York with the last name Smith, ordered alphabetically, we could write:

 SELECT LastName, FirstName
 FROM vwEmployeesFromNewYork
 WHERE LastName = "Smith"
 ORDER BY FirstName

However, using derived tables, we could eliminate the view entirely. (Of course the view could be eliminated by simply adding an "AND State = "NY"" to the above WHERE clause, but what's important here is the concept, not the example!) Here is the same resultset as above but with the use of a derived table in place of a veiw:

 SELECT LastName, FirstName
 FROM
  (SELECT * FROM Employee
   WHERE State = "NY") AS EmployeeDerivedTable
 WHERE LastName = "Smith"
 ORDER BY FirstName

Isn't that neat? What we are doing is first getting the result set from our derived table (the SELECT statement in the FROM clause). Once we have that resultset, it is as though it was a table in itself. We then perform the SELECT on the derived table, returning our results!

Pivots with Dynamic Columns in SQL Server 2005

2012-04-20T04:14:00.003-07:00

Pivots in SQL Server 2005 can rotate a table, i.e. they can turn rows into columns. PIVOTs are frequently used in reports, and they are reasonably simple to work with. However, I've seen quite a few questions about this operator. Most questions were about the column list in the PIVOT statement. This list is fixed, but many times the new columns are determined by the report at a later stage. This problem is easily solved when we mix pivots with dynamic SQL, so here is a very simple example about how to dynamically generate the pivot statement:
PIVOT allows you to turn data rows into columns. For example, if you have a table like this (I use only three months here for simplicity):

CREATE TABLE Sales ([Month] VARCHAR(20) ,SaleAmount INT)

INSERT INTO Sales VALUES ('January', 100)
INSERT INTO Sales VALUES ('February', 200)
INSERT INTO Sales VALUES ('March', 300)

SELECT * FROM SALES
 

Month             SaleAmount
----------------  -----------
January           100
February          200
March             300

Suppose we wanted to convert the above into this:

 
January     February    March
----------- ----------  ----------
100         200         300

We can do this using the PIVOT operator, as follows:

SELECT  [January]
      , [February]
      , [March]
FROM    ( SELECT    [Month]
                  , SaleAmount
          FROM      Sales
        ) p PIVOT ( SUM(SaleAmount)
                    FOR [Month] 
                      IN ([January],[February],[March])
                  ) AS pvt

However, in the above example, I have the column names fixed as the first three months. If I want to create a result in which the columns are dynamic (for example, they are read from a table), then I need to construct the above query dynamically. To demonstrate this let’s look at the following example:
In the first table I have the column names I want to use:

CREATE TABLE Table1 (ColId INT,ColName VARCHAR(10))
INSERT INTO Table1 VALUES(1, 'Country')
INSERT INTO Table1 VALUES(2, 'Month')
INSERT INTO Table1 VALUES(3, 'Day')

In the second table I have the data. This consists of a row identifier (tID), a column ID (ColID) that refers to the column type in Table1, and a value:

CREATE TABLE Table2 (tID INT,ColID INT,Txt VARCHAR(10))

INSERT INTO Table2 VALUES (1,1, 'US')
INSERT INTO Table2 VALUES (1,2, 'July')
INSERT INTO Table2 VALUES (1,3, '4')
INSERT INTO Table2 VALUES (2,1, 'US')
INSERT INTO Table2 VALUES (2,2, 'Sep')
INSERT INTO Table2 VALUES (2,3, '11')
INSERT INTO Table2 VALUES (3,1, 'US')
INSERT INTO Table2 VALUES (3,2, 'Dec')
INSERT INTO Table2 VALUES (3,3, '25')

Now I would like to retrieve data from these two tables, in the following format:


tID         Country    Day        Month
----------- ---------- ---------- ----------
1           US         4          July
2           US         11         Sep
3           US         25         Dec

In other words I want to turn the data rows in Table2 into columns. If I had a fixed set of columns for the result, i.e. the columns Country, Day, and Month were fixed, I could use SQL Server 2005’s PIVOT operator in a query like:

SELECT  tID
      , [Country]
      , [Day]
      , [Month]
FROM    ( SELECT    t2.tID
                  , t1.ColName
                  , t2.Txt
          FROM      Table1 AS t1
                    JOIN Table2 
                       AS t2 ON t1.ColId = t2.ColID
        ) p PIVOT ( MAX([Txt])
                    FOR ColName IN ( [Country], [Day],
                                     [Month] ) ) AS pvt
ORDER BY tID ;

However I need to construct this query dynamically, because the column names Country, Day, and Month are specified in a table, and can be changed independently from my query. In our case these columns are given in Table1.
In the first step to generate the final pivot query I need to create the list of columns, in this case [Country], [Day], [Month].
Since there is no string concatenation aggregator in SQL (a concatenation aggregator would not be deterministic without some order restriction), and since the column names are stored in rows of a table, I need to flatten these columns into a single row or variable. There are various solutions to achieve this. One solution would be to use a query like:

DECLARE @cols NVARCHAR(2000)
SELECT  @cols = COALESCE(@cols + ',[' + colName + ']',
                         '[' + colName + ']')
FROM    Table1
ORDER BY colName

This query works both on SQL Server 2000 and 2005. It is efficient, but some may not like it because it uses the same variable (@cols) on both sides of an assignment. Another solution that works on SQL Server 2005 only is to use XML PATH.

DECLARE @cols NVARCHAR(2000)
SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + t2.ColName
                        FROM    Table1 AS t2
                        ORDER BY '],[' + t2.ColName
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'

This second query (I’ve seen this posted by Peter Larsson) has the advantage that it does not use the @cols variable on the right hand side. I like this solution more, since this can be extended as a general string concatenation aggregate in more complex queries.
Both of the above queries generate, from Table1, the string: ‘[Country],[Day], [Month]’. This column list is used twice in the pivot query that we aim to construct. Once it is use in the list of columns that we want to retrieve, and once it is used as the list of values that should become columns. Having constructed this list of columns above, we can just concatenate it with the missing parts of the pivot query like:

DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT tID, '+
@cols +'
FROM
(SELECT  t2.tID
      , t1.ColName
      , t2.Txt
FROM    Table1 AS t1
        JOIN Table2 AS t2 ON t1.ColId = t2.ColID) p
PIVOT
(
MAX([Txt])
FOR ColName IN
( '+
@cols +' )
) AS pvt
ORDER BY tID;'

Executing this with

EXECUTE(@query)

will give us the expected result: a table that is pivoted and shows columns that were specified in a table:

tID         Country    Day        Month
----------- ---------- ---------- ----------
1           US         4          July
2           US         11         Sep
3           US         25         Dec

What are master, msdb, tempdb databases in Sql Server?

2012-04-11T01:26:00.007-07:00

Microsoft® SQL Server 2000 systems have four system databases:

• master - The master database records all of the system level information for a SQL Server system. It records all login accounts and all system configuration settings. master is the database that records the existence of all other databases, including the location of the database files.
• tempdb - tempdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server. tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database.
By default, tempdb autogrows as needed while SQL Server is running. If the size defined for tempdb is small, part of your system processing load may be taken up with autogrowing tempdb to the size needed to support your workload each time to restart SQL Server. You can avoid this overhead by using ALTER DATABASE to increase the size of tempdb.
• model - The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.
• msdb - The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators.

what is collations in sql server

2012-04-09T01:48:00.001-07:00

What is collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

Case sensitivity

If A and a, B and b, etc. are treated in the same way then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is66 and b is 98.

Accent sensitivity

If a and á, o and ó are treated in the same way, then it is accent-insensitive. A computer treats a and á differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and áis 225. The ASCII value of ois 111 and ó is 243.

Kana Sensitivity

When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width sensitivity

When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

Database, Tables and columns with different collation

SQL Server 2000 allows the users to create databases, tables and columns in different collations.

Databases with different collation

use master
go
create database BIN collate Latin1_General_BIN
go
create database CI_AI_KS collate Latin1_General_CI_AI_KS
go
create database CS_AS_KS_WS collate Latin1_General_CS_AS_KS_WS
go

Tables and columns with different collation

Create table Mytable (
[colu] char(10) COLLATE Albanian_CI_AI_KS_WS NULL,
[Maydate] [char] (8) COLLATE Korean_Wansung_Unicode_CS_AS_KS NOT NULL ,
[Risk_Rating] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
)

Comparing characters on the databases with different collation

When we run the below code in CI_AI_KS and CS_AS_KS_WS the results will be completely different.

declare @Accentvar1 char(1)
declare @Accentvar2 char(1)
declare @Casevar1 char(1)
declare @Casevar2 char(1)
set @casevar1 ='A'
set @casevar2 ='a'
set @Accentvar1 ='a'
set @Accentvar2 ='á'

if @casevar1 = @casevar2
 begin
  print "A and a are treated same"
 end
 else
 begin
  print "A and a are not treated same"
 end

if @Accentvar1 = @Accentvar2 
 begin
  print "A and á are treated same"
 end
 else
 begin
  print "A and á are not  treated same"
 end

When we execute these statements on a CI_AI_KS database, the results are similar to those shown below.


 A and a are treated same
 A and á are treated same

When we execute these statements on a CS_AS_KS_WS database, the results are similar to those shown below.


 A and a are not treated same
 A and á are not treated same

Simulating case sensitivity in a case in-sensitive database

It is often necessary to simulate case sensitivity in a case insensitive database. The example below shows how you can achieve that.

Use CI_AI_KS
go
declare @var1 varchar(10)
declare @var2 varchar(10)
set @var1 ='A'
set @var2 ='a'
if ASCII(@var1) = ASCII(@var2)
print "A and a are treated same"
else
print "A and a are not same"

However, the function ASCII cannot be used for words. In order to achieve the same functionality of simulating case sensitiveness, we can use the varbinary data type.

Use CI_AI_KS
go
declare @var1 varchar(10)
declare @var2 varchar(10)
set @var1 ='Good'
set @var2 ='gooD'
if cast(@var1 as varbinary) = cast(@var2 as varbinary) 
print "Good and gooD are treated same"
else
print "Good and gooD are not treated same"


Simulating case insensitivity in a case sensitive database

It is often necessary to simulate case insensitivity in a case sensitive database. The example below displays how you can achieve that.
Use CS_AS_KS_WS
go
declare @var1 varchar(10)
declare @var2 varchar(10)
set @var1 ='A'
set @var2 ='a'
if upper(@var1) = upper(@var2)
print "A and a are treated same"
else
print "A and a are not same"
go
Use CS_AS_KS_WS
go
declare @var1 varchar(10)
declare @var2 varchar(10)
set @var1 ='Good'
set @var2 ='gooD'
if upper(@var1) = upper(@var2 )
print "Good and gooD are treated same"
else
print "Good and gooD are not treated same"


Simulating Accent sensitivity in a Accent insensitive database

It is often necessary to simulate case sensitivity in a case insensitive database:
Use CI_AI_KS
go
declare @var1 varchar(10)
declare @var2 varchar(10)
set @var1 ='A'
set @var2 = 'á'
if ASCII(@var1) = ASCII(@var2)
print "A and á are treated same"
else
print "A and á are not treated same"


Again, the function ASCII cannot be used for words. In order to achieve the same functionality of simulating case sensitiveness, we can use the varbinary data type:
Use CI_AI_KS
go
declare @var1 varchar(10)
declare @var2 varchar(10)
set @var1 ='Gold'
set @var2 ='Góld'
if cast(@var1 as varbinary) = cast(@var2 as varbinary) 
print " Gold  and Góld  are treated same"
else
print " Gold  and Góld  are not treated same"


Change the collation

In SQL Server 7.0, Collation can be setup only on the server level during installation. In order to change the collation, you have to rebuild the master. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.

In SQL Sever 2000, you can change collation on the database level and column level just by using alter statements.
Create database testNorwegian collate Danish_Norwegian_CI_AI_KS
go
Alter database testNorwegian collate Danish_Norwegian_CI_AI
go

Create table Mytable (
[colu] char(10) COLLATE Albanian_CI_AI_KS_WS NULL,
[Maydate] [char] (8) COLLATE Korean_Wansung_Unicode_CS_AS_KS NOT NULL ,
[Risk_Rating] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
)
go
Alter table Mytable alter column [colu] char(10) COLLATE Korean_Wansung_Unicode_CS_AS_KS NULL
go


Find the collation of the current server
select SERVERPROPERTY ('collation')

Find the collation of a particular database
select convert(sysname,DatabasePropertyEx('CS_AS_KS_WS','Collation'))

Find the collation of the current database
select convert(sysname,DatabasePropertyEx(db_name(),'Collation'))

Find collation of all columns in a table
select name, collation from syscolumns where [id]=object_id('Mytable')

Find all collation available in SQL Server
select * from ::fn_helpcollations()

Conclusion

This article explains the basic concept of collation and its uses. You can create functions for simulating accent insensitive in an accent sensitive database by mapping a for á. Kana sensitivity and Width sensitivity are focused mainly on Asian languages.

Improving SQL Performance

2012-04-04T00:05:00.000-07:00

Twelve Tips For Optimizing Sql Server 2005 Query Performance

1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old data
8. Partition your data correctly
9. Remove user-defined inline scalar functions
10. Use APPLY
11. Use computed columns
12. Use the correct transaction isolation level

To increase the speed of SQL SELECT query, you can analyze the following issues:

RequestLive property value

Available indexes for conditions from WHERE clause

Rewriting a query with OR conditions as a UNION

Available indexes for JOIN conditions

Available indexes for ORDER BY clause

Available indexes for GROUP BY clause

Select from in-memory tables

SELECT INTO vs INSERT SELECT
RequestLive property value
The first important option which can be used for a speed optimization is the TABSQuery.RequestLive property.
If selection query is simple and involves a single table only, try to set RequestLive value to True and to False before opening a query. Some queries run faster with theRequestLive=True, others will perform much better with the RequestLive=False.
Available indexes for conditions from WHERE clause

It is recommended to make sure that optimal indexes for conditions from WHERE clause are available.
See the topic "Speeding up Searches and Filters" for more details on how to check search conditions and create appropriate indexes.
For example if you would like to get a better performance for the query:

SELECT * FROM customer WHERE City='Kapaa Kauai' AND State='HI'

the best way to speed it up is to create the following case-sensitive index:

ABSTable1.AddIndex('idxCityState', 'City;State', []);

If you need to get a better performance for the query:

SELECT * FROM customer WHERE Upper(City)='KAPAA KAUAI'

the best way to speed it up is to create the following case-insensitive index:

ABSTable1.AddIndex('idxCity_nocase', 'City', [ixCaseInsensitive]);

Available indexes for JOIN conditions

To improve a JOIN query, please check that each field from JOIN conditions has an index.
For example if you would like to improve the performance of the query:

SELECT Event_Name,Venue FROM Events e JOIN Venues v ON (e.VenueNo = v.VenueNo)

you can create the following indexes:

VenuesTable.AddIndex('idxVenueNo', 'VenueNo', [ixPrimary]);

EventsTable.AddIndex('idxVenueNo', 'VenueNo', []);

Rewriting query with OR conditions as a UNION

Absolute DB cannot use indexes to improve performance of a query with OR conditions. You can speedup your
query

SELECT * FROM table WHERE (Field1 = 'Value1') OR (Field2 = 'Value2')

by creating indexes on each field in the above conditions and by using a UNION operator instead
of using OR:

SELECT ... WHERE Field1 = 'Value1'

UNION

SELECT ... WHERE Field2 = 'Value2'

Available indexes for ORDER BY clause

If you want to speed up a "live" SELECT from a single table with ORDER BY clause, you can create a compound index for ORDER BY fields.
For example if you would like to increase the speed of the query:

SELECT * FROM Employee ORDER BY LastName, FirstName

you can do it by creating the following compound index:

ABSTable1.AddIndex('idxLastNameFirstName', 'LastName;FirstName', []);

Available indexes for GROUP BY clauseTo get a better performance for SELECT from a single table with GROUP BY clause, you can create a compound index for GROUP BY fields.
For example if you want to speed up the query:

SELECT * FROM Employee GROUP BY FirstName

you can create the following index:

ABSTable1.AddIndex('idxFirstName', 'FirstName', []);

Select from in-memory tablesYour query perofrmance could be increased also if you will move all data from disk tables to in-memory tables and you will perform a query using in-memory copies of the disk tables (Set TABSQuery.InMemory property to True before query execution).
SELECT INTO vs INSERT SELECT
In some cases SELECT ... INTO some_table query runs faster than INSERT INTO some_table (SELECT ...), in another cases INSERT INTO is faster. Please note that the RequestLive property could have an impact on a performance of these queries.

Coalesce in Sql server example

2012-03-30T02:33:00.000-07:00

Introduction

COALESCE() accepts a series of values and a value to use in the event that all items in the list are null; then, it returns the first not-null value. This tip describes two creative uses of the COALESCE() function in SQL Server.

Here is a simple example: You have a table of persons whose columns include FirstName, MiddleName and LastName. The table contains these values:

John A. MacDonald
Franklin D. Roosevelt
Madonna
Cher
Mary Weilage

If you want to print their complete names as single strings, here's how to do it with COALESCE():

SELECT  FirstName + ' ' +COALESCE(MiddleName,'')+ ' ' +COALESCE(LastName,'')

If you don't want to write that for every query, Listing A shows how you can turn it into a function. Now whenever you need this script (regardless of what the columns are actually named) just call the function and pass the three columns. In the examples below, I'm passing literals, but you can substitute column names and achieve the same results:

SELECT dbo.WholeName('James',NULL,'Bond')
UNION
 SELECT dbo.WholeName('Cher',NULL,NULL)
UNION
 SELECT dbo.WholeName('John','F.','Kennedy')

Here is the result set:

Cher  
 James  Bond
 John F. Kennedy

You'll notice a hole in our thinking -- there are two spaces in James Bond's name. It's easy to fix this by changing the @result line to the following:

SELECT @Result = LTRIM(@first + ' ' + COALESCE(@middle,'') + ' ') + COALESCE(@last,'')

Here's another use of COALESCE(). In this example, I will produce a list of monies paid to employees. The problem is there are different payment arrangements for different employees (e.g., some employees are paid by the hour, by piece work, with a weekly salary, or by commission).

Listing B contains the code to create a sample table. Here are a few sample rows, one of each type:

1     18.0040    NULL  NULL  NULL  NULL
 2     NULL  NULL  4.00  400   NULL  NULL
 3     NULL  NULL  NULL  NULL  800.00      NULL
 4     NULL  NULL  NULL  NULL  500.00      600

Use the following code to list the amount paid to employees (regardless of how they are paid) in a single column:

SELECT 
      EmployeeID,
      COALESCE(HourlyWage * HoursPerWeek,0)+
      COALESCE(AmountPerPiece * PiecesThisWeek,0)+
      COALESCE(WeeklySalary + CommissionThisWeek,0)AS Payment
FROM [Coalesce_Demo].[PayDay]

Here is the result set:

EmployeeID  Payment
 1     720.00
 2     1600.00
 3     800.00
 4     1100.00

You might need that expression in several places in your application and, although it works, it isn't very graceful. This is how you can create a calculated column to do it:

ALTER TABLE Coalesce_Demo.PayDay
ADD Payment AS
      COALESCE(HourlyWage * HoursPerWeek,0)+
      COALESCE(AmountPerPiece * PiecesThisWeek,0)+
      COALESCE(WeeklySalary + CommissionThisWeek,0)

Now a simple SELECT * displays the pre-calculated results.

Summary

This tip demonstrates some unusual ways and places to apply the power of COALESCE(). In my experience, COALESCE() most often appears within a very specific content, such as in a query or view or stored procedure.

You can generalize the use of COALESCE() by placing it in a function. You can also optimize its performance and make its results constantly available by placing it in a calculated column.

Alternatives To SQL Server Cursors

2012-03-30T02:17:00.000-07:00

Introduction

Anyone with more than a little exposure to SQL Server will soon come to the conclusion that the use of cursors is best avoided. SQL Server is a Relational Database Management System, and as such is very proficient at processing sets of data. The use of a cursor effectively repeats an operation once for each row being processed, thereby consuming far more server resources than would be necessitated by a set-based operation. In addition, since SQL cursors are processed much more slowly than set-based operations, any locks generated by the use of said cursor are held longer than would otherwise be necessary. In this way, the use of a cursor can affect not only the performance of a given batch of work, but also the performance of any other batches which need to make use of the same data resources.

In order to effectively avoid using a SQL cursor, one must know why a cursor is being used in the first place. The list below details the most common reasons why people use cursors, and some possible solutions for each problem.

Problem: The Developer or Designer is new to SQL Server
Problem: Need to update a table with incrementing column values for records already in the table
Problem: Need to use a custom identity function to perform an insert
Problem: Need to compare a row to adjacent rows in a table

Problem: The Developer or Designer is New to SQL Server

By far, this is the most common reason for cursor usage. If a programmer is already well versed in an event-driven programming language such as Visual Basic, they will possess a great wealth of experience in dealing with data items one at a time. As such, their natural inclination when moving into SQL is to do the same. Unfortunately, what is appropriate in one language may be (and in this case is) entirely inappropriate in another.

Resolution

The primary weapon against cursor usage in this scenario is to develop a strong understanding of the basics of Transact-SQL. There are countless courses, books, and websites dedicated to the topic. In addition, you can review the examples below, which present set-based solutions to problems which may otherwise necessitate a cursor.
If you’re still struggling with finding a set-based solution, post a question in the MSDN Transact-SQL Forum

Problem: Need to update a table with incrementing column values for records already in the table

This problem is most often encountered when importing data from an external data source. The external data source may not have a key field, for instance, and for one of various reasons, an identity column cannot be used while importing.

Resolution

A number of methods may be employed to resolve this issue. The most efficient and straightforward method, available in SQL 2005 and greater, is to use theROW_NUMBER() function.

Using the AdventureWorks database:

SELECT LoginID, ROW_NUMBER() OVER (ORDER BY LoginID ASC) AS RowKey
FROM  HumanResources.EmployeeLogins

WITH RowKeys (LoginID, RowKey) AS 
  (SELECT LoginID, 
    ROW_NUMBER() OVER (ORDER BY LoginID ASC) AS RowKey
  FROM HumanResources.EmployeeLogins)
UPDATE HumanResources.EmployeeLogins
SET  RowKey = rk.RowKey
FROM  HumanResources.EmployeeLogins el
JOIN  RowKeys rk ON el.LoginID = rk.LoginID

ROW_NUMBER() can also be used to number rows based upon logical groupings, or partitions in data, although this is less applicable to cursor replacement. For instance, the test data contains logins from multiple domains, and you may want to number the rows in the table based upon their position within a grouping of rows from the same domain.

--Use ROW_NUMBER in conjunction with PARTITION to create row numbers grouped by domain
SELECT LoginID, 
  ROW_NUMBER() OVER 
   (PARTITION BY LEFT(LoginID, CHARINDEX('\', LoginID) - 1) 
   ORDER BY LoginID ASC) AS RowKey
FROM  HumanResources.EmployeeLogins

Also worth noting is that the sort order of the OVER clause does not have to match the sort order of the outermost query.
In the event that the numbering has to start at a number other than zero, that number can be added to ROW_NUMBER() in order to obtain the desired row numbers.
For instance:

SELECT LoginID, 
  100 + ROW_NUMBER() OVER 
   (PARTITION BY LEFT(LoginID, CHARINDEX('\', LoginID) - 1) 
   ORDER BY LoginID ASC) AS RowKey
FROM  HumanResources.EmployeeLogins

The same function can be performed, albeit far less efficiently, in SQL 2000. The following solution uses an inline subquery in order to COUNT the number of rows on or before a given LoginID:

SELECT el1.LoginID,
  (SELECT COUNT(LoginID)
  FROM HumanResources.EmployeeLogins el2
  WHERE el2.LoginID <= el1.LoginID) AS RowKey
FROM HumanResources.EmployeeLogins el1

This solution is subject to error if duplicate values are present in the columns being used for comparison. If there were two identical LoginIDs, they would both be granted the same RowKey. When dealing with duplicate fields such as this, a local temporary table can be used in order to ensure unique row numbering:

CREATE TABLE #EmployeeLoginsTemp
 (RowKey int IDENTITY(1,1) NOT NULL,
 LoginID nvarchar(256))
 
INSERT #EmployeeLoginsTemp (LoginID)
SELECT LoginID FROM HumanResources.EmployeeLogins
 
SELECT * FROM #EmployeeLoginsTemp

Problem: Need to use a custom identity function to perform an insert

Many databases employ custom identity generation functions. There are VERY FEW good reasons for using custom identity functions in SQL Server, given the presence of IDENTITY columns and the UniqueIdentifier data type. That is not to say that you will never encounter such a situation - just that all possible alternatives should be vetted prior to deciding upon a custom identity solution.
A custom identity function commonly resembles the following:

CREATE TABLE Identities (TableName sysname PRIMARY KEY CLUSTERED,
      NextKey int NOT NULL)
 
CREATE PROC GetNextKey( @TableName sysname,
      @NextKey int OUTPUT)
AS
BEGIN
 --Fetch the NextID, if there is a row for the passed-in TableName in the Identities.
 SELECT @NextKey = NextKey
 FROM Identities
 WHERE TableName = @TableName
 
 --If there wasn't a row, @NextID will be NULL.
 IF @NextKey IS NULL
 BEGIN
  INSERT Identities (TableName, NextKey)
  SELECT @TableName, 1
 
  SELECT @NextKey = NextKey
  FROM Identities
  WHERE TableName = @TableName
 END
 
 --Advance the NextID value in Identities
 UPDATE Identities
 SET  NextKey = NextKey + 1
 WHERE TableName = @TableName
END

Resolution

The biggest problem with custom identity functions is that they serialize inserts. While the following solution (for SQL 2000 and greater) does not use a cursor, it does use a WHILE loop to call the GetNextKey proc once for each row in HumanResources.EmployeeLogins:

WHILE EXISTS (SELECT 1 FROM HumanResources.EmployeeLogins WHERE RowKey IS NULL)
BEGIN
 DECLARE @NextKey int
 EXEC dbo.GetNextKey 'HumanResources.EmployeeLogins', @NextKey OUTPUT
 
 UPDATE HumanResources.EmployeeLogins
 SET  RowKey = @NextKey
 WHERE LoginID = (SELECT TOP 1 LoginID FROM HumanResources.EmployeeLogins WHERE RowKey IS NULL)
END

Problem: Need to compare a row to adjacent rows in a table

A typical example of this type of problem is “I need to know by how much the latest price increase changed the price of each item”. The programmer often resorts to a cursor because they cannot understand how to compare each row with the previous row for the same item, where the previous row’s price was not equal to the current row’s price.

Resolution

In this situation, an inline query can be very useful. The following solution works for SQL 2000 and higher, and uses a different AdventureWorks table for illustration. In AdventureWorks, the Production.ProductListPriceHistory table stores pricing history for each item. The current row is identified by a NULL EndDate, so we must compare the current row to the most recent row for the same item.

SELECT plph1.ProductID,  plph1.ListPrice - 
  COALESCE((SELECT  TOP 1 ListPrice
    FROM  Production.ProductListPriceHistory plph2
    WHERE  EndDate IS NOT NULL
    AND   plph2.ProductID = plph1.ProductID
    ORDER BY EndDate DESC), 
    plph1.ListPrice) AS LastPriceChangeAmount
FROM Production.ProductListPriceHistory plph1
WHERE plph1.EndDate IS NULL

SQL Server Transactions and Error Handling

2012-03-26T21:40:00.000-07:00

Transactions

Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.

Users can group two or more Transact-SQL statements into a single transaction using the following statements:

Begin Transaction
Rollback Transaction
Commit Transaction

If anything goes wrong with any of the grouped statements, all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology. If everything is in order with all statements within a single transaction, all changes are recorded together in the database. In SQL Server terminology, we say that these changes are committed to the database.

Here is an example of a transaction :

USE pubs

DECLARE @intErrorCode INT

BEGIN TRAN
    UPDATE Authors
    SET Phone = '415 354-9866'
    WHERE au_id = '724-80-9391'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM

    UPDATE Publishers
    SET city = 'Calcutta', country = 'India'
    WHERE pub_id = '9999'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
    ROLLBACK TRAN
END

Before the real processing starts, the BEGIN TRAN statement notifies SQL Server to treat all of the following actions as a single transaction. It is followed by two UPDATE statements. If no errors occur during the updates, all changes are committed to the database when SQL Server processes the COMMIT TRAN statement, and finally the stored procedure finishes. If an error occurs during the updates, it is detected by if statements and execution is continued from the PROBLEM label. After displaying a message to the user, SQL Server rolls back any changes that occurred during processing. Note: Be sure to match BEGIN TRAN with either COMMIT or ROLLBACK.

Nested Transactions

SQL Server allows you to nest transactions. Basically, this feature means that a new transaction can start even though the previous one is not complete. Transact-SQL allows you to nest transaction operations by issuing nested BEGIN TRAN commands. The @@TRANCOUNT automatic variable can be queried to determine the level of nesting - 0 indicates no nesting , 1 indicates nesting one level deep, and so fourth.
A COMMIT issued against any transaction except the outermost one doesn't commit any changes to disk - it merely decrements the@@TRANCOUNT automatic variable. A ROLLBACK, on the other hand, works regardless of the level at which it is issued, but rolls back all transactions, regardless of the nesting level. Though this is counterintuitive, there's a very good reason for it. If a nested COMMIT actually wrote changes permanently to disk, an outer ROLLBACK wouldn't be able to reverse those changes since they would already be recorded permanently.
When you explicitly begin a transaction, the @@TRANCOUNT automatic variable count increases from 0 to 1; when you COMMIT, the count decreases by one; when you ROLLBACK, the count is reduced to 0. As you see, the behavior of COMMIT and ROLLBACK is not symmetric. If you nest transactions, COMMIT always decreases the nesting level by 1, as you can see illustrated in Figure 1. The ROLLBACK command, on the other hand, rolls back the entire transaction, illustrated in Figure 2. This asymmetry between COMMIT and ROLLBACK is the key to handling errors in nested transactions.

Figure 1: A COMMIT always balances a BEGIN TRANSACTION by reducing the transaction count by one.

Figure 2: A single ROLLBACK always rolls back the entire transaction.

As you can see from Figure 1 and Figure 2, you can nest transactions and use the @@TRANCOUNT automatic variable to detect the level. You also learned that COMMIT and ROLLBACK do not behave symmetrically;COMMIT just decreases the value of @@TRANCOUNT, while ROLLBACK resets it to 0. The implication is that a transaction is never fully committed until the last COMMIT is issued. No matter how deeply you nest a set of transactions, only the last COMMIT has any effect.
Here is an example of a nested transaction :

USE pubs
SELECT 'Before BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 0
BEGIN TRAN
    SELECT 'After BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 1
    DELETE sales
    BEGIN TRAN nested
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
                   -- The value of @@TRANCOUNT is 2
        DELETE titleauthor
    COMMIT TRAN nested
                   -- Does nothing except decrement the value of @@TRANCOUNT

    SELECT 'After COMMIT TRAN nested', @@TRANCOUNT
                   -- The value of @@TRANCOUNT is 1
ROLLBACK TRAN

SELECT 'After ROLLBACK TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 0 
-- because ROLLBACK TRAN always rolls back all transactions and sets 
-- @@TRANCOUNT to 0.

SELECT TOP 5 au_id FROM titleauthor

In this example we see that despite the nested COMMIT TRAN, the outer ROLLBACK still reverses the effects of the DELETE titleauthor command.
Here is another similar example of nested transaction :

USE pubs
SELECT 'Before BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 0
BEGIN TRAN
    SELECT 'After BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 1
    DELETE sales
    BEGIN TRAN nested
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
               -- The value of @@TRANCOUNT is 2
        DELETE titleauthor
    ROLLBACK TRAN
  
    SELECT 'After COMMIT TRAN nested', @@TRANCOUNT
    -- The value of @@TRANCOUNT is 0 because 
    -- ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT 
    -- to 0.

IF (@@TRANCOUNT > 0) BEGIN
    COMMIT TRAN -- Never makes it here cause of the ROLLBACK
    SELECT 'After COMMIT TRAN', @@TRANCOUNT
END

SELECT TOP 5 au_id FROM titleauthor

In this example, execution never reaches the out COMMIT TRAN because the ROLLBACK TRAN reverses all transactions currently in progress and sets @@TRANCOUNT to 0. Unless ROLLBACK TRAN is called with a save point, ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT to 0, regardless of the context in which it's called.

SAVE TRAN and Save Points

Savepoints offer a mechanism to roll back portions of transactions. A user can set a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled. SQL Server allows you to use savepoints via the SAVE TRAN statement, which doesn't affect the @@TRANCOUNT value. A rollback to a savepoint (not a transaction) doesn't affect the value returned by @@TRANCOUNT, either. However, the rollback must explicitly name the savepoint: using ROLLBACK TRANwithout a specific name will always roll back the entire transaction.
The following script demonstrates how savepoints can be used :

USE pubs
SELECT 'Before BEGIN TRAN main', @@TRANCOUNT
   -- The value of @@TRANCOUNT is 0

BEGIN TRAN main
    SELECT 'After BEGIN TRAN main', @@TRANCOUNT
   -- The value of @@TRANCOUNT is 1
    DELETE sales
    SAVE TRAN sales  -- Mark a save point
    SELECT 'After SAVE TRAN sales', @@TRANCOUNT
   -- The value of @@TRANCOUNT is still 1

    BEGIN TRAN nested
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
        -- The value of @@TRANCOUNT is 2
        DELETE titleauthor
        SAVE TRAN titleauthor  -- Mark a save point
        SELECT 'After SAVE TRAN titleauthor', @@TRANCOUNT
        -- The value of @@TRANCOUNT is still 2
    ROLLBACK TRAN sales

    SELECT 'After ROLLBACK TRAN sales', @@TRANCOUNT
     -- The value of @@TRANCOUNT is still 2

    SELECT TOP 5 au_id FROM titleauthor

IF (@@TRANCOUNT > 0) BEGIN
    ROLLBACK TRAN
    SELECT 'AFTER ROLLBACK TRAN', @@TRANCOUNT
    -- The value of @@TRANCOUNT is 0 because 
    -- ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT 
    -- to 0.
END
    
SELECT TOP 5 au_id FROM titleauthor

Error Handling

The examples presented here are specific to stored procedures as they are the desired method of interacting with a database. When an error is encountered within a stored procedure, the best you can do is halt the sequential processing of the code and either branch to another code segment in the procedure or return processing to the calling application. The @@ERROR automatic variable is used to implement error handling code. It contains the error ID produced by the last SQL statement executed during a client’s connection. When a statement executes successfully, @@ERROR contains 0. To determine if a statement executes successfully, an IF statement is used to check the value of @@ERROR immediately after the target statement executes. It is imperative that @@ERROR be checked immediately after the target statement, because its value is reset to 0 when the next statement executes successfully. If a trappable error occurs, @@ERROR will have a value greater than 0. SQL Server resets the @@ERROR value after every successful command, so you must immediately capture the @@ERROR value. Most of the time, you'll want to test for changes in @@ERROR right after anyINSERT, UPDATE, or DELETE statement.

CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11), 
                          @title VARCHAR(20), @title_type CHAR(12))
AS

BEGIN TRAN
    INSERT titles(title_id, title, type)
    VALUES (@title_id, @title, @title_type)

    IF (@@ERROR <> 0) BEGIN
        PRINT 'Unexpected error occurred!'
        ROLLBACK TRAN
        RETURN 1
    END

    INSERT titleauthor(au_id, title_id)
    VALUES (@au_id, @title_id)

    IF (@@ERROR <> 0) BEGIN
        PRINT 'Unexpected error occurred!'
        ROLLBACK TRAN
        RETURN 1
    END

COMMIT TRAN

RETURN 0

This kind of solution contains substantial repetition especially if your business logic requires more than two Transact-SQL statements to be implemented. A more elegant solution is to group codes into a generic error handling procedure:

CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),
                          @title VARCHAR(20), @title_type CHAR(12))
AS

BEGIN TRAN
    INSERT titles(title_id, title, type)
    VALUES (@title_id, @title, @title_type)

    IF (@@ERROR <> 0) GOTO ERR_HANDLER

    INSERT titleauthor(au_id, title_id)
    VALUES (@au_id, @title_id)

    IF (@@ERROR <> 0) GOTO ERR_HANDLER

COMMIT TRAN

RETURN 0

ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1

Transaction in SQL Server

2012-03-26T21:27:00.000-07:00

A transaction must follows this properties:-

In a perfect transaction world, a transaction must contain a series of properties known as ACID. These properties are:

Atomicity
A transaction is an atomic unit of work or collection of separate operations. So, a transaction succeeds and is committed to the database only when all the separate operations succeed. On the other hand, if any single operations fail during the transaction, everything will be considered as failed and must be rolled back if it is already taken place. Thus, Atomicity helps to avoid data inconsistencies in database by eliminating the chance of processing a part of operations only.

Consistency
A transaction must leave the database into a consistent state whether or not it is completed successfully. The data modified by the transaction must comply with all the constraints in order to maintain integrity.

Isolation
Every transaction has a well defined boundary. One transaction will never affect another transaction running at the same time. Data modifications made by one transaction must be isolated from the data modification made by all other transactions. A transaction sees data in the state as it was before the second transaction modification takes place or in the state as the second transaction completed, but under any circumstance a transaction can not be in any intermediate state.

Durability
If a transaction succeeds, the updates are stored in permanent media even if the database crashes immediately after the application performs a commit operation. Transaction logs are maintained so that the database can be restored to its original position before failure takes place.

Using OUTPUT Parameters in Stored Procedures

2012-03-23T02:21:00.001-07:00

One way to retrieve scalar data in addition to a standard resultset from a stored procedure is to use one or more output parameters. An output parameter is a parameter that is passed into the SQL stored procedure, but whose value can be set in the stored procedure. This assigned parameter, then, is readable back from the application that called the stored procedure.
To use an output parameter you need to indicate that the parameter is intended for output via the OUTPUT keyword. The following snippet shows a stored procedure that returns the set of inventory items through a SELECT statement and uses an output parameter to return the average price:

CREATE PROCEDURE store_GetInventoryWithAveragePrice
(
@AveragePrice money OUTPUT
)
AS
SET @AveragePrice = (SELECT AVG(Price) FROM store_Inventory)
SELECT InventoryID, ProductName, Price, UnitsOnStock
FROM store_Inventory

According to MS-BOL, SQL Server Stored-Procedures can return data in 4 forms:
1. Return Code: which are always an integer value.
2. OUTPUT Parameter: which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time).
3. A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.
4. A global cursor that can be referenced outside the stored procedure.

Specifying OUTPUT keyword to the parameters in the Stored Procedures can return the values of the parameters to the calling program. Lets check this with a simple example by using AdventureWorks database:

view source

print?

01USE [AdventureWorks]

02GO

03 

04--// Create Stored Prcedure with OUTPUT parameter

05CREATE PROCEDURE getContactName

06    @ContactID INT,

07    @FirstName VARCHAR(50) OUTPUT,

08    @LastName  VARCHAR(50) OUTPUT

09AS

10BEGIN

11    SELECT @FirstName = FirstName, @LastName = LastName

12    FROM Person.Contact

13    WHERE ContactID = @ContactID

14end

15GO

16 

17--// Test the Procedure

18DECLARE @CID INT, @FName VARCHAR(50), @LName VARCHAR(50)

19 

20--/ Test# 1

21SET @CID = 100

22EXEC getContactName @ContactID=@CID,

23                    @FirstName=@FName OUTPUT,

24                    @LastName=@LName OUTPUT

25 

26SELECT @FName as 'First Name', @LName as 'Last Name'

27--/ Output

28-- ContactID    First Name  Last Name

29-- 100          Jackie      Blackwell

30 

31--/ Test# 2

32SET @CID = 200

33EXEC getContactName @ContactID=@CID,

34                    @FirstName=@FName OUTPUT,

35                    @LastName=@LName OUTPUT

36 

37SELECT @FName as 'First Name', @LName as 'Last Name'

38--/ Output

39-- ContactID    First Name  Last Name

40-- 200          Martin      Chisholm

41GO

42 

43--// Final Cleanup

44DROP PROCEDURE getContactName

45GO

What is normalization

2012-03-20T21:42:00.002-07:00

Defination : Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored. There are several benefits for using Normalization in Database.

Benefits :

Eliminate data redundancy

Improve performance

Query optimization

Faster update due to less number of columns in one table

Index improvement

There are diff. - diff. types of Normalizations form available in the Database. Lets see one by one.

1. First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an organized database:

Eliminate duplicative columns from the same table.
Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

Remove repetative groups
Create Primary Key


Name	State	Country	Phone1	Phone2	Phone3
John	101	1	488-511-3258	781-896-9897	425-983-9812
Bob	102	1	861-856-6987
Rob	201	2	587-963-8425	425-698-9684
PK			[ Phone Nos ]
?				?
ID	Name	State	Country	Phone
1	John	101	1	488-511-3258
2	John	101	1	781-896-9897
3	John	101	1	425-983-9812
4	Bob	102	1	861-856-6987
5	Rob	201	2	587-963-8425
6	Rob	201	2	425-698-9684

2. Second Normal Form (2NF)Second normal form (2NF) further addresses the concept of removing duplicative data:

· Meet all the requirements of the first normal form.

· Remove subsets of data that apply to multiple rows of a table and place them in separate tables.

· Create relationships between these new tables and their predecessors through the use of foreign keys.

Remove columns which create duplicate data in a table and related a new table with Primary Key – Foreign Key relationship

ID	Name	State	Country	Phone
1	John	101	1	488-511-3258
2	John	101	1	781-896-9897
3	John	101	1	425-983-9812
4	Bob	102	1	861-856-6987
5	Rob	201	2	587-963-8425
6	Rob	201	2	425-698-9684


ID	Name	State	Country		PhoneID	ID	Phone
1	John	101			1	1	488-511-3258
2	Bob	102			2	1	781-896-9897
3	Rob	201			3	1	425-983-9812
					4	2	587-963-8425
					5	3	587-963-8425
					6	3	425-698-9684

3. Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:

· Meet all the requirements of the second normal form.

· Remove columns that are not dependent upon the primary key.

Country can be derived from State also… so removing country

ID	Name	State	Country
1	John	101	1
2	Bob	102	1
3	Rob	201	2

4. Fourth Normal Form (4NF)

Finally, fourth normal form (4NF) has one additional requirement:

· Meet all the requirements of the third normal form.

· A relation is in 4NF if it has no multi-valued dependencies.

If PK is composed of multiple columns then all non-key attributes should be derived from FULL PK only. If some non-key attribute can be derived from partial PK then remove it

The 4NF also known as BCNF NF

TeacherID	StudentID	SubjectID	StudentName
101	1001	1	John
101	1002	2	Rob
201	1002	3	Bob
201	1001	2	Rob


TeacherID	StudentID	SubjectID	StudentName
101	1001	1	X
101	1002	2	X
201	1001	3	X
201	1002	2	X

View in Sql Server

2012-03-19T21:36:00.001-07:00

What is View
Use of Views
General syntax for Views

Creating a View
Get result from a View
Drop a View

Different Types of Views

System View

Information View
Catalog View
Dynamic Management View

User Defined View

When to use a View

View creation options

Schema binding option
Encryption

Use SSMS for creating a View
Summary
Topics to be covered in the next article
References and future study
History

What is a View

A View is a "Virtual Table". It is not like a simple table, but is a virtual table which contains columns and data from different tables (may be one or more tables). A View does not contain any data, it is a set of queries that are applied to one or more tables that is stored within the database as an object. After creating a view from some table(s), it used as a reference of those tables and when executed, it shows only those data which are already mentioned in the query during the creation of the View.

In the above diagram, we have created a View (View_Table1_Table2) from Table1 and Table2. So theView_Table1_Table2 will only show the information from those columns. Let's checkout the basic syntax for creating a View:

CREATE VIEW  [View Name]
AS
[SELECT Statement]

Use of a View

Views are used as security mechanisms in databases. Because it restricts the user from viewing certain column and rows. Views display only those data which are mentioned in the query, so it shows only data which is returned by the query that is defined at the time of creation of the View. The rest of the data is totally abstract from the end user.

Along with security, another advantage of Views is data abstraction because the end user is not aware of all the data in a table.

General syntax for Views

In this section, I will describe how to create Views, select data from Views, and deleting Views. I have created a database named ViewDemo. It has a table called EmpInfo as shown below:

which contains the following data:

All the examples I have described are from this database.

Creating a View

Below is the general syntax for creating a View:

CREATE VIEW [View_Name]
 AS
 [SELECT Statement]

For example:

CREATE VIEW SampleView
As
SELECT EmpID, EmpName  FROM EmpInfo

which will create a View with the name SampleView that will only contain EmpID, EMPName.

Get result from a View

This is similar to a Select statement:

select * from SampleView

Now have a look at the output of SampleView:

Drop a View

DROP VIEW SampleView

Now if we want to select data from SampleView, we will get the following error:

Different types of Views

There are two different types of Views:

System Views

Information Schema View
Catalog View
Dynamic Management View (DMV)

User Defined Views

Simple View
Complex View

Now we will take a look at the different types of Views in SQL Server 2005.

System Views

In SQL Server, there are a few system databases like Master, Temp, msdb, and tempdb. Each and every database has its own responsibility, like Master data is one of the template databases for all the databases which are created in SQL Server 2005. Similarly, System Views are predefined Microsoft created Views that already exist in the Master database. These are also used as template Views for all new databases. These system Views will be automatically inserted into any user created database. There are around 230 system Views available.

We can explore system Views from the SQL Server Management Studio. Expand any database > View > System View.

In SQL Server, all system Views are divided into different schemas. These are used for the security container of the SQL Server database. We can categorize system Views in the following way:

Information Schema View
Catalog View
Dynamic Management View (DMV)

Now all the above categories are themselves huge topics, so I will not go into the details of them. Let us go through an overview of those View types:

Information View

These are one of the most important system grouped Views. There are twenty different schema Views in this group. These are used for displaying most physical information of a database, such as table and columns. The naming convention of this type of Views is INFORMATION_SCHEMA.[View Name]. From the system View image, we can get the names of a few Information Schema Views.

Let's see this with an example.

I have create a database named ViewDemo. It has a table called EmpInfo and the below diagram shows you the design of the table:

Now if we want to know detailed information on the columns of the table Empinfo using the View, we have to run the following query:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME='EmpInfo'

The following will be the output:

Similarly we can use other schema Views to read database information.

Catalog View

This type of Views were introduced in SQL Server 2005. Catalog Views are categorized in to different groups also. These are used to show database self describing information.

For example:

select * from sys.tables

and following is a sample output:

Dynamic Management View

This is newly introduced in SQL Server 2005. These Views give the database administrator information about the current state of the SQL Server machine. These values help the administrator to diagnose problems and tune the server for optimal performance. In SQL Server 2005, there are two types of DMVs:

Server-scoped DMV: Stored in the Master database.
Database-scoped DMV: Specific to each database.

For example, if we want to check all SQL Server connections, we can use the following query:

SELECT
connection_id,
session_id,client_net_address,
auth_scheme
FROM sys.dm_exec_connections

And the following is the sample output:

If you want to know the details on DMV, here is a complete article on CodeProject: Dynamic Management Views [DMV] - A SQL Server 2005 Feature [^].

Note: There are many things to learn on system Views, I have just introduced them for beginners. If anyone has more interest, look into this article: System Views in SQL Server 2005 [^].

User Defined View

Up till now I described about system Views, now we will take a look at user defined Views. These Views are created by a user as per requirements. There is no classification for UDVs and how to create them, I have already explained the syntax. Now we can take a look at another View creation.

CREATE VIEW DemoView
AS
SELECT EmpID, EmpName, Phone
FROM EmpInfFROM EmpInfo

When to Use a View

There are a number of scenarios where we will like to create our own View:

To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.
To control access to rows and columns of data.

View Creation Option

There are two different options for creating a View:

Schema Binding Option
Encryption

Schema BindSchema Binding Option

If we create a View with the SCHEMABINDING option, it will lock the tables being referred to by the View and restrict all kinds of changes that may change the table schema (no Alter command). While creating a schema binding View, we can't mention "Select * from tablename" with the query. We have to mention all column names for reference.

For example:

CREATE VIEW DemoSampleView
With SCHEMABINDING
As
SELECT
          EmpID,
          EmpName,
FROM DBO.EmpInfo

And one more thing that we need to remember, while specifying the database name, we have to use Dbo.[DbName]. After creating the View, try to alter the table EmpInfo, we won't be able to do it! This is the power of the SCHEMABINDING option.

If we want to change/alter the definition of a table which is referred by a schema bound View, we will get the following error message:

Encryption

This option encrypts the definition. This option encrypts the definition of the View. Users will not be able to see the definition of the View after it is created. This is the main advantage of the View where we can make it secure:

CREATE VIEW DemoView
With ENCRYPTION.EmpInfo

Note: Once the View is encrypted, there is no way to decrypt it again.

Use SSMS for Creating a View

SQL Server Management Studio provides a handy GUI for creating and managing Views. In the Object Explorer tab, it lists all the Views corresponding to a database. In this section, we will just quickly check how SSMS is used to create and maintain a View.

First expand ViewDemoDB > Move to View. Right click on the View folder.

When we will click on New View, the following screen will appear. In ViewDemoDB, we have two datatables. Now I am going to create a View from the EmpInfo table.

Select EmpInfo, click on Add. You will be redirected to the Create View screen where you can configure the View creation. Check the following image:

The above image shows three sections where we can select the table name or in the below section, we can write the query for the View. When done, just click on the Save button on the toolbar. Give the name of the View and click on OK.

Now go to ViewDemoDB > View > Expand View folder. Here, along with system Views, you can see the View that we created right now.

So this is our user defined View. If we right click on it, we will get the option of opening the View and which will show the result of the View.

We can also create a View from a View itself in a similar way that we have done with a table.

Union and union All in Sql Server

2012-03-19T21:25:00.003-07:00

Union vs. Union All Query Syntax

The purpose of the SQL UNION and UNION ALL commands are to combine the results of two or more queries into a single result set consisting of all the rows belonging to all the queries in the union. The question becomes whether or not to use the ALL syntax.

The main difference between UNION ALL and UNION is that, UNION only selects distinct values, while UNION ALL selects all values (including duplicates).

The syntax for UNION {ALL} is as follows:

[SQL Statement 1]
UNION {ALL}
[SQL Statement 2]

Sample Data

Use Authors table in SQL Server Pubs database or just use a simple table with these values (obviously simplified to just illustrate the point):

City State Zip

Nashville TN 37215

Lawrence KS 66044

Corvallis OR 97330

UNION ALL Example

This SQL statement combines two queries to retrieve records based on states. The two queries happen to both get records from Tennessee ('TN'):

SELECT City, State, Zip FROM Authors WHERE State IN ('KS', 'TN')
UNION ALL
SELECT City, State, Zip FROM Authors WHERE IN ('OR' 'TN')

Result of UNION ALL syntax:

City State Zip

Nashville TN 37215

Lawrence KS 66044

Nashville TN 37215

Corvallis OR 97330

Notice how this displays the two query results in the order they appear from the queries. The first two records come from the first SELECT statement, and the last two records from the second SELECT statement. The TN record appears twice, since both SELECT statements retrieve TN records.

Union Query SQL Example

Using the same SQL statements and combining them with a UNION command:

SELECT City, State, Zip FROM Authors WHERE State IN ('KS', 'TN')
UNION
SELECT City, State, Zip FROM Authors WHERE IN ('OR' 'TN')

Result of UNION Query

City State Zip

Corvallis OR 97330

Lawrence KS 66044

Nashville TN 37215

Notice how the TN record only appears once, even though both SELECT statements retrieve TN records. The UNION syntax automatically eliminates the duplicate records between the two SQL statements and sorts the results. In this example the Corvallis record appears first but is from the second SELECT statement.

Temporary Index Issues for UNION Queries

A UNION query, by definition, eliminates all duplicate rows (as opposed to UNION ALL) and is slower. To do this in SQL Server, it must build a temporary index on all the columns returned by both queries. If the index cannot be build for the queries, you will get a SQL error. In this situation, UNION ALL may be the solution if you don't have rights to do this. Alternatively, you may need to use a temporary table with INSERT queries to store the results of each query and use a standard SELECT query to group and get the unique values.

City	State	Zip
Nashville	TN	37215
Lawrence	KS	66044
Nashville	TN	37215
Corvallis	OR	97330

SelectSql

What's the difference between a temp table and table variable in SQL Server?

Introduction to Locking in SQL Server

Introduction

Transaction Isolation Levels

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

Lock types

Locking optimizer hints

Deadlocks

View locks (sp_lock)

Retrieve Last Inserted Identity of Record

Getting started with SQL Server Reporting Services links

Constraints sql server

SQL Server Stored Procedure Coding Standard

Use Sysobjects in SQL Server to Find Useful Database Information

sys.procedure in sql server

INFORMATION_SCHEMA.COLUMNS

INFORMATION_SCHEMA.TABLES

INFORMATION_SCHEMA VIEW

Temporary table vs Table variable vs derived Table

What is Derived Tables in SQL

Pivots with Dynamic Columns in SQL Server 2005

What are master, msdb, tempdb databases in Sql Server?

what is collations in sql server

Database, Tables and columns with different collation

Databases with different collation

Tables and columns with different collation

Comparing characters on the databases with different collation

Simulating case sensitivity in a case in-sensitive database

Simulating case insensitivity in a case sensitive database

Simulating Accent sensitivity in a Accent insensitive database

Change the collation

Find the collation of the current server

Find the collation of a particular database

Find the collation of the current database

Find collation of all columns in a table

Find all collation available in SQL Server

Conclusion

Improving SQL Performance

Coalesce in Sql server example

Summary

Alternatives To SQL Server Cursors

Introduction

Problem: The Developer or Designer is New to SQL Server

Resolution

Problem: Need to update a table with incrementing column values for records already in the table

Resolution

Problem: Need to use a custom identity function to perform an insert

Resolution

Problem: Need to compare a row to adjacent rows in a table

Resolution

SQL Server Transactions and Error Handling

Transactions

Nested Transactions

SAVE TRAN and Save Points

Error Handling

Transaction in SQL Server

Using OUTPUT Parameters in Stored Procedures

What is normalization

View in Sql Server

Table of Contents

What is a View

Use of a View

General syntax for Views

Creating a View

Get result from a View

Drop a View

Different types of Views

System Views

Information View

Catalog View

Dynamic Management View

User Defined View

When to Use a View

View Creation Option

Schema BindSchema Binding Option

Encryption