SQL World

TRIGGERS

2013-12-19T16:53:00.000+05:30

A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updating of data. It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action on the table that they are assigned to.

Types of Triggers:

1) DDL Triggers

We can create triggers on DDL statements (like CREATE, ALTER, and DROP).

We can use only FOR/AFTER clause in DDL triggers not INSTEAD OF clause means we can make only After Trigger on DDL statements.

DDL trigger can be used to observe and control actions performed on the server, and to audit these operations. DDL triggers can be used to manage administrator tasks such as auditing and regulating database operations.

2) DML Triggers

We can create triggers on DML statements (like INSERT, UPDATE, and DELETE) DML Triggers are of two types

After Trigger (using FOR/AFTER CLAUSE)

This type of trigger fires after SQL Server finishes the execution of the action successfully that fired it.

Example: If you insert row in a table then the trigger with the insert event on this table will fire only after the row passes all the constraints, like as primary key constraint, and some rules. If the row insertion fails, SQL Server will not fire the After Trigger.

Instead of Trigger (using INSTEAD OF CLAUSE)

This type of trigger fires before SQL Server starts the execution of the action that fired it. It does differ from the AFTER trigger. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.

Example: If you insert row in a table then the trigger with the insert event on this table will fire before the row passes all the constraints. If the row insertion fails, SQL Server will fire the Instead of Trigger.

3) CLR Triggers

CLR triggers are special type of triggers that based on the CLR (Common Language Runtime) in .net framework. CLR integration of triggers has been introduced with SQL Server 2008 and allows for triggers to be coded in one of .NET languages like C#, Visual Basic and F#.

4) Logon Triggers

Logon triggers are special type of trigger that fire when LOGON event of SQL Server is raised. This event is raised when a user session is being established with SQL Server that is made after the authentication phase finishes, but before the user session is actually established. Hence, all messages that we define in the trigger such as error messages, will be redirected to the SQL Server error log.

Logon triggers do not fire if authentication fails. We can use these triggers to audit and control server sessions, such as to track login activity or limit the number of sessions for a specific login.

Advantages of Triggers:

Triggers can be used as an alternative method for implementing referential integrity constraints.
By using triggers, business rules and transactions are easy to store in database and can be used consistently even if there are future updates to the database.
It controls on which updates are allowed in a database.
When a change happens in a database a trigger can adjust the change to the entire database.
Triggers are used for calling stored procedures.
SQL triggers provide an alternative way to run scheduled tasks. By using SQL triggers, you don’t have to wait to run the scheduled tasks because the triggers are invoked automatically before or after a change is made to the data in tables.
SQL triggers are very useful to audit the changes of data in tables.

Disadvantages (Problems) of Triggers:

It is easy to view table relationships, constraints, indexes, stored procedure in database but triggers are difficult to view.
Triggers execute invisible to client-application. They are not visible or can be traced in debugging code.
It is hard to follow their logic as it they can be fired before or after the database insert/update happens.
It is easy to forget about triggers and if there is no documentation it will be difficult to figure out for new developers for their existence.
Triggers run every time when the database fields are updated and it is overhead on system. It makes system run slower.
Triggers work over tempdb, so extensive use of them needs to be taken into account when thinking about tempdb

I will update the following concepts on Triggers in my next posts.

Examples of triggers
Disabling a trigger
Enable a trigger
Drop a trigger
Find the Triggers in a database

Populating a Hierarchical Table Using Hierarchical Methods in SQL Server 2012

2013-12-04T15:29:00.000+05:30

SQL Server 2012 have greate feature to create Hierarchical Table Using Hierarchical Methods.

Creating EmployeeOrg table for implement Hierarchy.

USE AdventureWorks2012 ;

CREATE TABLE HumanResources.EmployeeOrg

(

OrgNode HIERARCHYID PRIMARY KEY CLUSTERED,

OrgLevel AS OrgNode.GetLevel(),

EmployeeID INT UNIQUE NOT NULL,

EmpName VARCHAR(20) NOT NULL,

Title VARCHAR(20) NULL

) ;

CREATE UNIQUE INDEX EmployeeOrgNc1

ON HumanResources.EmployeeOrg(OrgLevel, OrgNode) ;

To insert the root of the hierarchy tree:

INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)

VALUES (HIERARCHYID::GetRoot(), 6, 'Raju', 'CEO') ;

To create a procedure for entering new nodes:

1.      To simplify entering data, create the following stored procedure to add employees to the EmployeeOrg table. The procedure accepts input values about the employee being added. This includes the EmployeeID of the new employee's manager, the new employee's EmployeeID number, and their first name and title. The procedure uses GetDescendant() and also the GetAncestor() method. Execute the following code to create the procedure:

CREATE PROC AddEmp(@mgrid int, @empid int, @e_name varchar(20), @title varchar(20))

BEGIN

DECLARE @mOrgNode hierarchyid, @lc hierarchyid

SELECT @mOrgNode = OrgNode

FROM HumanResources.EmployeeOrg

WHERE EmployeeID = @mgrid

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

SELECT @lc = max(OrgNode)

FROM HumanResources.EmployeeOrg

WHERE OrgNode.GetAncestor(1) =@mOrgNode ;

INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)

VALUES(@mOrgNode.GetDescendant(@lc, NULL), @empid, @e_name, @title)

COMMIT

END ;

2.       The following example adds the remaining 4 employees that report directly or indirectly to David.

EXEC AddEmp 6, 10, 'Srinivas', 'Marketing Specialist';

EXEC AddEmp 6, 55, 'Rambabu', 'Marketing Expert';

EXEC AddEmp 55, 269, 'Bhanu', 'Marketing Assistant';

EXEC AddEmp 10, 272, 'Kotesh', 'Development Assistant';

3.       Again, execute the following query examine the rows in the EmployeeOrg table

SELECT OrgNode.GetAncestor(1) AS Ancestor, OrgNode.ToString() AS Text_OrgNode, OrgNode, OrgLevel, EmployeeID, EmpName, Title

FROM HumanResources.EmployeeOrg ;

Here is the result set.

Normalization in SQL Server

2013-10-28T15:09:00.000+05:30

Normalization with Examples

According to the database rules, below are the lists of Normalization forms

1. 1NF (Eliminating Repeating Groups)

Make a separate table for each set of related attributes and give each table a primary key. In simple words I can say it as

There are no repeating or duplicate fields.
Each cell contains only a single value.
Each record is unique and identified by primary key

Let’s see the example below:

Prior to Normalization

Item Colors Price Tax

Pen red, blue 2.0 0.20

Scale red, yellow 2.0 0.20

Pen red, blue 2.0 0.20

Bag blue, black 150.00 7.80

This table is not in first normal form because:

There are multiple fields in color lab.
Records are repeating (Duplicate records) or no primary key.

First Normal Form (1NF)

Item Colors Price Tax

Pen red 2.0 0.20

Pen blue 2.0 0.20

Scale red 2.0 0.20

Scale yellow 2.0 0.20

Bag blue 150.00 7.80

Bag black 150.00 7.80

This table is now in first normal form.

2. 2 NF (Eliminating Redundant Data)

If an attribute is dependent on only part of the multivalued key, then remove it to a separate table. In simple words,

It should meet all the requirements of the first normal form.
It should remove subsets of data that apply to multiple rows of a table and place them in separate tables.
It creates relationships between these new tables and their predecessors through the use of foreign keys.

The First Normal form deals with the atomicity whereas the Second Normal Form deals with the relationship between the composite key columns and non-key columns. To achieve the next progressive level your table should satisfy the requirement of First Normal Form then move towards the Second Normal Form.

Let’s introduce a Review table as an example:

Item Colors Price Tax

Pen red 2.0 0.20

Pen blue 2.0 0.20

Scale red 2.0 0.20

Scale yellow 2.0 0.20

Bag blue 150.00 7.80

Bag black 150.00 7.80

Table is not in Second Normal Form because the price and tax depends on the item, but not color.

Item Colors

Pen red

Pen blue

Scale red

Scale yellow

Bag blue

Bag black

Item Price Tax

Pen 2.0 0.20

Scale 2.0 0.20

Bag 150.00 7.80

Tables are now in Second Normal Form.

3. 3NF (eliminate Columns not dependent on the Key)

If attributes do not contribute description of the key, then remove it to the separate table. All the attributes must be directly dependent on the primary key. In simple words,

It should meet all the requirements of the second normal form.
It should remove columns that are not dependent upon the primary key.
In the Third Normal Form all columns depend upon the primary key. When one column depends upon the other column, tables break the rule and turn into the dependency on the primary key.

Item Colors

Pen red

Pen blue

Scale red

Scale yellow

Bag blue

Bag black

Item Price Tax

Pen 2.0 0.20

Scale 2.0 0.20

Bag 150.00 7.80

Tables are not in Second Normal Form because tax depends on price, not item.

Item Colors

Pen red

Pen blue

Scale red

Scale yellow

Bag blue

Bag black

Item Price

Pen 2.0

Scale 2.0

Bag 150.00

Price Tax

2.0 0.20

150.00 7.80

ON UPDATE CASCADE and ON DELETE CASCADE in SQL Server

2013-08-23T20:48:00.000+05:30

CASCADE allows deletions or updates of key values to cascade through the tables defined to have foreign key relationships that can be traced back to the table on which the modification is performed. ON DELETE CASCADE specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted. ON UPDATE CASCADE specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are also updated to the new value specified for the key.

Let me reproduce the simple scenario in T-SQL.

-- Creating Table Products

CREATE TABLE [dbo].[Products]

(

[ProductID] [int] NOT NULL PRIMARY KEY CLUSTERED,

[ProductDesc] [varchar](50) NOT NULL

)

-- Creating Table ProductDetails

CREATE TABLE [dbo].[ProductDetails]

(

[ProductDetailID] [int] NOT NULL PRIMARY KEY CLUSTERED,

[ProductID] [int] NOT NULL

CONSTRAINT [FK_ProductDetails_Products] FOREIGN KEY([ProductID])

REFERENCES [dbo].[Products] ([ProductID])

ON UPDATE CASCADE

ON DELETE CASCADE,

[Total] [int] NOT NULL

)

-- Insert Data into Table

INSERT INTO Products (ProductID, ProductDesc)

VALUES (1, 'Bike'),(2, 'Car'),(3, 'Books')

INSERT INTO ProductDetails ([ProductDetailID],[ProductID],[Total])

VALUES (1, 1, 200),(2, 1, 100),(3, 1, 111),(4, 2, 200),(5, 3, 100),

(6, 3, 100),(7, 3, 200)

--Select Data from Tables

SELECT *

FROM Products

SELECT *

FROM ProductDetails

On Delete:

--Delete Data from Products Table

DELETE

FROM Products

WHERE ProductID = 1

--Select Data from Tables Again

SELECT *

FROM Products

SELECT *

FROM ProductDetails

On Update:

--Update Data from Tables

UPDATE Products

SET ProductID = 5

WHERE ProductID = 3

--Select Data from Tables Again

SELECT *

FROM Products

SELECT *

FROM ProductDetails

How to Add a Linked Server

2013-02-12T17:03:00.003+05:30

Adding a Linked server can be done by either using the GUI interface or the sp_addlinkedserver command.

Adding a linked Server using the GUI

There are two ways to add another SQL Server as a linked server. Using the first method, you need to specify the actual server name as the “linked server name”. What this means is that everytime you want to reference the linked server in code, you will use the remote server’s name. This may not be beneficial because if the linked server’s name changes, then you will have to also change all the code that references the linked server. I like to avoid this method even though it is easier to initially setup. The rest of the steps will guide you through setting up a linked server with a custom name:

To add a linked server using SSMS (SQL Server Management Studio), open the server you want to create a link from in object explorer.

In SSMS, Expand Server Objects -> Linked Servers -> (Right click on the Linked Server Folder and select “New Linked Server”)

Add New Linked Server

The “New Linked Server” Dialog appears. (see below).

Linked Server Settings

For “Server Type” make sure “Other Data Source” is selected. (The SQL Server option will force you to specify the literal SQL Server Name)

Type in a friendly name that describes your linked server (without spaces). I use AccountingServer.

Provider – Select “Microsoft OLE DB Provider for SQL Server”

Product Name – type: SQLSERVER (with no spaces)

Datasource – type the actual server name, and instance name using this convention: SERVERNAMEINSTANCENAME

ProviderString – Blank

Catalog – Optional (If entered use the default database you will be using)

Prior to exiting, continue to the next section (defining security)
Define the Linked Server Security

Linked server security can be defined a few different ways. The different security methods are outlined below. The first three options are the most common:

Option Name	Description
Be made using the login’s current security context	Most Secure. Uses integrated authentication, specifically Kerberos delegation to pass the credentials of the current login executing the request to the linked server. The remote server must also have the login defined. This requires establishing Kerberos Constrained Delegation in Active Directory, unless the linked server is another instance on the same Server. If instance is on the same server and the logins have the appropriate permissions, I recommend this one.
Be made using this security context	Less Secure. Uses SQL Server Authentication to log in to the linked server. The credentials are used every time a call is made.
Local server login to remote server login mappings	You can specify multiple SQL Server logins to use based upon the context of the user that is making the call. So if you have George executing a select statement, you can have him execute as a different user’s login when linking to the linked server. This will allow you to not need to define “George” on the linked server.
Not be made	If a mapping is not defined, and/or the local login does not have a mapping, do not connect to the linked server.
Be made without using a security context	Connect to the server without any credentials. I do not see a use for this unless you have security defined as public.

Within the same Dialog on the left menu under “Select a Page”, select Security
Enter the security option of your choice.
Linked Server Security Settings
Click OK, and the new linked server is created

Execute Keyword in SQL Server 2012

2012-12-17T13:29:00.000+05:30

Today, I have provided an article showing you an improved version of the execute keyword in SQL Server 2012. The EXECUTE keyword is used to execute a command string. You cannot change the column name and datatype using the execute keyword in SQL Server 2005/2008. You have to modify the stored procedure respectively. The previous version of SQL Server only has the WITH RECOMPILE option to force a new plan to be re-compiled. The ability to do that in SQL Server 2012dramatically improves this part. In SQL Server 2012, there is no need to modify a stored procedure. You can change the column name and datatype using the execute keyword. So let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

The table looks as in the following:

Create TABLE UserDetail

(

User_Id int NOT NULL IDENTITY(1,1),

FirstName varchar(20),

LastName varchar(40) NOT NULL,

Address varchar(255),

PRIMARY KEY (User_Id)

)

INSERT INTO UserDetail(FirstName, LastName, Address)

VALUES ('Smith', 'Kumar','Capetown'),

('Crown', 'sharma','Sydney'),

('Copper', 'verma','Jamaica'),

('lee', 'verma','Sydney')

Now create a stored procedure for the select statement in SQL Server 2008:

Create PROCEDURE SelectUserDetail

begin

select FirstName,LastName, Address from UserDetail

end

Now use an Execute command to run the stored procedure:

-- SQL Server 2008

execute SelectUserDetail

Output

SQL Server 2012

Now we see how we can change the column name and datatype using an execute keyword in SQL Server 2012. The previous version of SQL Server only has the WITH RECOMPILE option to force a new plan to be re-compiled. To do that in SQL Server 2012 dramatically improves this part. We change the FirstName to Name and the datatype varchar(20) to char(4). Now execute the following code in SQL Server 2012:

WITH result SETS

(

Name CHAR(4),

Lastname VARCHAR(20),

Address varchar(25)

)

);

Now Press F5 to run the query and see the result:

Magic of Derived Tables

2012-10-30T20:13:00.002+05:30

Using Derived Tables to Simplify the SQL Server Query Process

Problem
Sometimes querying data is not that simple and there may be the need to create temporary tables or views to predefine how the data should look prior to its final output. Unfortunately there are problems with both of these approaches if you are trying to query data on the fly.

With the temporary tables approach you need to have multiple steps in your process, first to create the temporary table, then to populate the temporary table, then to select data from the temporary table and lastly cleanup of the temporary table.

With the view approach you need to predefine how this data will look, create the view and then use the view in your query. Granted if this is something that you would be doing over and over again this might make sense to just create a view, but let's look at a totally different approach.

Solution
With SQL Server you have the ability to create derived tables on the fly and then use these derived tables within your query. In concept this is similar to creating a temporary table and then using the temporary table in your query, but the approach is much simpler, because it can all be done in one step.

Let's take a look at an example where we query the Sales database to try to find out how many customers fall into various categories based on sales. The categories that we have predefined are as follows:

Ø Total Sales between 0 and 5,000 = Micro

Ø Total Sales between 5,001 and 10,000 = Small

Ø Total Sales between 10,001 and 15,000 = Medium

Ø Total Sales between 15,001 and 20,000 = Large

Ø Total Sales > 20,000 = Very Large

There are several ways that this data can be pulled, but let's look at an approach using a derived table.

The first step is to find out the total sales by each customer, which can be done with the following statement.

SELECT   o.CustomerID,
         SUM(UnitPrice * Quantity) AS TotalSales
FROM     [Order Details] AS od
         INNER JOIN Orders AS o
           ON od.OrderID = o.OrderID
GROUP BY o.CustomerID

This is a partial list of the output:

CustomerID	TotalSales
ALFKI	4596.2000
ANATR	1402.9500
ANTON	7515.3500
...
WOLZA	3531.9500

The next step is to classify the TotalSales value into the OrderGroups that were specified above:

SELECT   o.CustomerID,
         SUM(UnitPrice * Quantity) AS TotalSales,
         CASE
           WHEN SUM(UnitPrice * Quantity)
               BETWEEN 0 AND 5000 THEN 'Micro'
           WHEN SUM(UnitPrice * Quantity)
               BETWEEN 5001 AND 10000 THEN 'Small'
           WHEN SUM(UnitPrice * Quantity)
               BETWEEN 10001 AND 15000 THEN 'Medium'
           WHEN SUM(UnitPrice * Quantity)
               BETWEEN 15001 AND 20000 THEN 'Large'
           WHEN SUM(UnitPrice * Quantity)
               > 20000 THEN 'Very Large'
         END AS OrderGroup
FROM     [Order Details] AS od
         INNER JOIN Orders AS o
          ON od.OrderID = o.OrderID
GROUP BY o.CustomerID

This is a partial list of the output:

CustomerID	TotalSales	OrderGroup
ALFKI	4596.2000	Micro
ANATR	1402.9500	Micro
ANTON	7515.3500	Small
...
WOLZA	3531.9500	Micro

The next step is to figure out how many customers fit into each of these groups and this is where the derived table comes into play. Take a look at the following query which uses a derived table called OG. What we are doing here is using the same query from the step above, but calling this derived table OG. Then we are selecting data from this derived table for our final output just like we would with any other query. All of the columns that are created in the derived table are now available for our final query.

SELECT   OG.OrderGroup,
         COUNT(OG.OrderGroup) AS OrderGroupCount
FROM     (SELECT   o.CustomerID,
                   SUM(UnitPrice * Quantity) AS TotalSales,
                   CASE
                     WHEN SUM(UnitPrice * Quantity)
                       BETWEEN 0 AND 5000 THEN 'Micro'
                     WHEN SUM(UnitPrice * Quantity)
                       BETWEEN 5001 AND 10000 THEN 'Small'
                     WHEN SUM(UnitPrice * Quantity)
                       BETWEEN 10001 AND 15000 THEN 'Medium'
                     WHEN SUM(UnitPrice * Quantity)
                       BETWEEN 15001 AND 20000 THEN 'Large'
                     WHEN SUM(UnitPrice * Quantity)
                       > 20000 THEN 'Very Large'
                   END AS OrderGroup
          FROM     [Order Details] AS od
                   INNER JOIN Orders AS o
                     ON od.OrderID = o.OrderID
          GROUP BY o.CustomerID) AS OG
GROUP BY OG.OrderGroup

This is the complete list of the output from the above query.

OrderGroup	OrderGroupCount
Large	10
Medium	11
Micro	33
Small	15
Very Large	20

Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database

2012-07-17T17:24:00.000+05:30

SELECT K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT I1.TABLE_NAME, I2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS I1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE I2
ON I1.CONSTRAINT_NAME = I2.CONSTRAINT_NAME
WHERE I1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
ORDER BY 1,2,3,4

SQL Tuning/SQL Optimization Techniques:

2012-07-11T19:36:00.002+05:30

1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.

For Example: Write the query as

SELECT id, first_name, last_name, age, subject FROM student_details;

Instead of:

SELECT * FROM student_details;

2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.
For Example: Write the query as

SELECT subject, count(subject)
FROM student_details
WHERE subject != 'Science'
AND subject != 'Maths'
GROUP BY subject;

Instead of:

SELECT subject, count(subject)
FROM student_details
GROUP BY subject
HAVING subject!= 'Vancouver' AND subject!= 'Toronto';

3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
For Example: Write the query as

SELECT name
FROM employee
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)
FROM employee_details)
AND dept = 'Electronics';

Instead of:

SELECT name
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
AND emp_dept = 'Electronics';

4) Use operator EXISTS, IN and table joins appropriately in your query.
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.

For Example: Write the query as

Select * from product p
where EXISTS (select * from order_items o
where o.product_id = p.product_id)

Instead of:

Select * from product p
where product_id IN
(select product_id from order_items

5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
For Example: Write the query as

SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);

Instead of:

SELECT DISTINCT d.dept_id, d.dept
FROM dept d,employee e
WHERE e.dept = e.dept;

6) Try to use UNION ALL in place of UNION.
For Example: Write the query as

SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team;

Instead of:

SELECT id, first_name, subject
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team;

7) Be careful while using conditions in WHERE clause.
For Example: Write the query as

SELECT id, first_name, age FROM student_details WHERE age > 10;

Instead of:

SELECT id, first_name, age FROM student_details WHERE age != 10;

Write the query as

SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE 'Chan%';

Instead of:

SELECT id, first_name, age
FROM student_details
WHERE SUBSTR(first_name,1,3) = 'Cha';

Write the query as

SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE NVL ( :name, '%');

Instead of:

SELECT id, first_name, age
FROM student_details
WHERE first_name = NVL ( :name, first_name);

Write the query as

SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)

Instead of:

SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)

Write the query as

SELECT id, name, salary
FROM employee
WHERE dept = 'Electronics'
AND location = 'Bangalore';

Instead of:

SELECT id, name, salary
FROM employee
WHERE dept || location= 'ElectronicsBangalore';

Use non-column expression on one side of the query because it will be processed earlier.

Write the query as

SELECT id, name, salary
FROM employee
WHERE salary < 25000;

Instead of:

SELECT id, name, salary
FROM employee
WHERE salary + 10000 < 35000;

Write the query as

SELECT id, first_name, age
FROM student_details
WHERE age > 10;

Instead of:

SELECT id, first_name, age
FROM student_details
WHERE age NOT = 10;

8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause.
For Example: Write the query as

SELECT id FROM employee
WHERE name LIKE 'Ramesh%'
and location = 'Bangalore';

Instead of:

SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee
WHERE name LIKE 'Ramesh%';

9) To store large binary objects, first place them in the file system and add the file path in the database.

10) To write queries which provide efficient performance follow the general SQL standard rules.

a) Use single case for all SQL verbs
b) Begin all SQL verbs on a new line
c) Separate all words with a single space
d) Right or left aligning verbs within the initial SQL verb

Table Variables Vs Temporary Tables

2012-05-25T18:38:00.001+05:30

Feature	Table Variables	Temporary Tables
Scope	Current batch	Current session, nested stored procedures. Global: all sessions.
Usage	UDFs, Stored Procedures, Triggers, Batches.	Stored Procedures, Triggers, Batches.
Creation	DECLARE statement only.	CREATE TABLE statement. SELECT INTO statement.
Table name	Maximum 128 characters.	Maximum 116 characters.
Column data types	Can use user-defined data types. Can use XML collections.	User-defined data types and XML collections must be in tempdb to use.
Collation	String columns inherit collation from current database.	String columns inherit collation from tempdb database.
Indexes	Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.	Indexes can be added after the table has been created.
Constraints	PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.	PRIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed.
Post-creation DDL (indexes, columns)	Statements are not allowed.	Statements are allowed.
Data insertion	INSERT statement (SQL 2000: cannot use INSERT/EXEC).	INSERT statement, including INSERT/EXEC. SELECT INTO statement.
Insert explicit values into identity columns (SET IDENTITY_INSERT).	The SET IDENTITY_INSERT statement is not supported.	The SET IDENTITY_INSERT statement is supported.
Truncate table	Not allowed.	Allowed.
Destruction	Automatically at the end of the batch.	Explicitly with DROP TABLE statement. Automatically when session ends. (Global: also when other sessions have no statements using table.)
Transactions	Last only for length of update against the table variable. Uses less than temporary tables.	Last for the length of the transaction. Uses more than table variables.
Stored procedure recompilations	Not applicable.	Creating temp table and data inserts cause procedure recompilations.
Rollbacks	Not affected (Data not rolled back).	Affected (Data is rolled back).
Statistics	Optimizer cannot create any statistics on columns, so it treats table variable has having 1 record when creating execution plans.	Optimizer can create statistics on columns. Uses actual row count for generation execution plan.
Pass to stored procedures	SQL 2008 only, with predefined user-defined table type.	Not allowed to pass, but they are still in scope to nested procedures.
Explicitly named objects (indexes, constraints).	Not allowed.	Allowed, but be aware of multi-user issues.
Dynamic SQL	Must declare table variable inside the dynamic SQL.	Can use temporary tables created prior to calling the dynamic sql.

Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT

2012-05-22T20:20:00.002+05:30

What SCOPE_IDENTITY is

SCOPE_IDENTITY is:

SCOPE_IDENTITY returns the last IDENTITY value inserted into an IDENTITY column in the same scope.
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
A scope is a module; a Stored Procedure, trigger, function, or batch.
Thus, two statements are in the same scope if they are in the same Stored Procedure, function, or batch.
The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

What IDENT_CURRENT is

IDENT_CURRENT is:

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table.

What @@IDENTITY is

@@IDENTITY is:

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement.
If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL.
If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated.
The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.

Differences

The differences between them are:

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.
SCOPE_IDENTITY and @@IDENTITY will return the last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope. A scope is a module; a Stored Procedure, trigger, function, or batch.

Magic of the OUTPUT Clause with INSERT, UPDATE, DELETE

2012-04-09T12:44:00.000+05:30

SQL Server 2005 has new OUTPUT clause, which is quite useful. OUTPUT clause has accesses to inserted and deleted tables (virtual tables) just like triggers. OUTPUT clause can be used to return values to client clause. OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements.

OUTPUT clause can generate table variable, a permanent table, or temporary table. Even though, @@Identity will still work in SQL Server 2005, however I find OUTPUT clause very easy and powerful to use. Let us understand OUTPUT clause using example.

Example 1 : OUTPUT clause into Table with INSERT statement

USE AdventureWorks;
GO

--------Creating the
table which will store permanent table

CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))

----Creating temp table
to store ovalues of OUTPUT clause

DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))

----Insert values in
real table as well use OUTPUT clause to insert

----values
in the temp table.

INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES (2,'SecondVal')

----Check the values in
the temp table and real table

----The
values in both the tables will be same

SELECT * FROM @TmpTable
SELECT * FROM TestTable
----Clean up time
DROP TABLE TestTable
GO
ResultSet 1:
ID TextVal
——————— ————————
1 FirstVal
2 SecondVal
ID TextVal
——————— ———————
1 FirstVal
2 SecondVal

Example 2 : OUTPUT clause with INSERT statement

USE AdventureWorks;
GO

----Creating the table
which will store permanent table

CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))

----Insert values in
real table as well use OUTPUT clause to insert

----values
in the temp table.

INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal
VALUES (2,'SecondVal')
----Clean up time
DROP TABLE TestTable
GO
ResultSet 2:
ID TextVal
——————— ———————
1 FirstVal
(1 row(s) affected)
ID TextVal
——————— ———————
2 SecondVal

Example 3 : OUTPUT clause into Table with UPDATE statement

USE AdventureWorks;
GO

----Creating the table
which will store permanent table

CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))

----Creating temp table
to store ovalues of OUTPUT clause

DECLARE @TmpTable TABLE (ID_New INT, TEXTVal_New VARCHAR(100),

ID_Old

INT, TEXTVal_Old VARCHAR(100))

----Insert values in
real table

INSERT TestTable (ID, TEXTVal)
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
VALUES (2,'SecondVal')

----Update the table and
insert values in temp table using Output clause

UPDATE TestTable
SET TEXTVal = 'NewValue'
OUTPUT Inserted.ID, Inserted.TEXTVal, Deleted.ID, Deleted.TEXTVal INTO @TmpTable
WHERE ID IN (1,2)

----Check the values in
the temp table and real table

----The
values in both the tables will be same

SELECT * FROM @TmpTable
SELECT * FROM TestTable
----Clean up time
DROP TABLE TestTable
GO
ResultSet 3:
ID_New TextVal_New ID_Old TextVal_Old
——————— ——————— ——————— ———————
1 NewValue 1 FirstVal
2 NewValue 2 SecondVal
ID TextVal
——————— ———————
1 NewValue
2 NewValue

Example 4 : OUTPUT clause into Table with DELETE statement

USE AdventureWorks;
GO

----Creating the table
which will store permanent table

CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))

----Creating temp table
to store ovalues of OUTPUT clause

DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))

----Insert values in
real table

INSERT TestTable (ID, TEXTVal)
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
VALUES (2,'SecondVal')

----Update the table and
insert values in temp table using Output clause

DELETE
FROM TestTable

OUTPUT
Deleted.ID

Deleted.TEXTVal INTO @TmpTable
WHERE ID IN (1,2)

----Check the values in
the temp table and real table

----The
values in both the tables will be same

SELECT * FROM @TmpTable
SELECT * FROM TestTable
----Clean up time
DROP TABLE TestTable
GO
ResultSet 4:
ID TextVal
——————— ———————
1 FirstVal
2 SecondVal
ID TextVal
——————— ———————
If you run all the above four example, you will find that OUTPUT clause is very useful.

Working with CTE (Common Table Expressions)

2012-04-03T19:05:00.001+05:30

Introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.

SQL Server supports two types of CTEs—recursive and nonrecursive. In this article, I explain how to create both types. The examples I provide are based on a local instance of SQL Server 2008 and retrieve data from the AdventureWorks2008 sample database.

Working with Common Table Expressions

You define CTEs by adding a WITH clause directly before your SELECT, INSERT, UPDATE, DELETE, or MERGE statement. The WITH clause can include one or more CTEs, as shown in the following syntax:

[WITH <common_table_expression> [,...]]

<common_table_expression>::=

cte_name [(column_name [,...])]

AS (cte_query)

...which can be represented like this...

As you can see, if you include more than one CTE in your WITH clause, you must separate them with commas. In addition, for each CTE, you must provide a name, the AS keyword, and a SELECT statement. You can also provide column names (separated by commas), as long as the number of names match the number of columns returned by the result set.

The SELECT statement in your CTE query must follow the same requirements as those used for creating a view. For details about those requirements, see the topic “CREATE VIEW (Transact-SQL)” in SQL Server Books Online. For more details about CTEs in general, see the topic “WITH common_table_expression (Transact-SQL).”

After you define your WITH clause with the necessary CTEs, you can then reference those CTEs as you would any other table. However, you can reference a CTE only within the execution scope of the statement that immediately follows the WITH clause. After you’ve run your statement, the CTE result set is not available to other statements.

Creating a Nonrecursive Common Table Expression

A nonrecursive CTE is one that does not reference itself within the CTE. Nonrecursive CTEs tend to be simpler than recursive CTEs, which is why I’m starting with this type. In the following example, I create a CTE named cteTotalSales:

WITH

cteTotalSales (SalesPersonID, NetSales)

(

SELECT SalesPersonID, ROUND(SUM(SubTotal), 2)

FROM Sales.SalesOrderHeader

WHERE SalesPersonID IS NOT NULL

GROUP BY SalesPersonID

)

SELECT

sp.FirstName + ' ' + sp.LastName AS FullName,

sp.City + ', ' + StateProvinceName AS Location,

ts.NetSales

FROM Sales.vSalesPerson AS sp

INNER JOIN cteTotalSales AS ts

ON sp.BusinessEntityID = ts.SalesPersonID

ORDER BY ts.NetSales DESC

After I specify the CTE name, I provide two column names, SalesPersonID and NetSales, which are enclosed in parentheses and separated by a comma. That means the result set returned by the CTE query must return two columns.

Next, I provide the AS keyword, then a set of parentheses that enclose the CTE query. In this case, the SELECT statement returns the total sales for each sales person (total sales grouped by salesperson ID). As you can see, the CTE query can include Transact-SQL functions, GROUP BY clauses, or any elements that the SELECT statement in a view definition can include.

I can now reference cteTotalSales in the statement that immediately follows. For this example, I create a SELECT statement that joins the Sales.vSalesPerson view to cteTotalSales, based on the salesperson ID. I then pull the names and locations from the view and the net sales from the CTE. The following table shows the results returned by this statement.

As you saw earlier in the syntax, you can include multiple CTEs in a WITH clause. The following WITH clause includes two CTEs, one named cteTotalSales and one named cteTargetDiff:

WITH

cteTotalSales (SalesPersonID, NetSales)

(

SELECT SalesPersonID, ROUND(SUM(SubTotal), 2)

FROM Sales.SalesOrderHeader

WHERE SalesPersonID IS NOT NULL

AND OrderDate BETWEEN '2003-01-01 00:00:00.000'

AND '2003-12-31 23:59:59.000'

GROUP BY SalesPersonID

cteTargetDiff (SalesPersonID, SalesQuota, QuotaDiff)

(

SELECT ts.SalesPersonID,

CASE

WHEN sp.SalesQuota IS NULL THEN 0

ELSE sp.SalesQuota

END,

CASE

WHEN sp.SalesQuota IS NULL THEN ts.NetSales

ELSE ts.NetSales - sp.SalesQuota

END

FROM cteTotalSales AS ts

INNER JOIN Sales.SalesPerson AS sp

ON ts.SalesPersonID = sp.BusinessEntityID

)

SELECT

sp.FirstName + ' ' + sp.LastName AS FullName,

sp.City,

ts.NetSales,

td.SalesQuota,

td.QuotaDiff

FROM Sales.vSalesPerson AS sp

INNER JOIN cteTotalSales AS ts

ON sp.BusinessEntityID = ts.SalesPersonID

INNER JOIN cteTargetDiff AS td

ON sp.BusinessEntityID = td.SalesPersonID

ORDER BY ts.NetSales DESC

The first CTE—cteTotalSales—is similar to the one in the preceding example, except that the WHERE clause has been further qualified to include sales only from 2003. After I define cteTotalSales, I add a comma, and then define cteTargetDiff, which calculates the difference between the sales total and the sales quota.

The new CTE definition specifies three columns for the result set: SalesPersonID, SalesQuota, and QuotaDiff. As you would expect, the CTE query returns three columns. The first is the salesperson ID. The second is the sales quota. However, because a sales quota is not defined for some salespeople I use a CASE statement. If the value is null, that value is set to 0, otherwise the actual SalesQuota value is used.

The final column returned is the difference between the net sales and sales quota. Again, I use a CASE statement. If the SalesQuota value is null, then the NetSales value is used, otherwise the sales quota is subtracted from the net sales to arrive at the difference.

Something interesting to note about the second CTE query is that I’ve joined the Sales.SalesPerson table to the first CTE—cteTotalSales—so I could calculate the difference between total sales and the sales quota. Whenever you define multiple CTEs in a single WITH clause, you can reference preceding CTEs (but not the other way around).

Once I’ve defined my CTEs, I can reference them in the first statement that follows the CTE, as you saw in the previous example. In this case, I join the Sales.vSalesPerson view to cteTotalSales and then join to cteTargetDiff, all based on the salesperson ID. My SELECT list then includes columns from all three sources. The statement returns the results shown in the following table.

As you can see, sales data is provided for all salespeople, including the city in which they reside, their net sales, their sales quota, and the calculated difference between the two figures. In this case, everyone well exceeds the quota, where a quota has been defined.

Creating a Recursive Common Table Expression

A recursive CTE is one that references itself within that CTE. The recursive CTE is useful when working with hierarchical data because the CTE continues to execute until the query returns the entire hierarchy.

A typical example of hierarchical data is a table that includes a list of employees. For each employee, the table provides a reference to that person’s manager. That reference is itself an employee ID within the same table. You can use a recursive CTE to display the hierarchy of employee data, as it would appear within the organizational chart.

Note that a CTE created incorrectly could enter an infinite loop. To prevent this, you can include the MAXRECURSION hint in the OPTION clause of the primary SELECT, INSERT, UPDATE, DELETE, or MERGE statement. For information about using query hints, see the topic “Query Hints (Transact-SQL)” in SQL Server Books Online.

To demonstrate how the recursive CTE works, I used the following Transact-SQL statements to create and populate the Employees table in the AdventureWorks2008 database:

IF OBJECT_ID('Employees', 'U') IS NOT NULL

DROP TABLE dbo.Employees

CREATE TABLE dbo.Employees

(

EmployeeID int NOT NULL PRIMARY KEY,

FirstName varchar(50) NOT NULL,

LastName varchar(50) NOT NULL,

ManagerID int NULL

)

INSERT INTO Employees VALUES (101, 'Ken', 'Sánchez', NULL)

INSERT INTO Employees VALUES (102, 'Terri', 'Duffy', 101)

INSERT INTO Employees VALUES (103, 'Roberto', 'Tamburello', 101)

INSERT INTO Employees VALUES (104, 'Rob', 'Walters', 102)

INSERT INTO Employees VALUES (105, 'Gail', 'Erickson', 102)

INSERT INTO Employees VALUES (106, 'Jossef', 'Goldberg', 103)

INSERT INTO Employees VALUES (107, 'Dylan', 'Miller', 103)

INSERT INTO Employees VALUES (108, 'Diane', 'Margheim', 105)

INSERT INTO Employees VALUES (109, 'Gigi', 'Matthew', 105)

INSERT INTO Employees VALUES (110, 'Michael', 'Raheem', 106)

As you might realize, the AdventureWorks2008 database already includes the HumanResources.Employee table. However, that table now uses the hierarchyid data type to store hierarchical data, which would introduce unnecessary complexity when trying to demonstrate a recursive CTE. For that reason, I created my own table. However, if you want to try out a recursive CTE without creating and populating a new table, you can use the AdventureWorks sample database that shipped with SQL Server 2005. The HumanResources.Employee table in that database stores the data in a way similar to the table I create above.

After I created the Employees table, I created the following SELECT statement, which is preceded by a WITH clause that includes a CTE named cteReports:

WITH

cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)

(

SELECT EmployeeID, FirstName, LastName, ManagerID, 1

FROM Employees

WHERE ManagerID IS NULL

UNION ALL

SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,

r.EmpLevel + 1

FROM Employees e

INNER JOIN cteReports r

ON e.ManagerID = r.EmpID

)

SELECT

FirstName + ' ' + LastName AS FullName,

EmpLevel,

(SELECT FirstName + ' ' + LastName FROM Employees

WHERE EmployeeID = cteReports.MgrID) AS Manager

FROM cteReports

ORDER BY EmpLevel, MgrID

As you can see, the CTE returns five columns: EmpID, FirstName, LastName, MgrID, and EmpLevel. The EmpLevel column refers to the level in the hierarchy in which the employees fit. The highest level of the hierarchy is 1, the next level is 2, followed by 3, and so on.

The CTE query is itself made up of two SELECT statements, connected with the UNION ALL operator. A recursive CTE query must contain at least two members (statements), connected by the UNION ALL, UNION, INTERSECT, or EXCEPT operator. In this example, the first SELECT statement is the anchor member, and the second statement is the recursive member. All anchor members must precede the recursive members, and only the recursive members can reference the CTE itself. In addition, all members must return the same number of columns with corresponding data types.

Now lets look closer at the statements themselves. The first statement, the anchor member, retrieves the employee ID, first name, last name, and manager ID from the Employees table, where the manager ID is null. This would be the employee at the top of the hierarchy, which means this person reports to no one. Consequently, the manager ID value is null. To reflect that this person is at the top of the hierarchy, I assign a value of 1 to the EmpLevel column.

The second statement in the CTE query—the recursive member—also retrieves the employee ID, first name, last name, and manager ID for employees in the Employees table. However, notice that I join the Employees table to the CTE itself. In addition, the join is based on the manager ID in the Employees table and the employee ID in the CTE. By doing this, the CTE will loop through the Employees table until it returns the entire hierarchy.

One other item to notice about the second statement is that, for the EmpLevel column, I add the value 1 to the EmpLevel value as it appears in the CTE. That way, each time the statement loops through the hierarchy, the next correct level is applied to the employees at the level.

After I define my WITH clause, I create a SELECT statement that retrieves the data from the CTE. Note, however, that for the Manager column, I retrieve the first and last name of the employee associated with the manager ID in the CTE. This allows me to display the full name of the manager for each employee. The following table shows the result set returned by the SELECT statement and its CTE.

As you can see, the CTE, whether recursive or nonrecursive, can be a useful tool when you need to generate temporary result sets that can be accessed in a SELECT, INSERT, UPDATE, DELETE, or MERGE statement. In a sense, a CTE is like a derived table: it’s not stored as an object and is valid only during the execution of the primary statement. However, unlike the derived table, a CTE can be referenced multiple times within a query and it can be self-referencing. And best of all, CTEs are relatively easy to implement.

Types of Indexes in SQL Server 2012

2012-03-29T15:42:00.000+05:30

The following table lists the types of indexes available in SQL Server and provides links to additional information.

Index type	Description	Additional information
Clustered	A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.	Clustered Index Design Guidelines Clustered Index Structures
Nonclustered	A nonclustered index can be defined on a table or view with a clustered index or on a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table.	Nonclustered Index Design Guidelines Nonclustered Index Structures
Unique	A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique. Uniqueness can be a property of both clustered and nonclustered indexes.	Create Unique Indexes
Columnstore	An index based on vertical partitioning of the data by columns, stored as large objects (LOB).	Columnstore Index Structures Columnstore Indexes
Index with included columns	A nonclustered index that is extended to include nonkey columns in addition to the key columns.	Create Indexes with Included Columns
Index on computed columns	An index on a column that is derived from the value of one or more other columns, or certain deterministic inputs.	Indexes on Computed Columns
Filtered	An optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.	Create Filtered Indexes
Spatial	A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometry data type. The spatial index reduces the number of objects on which relatively costly spatial operations need to be applied.	Spatial Indexing Overview
XML	A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column.	XML Indexes (SQL Server)
Full-text	A special type of token-based functional index that is built and maintained by the Microsoft Full-Text Engine for SQL Server. It provides efficient support for sophisticated word searches in character string data.	Populate Full-Text Indexes

Database Performance Tuning

2012-03-15T16:56:00.000+05:30

Why Performance Tuning?

Prevention is better than Cure: The solution to most slow data centric applications is taking care of issues proactively rather than reactively which include:
Thorough analysis of the user’s needs
Thoughtful design
Optimal coding
Appropriate implementation

How Queries are Executed

Common causes of poor SQL Server performance

Poorly written queries and database schema
Inadequate disk performance
Memory pressure leading to low page life expectancy
Long running queries (blocking and locking)

Focus: Query Optimization

Execution plans and cost-based optimizations
Optimization phases
SARG-able clauses – (This basically refers to a condition in a WHERE clause that is able to use an index if one exists)
Indices and Distribution statistics
Join Selection
Execution plan notation

Optimization Process

Performance Tuning Best Practices – DON’Ts

DO NOT use scalar functions in queries which process a large number of rows. Due to the way SQL Server processes this sort of operation, long query durations can result (although typically without heavy resource use – a common cause of slow running queries).
DO NOT use sub-queries unless it is absolutely needed. The entire sub-query result set is often evaluated, even if many of the rows are eliminated immediately by the way the sub-query is joined to the main query.
DO NOT use the DISTINCT operator unless it is absolutely necessary. In many cases duplicate rows can be eliminated through restructuring of a query – there are very few circumstances where DISTINCT is actually necessary.
DO NOT apply the ORDER BY clause to a set of data unless it is absolutely required (e.g. for front-end presentation). Never sort data into a table where the clustered index sequence differs from the ORDER BY. Sorting data is expensive, and sorting against the clustered index is pointless as SQL Server will automatically re-sequence the data upon insertion.
DO NOT use cursors.They use memory; they lock tables in weird ways, and they are slow.
DO NOT use the TEXT datatype. Unless you are using it for really large data. The TEXT datatype is not flexible to query, is slow and wastes a lot of space if used incorrectly. Sometimes a VARCHAR will handle your data better.
DO NOT use triggers to maintain data in regularly accessed tables. While useful for change-logging, firing triggers at (or from) busy tables can cause serious contention issues.

Performance Tuning Best Practices – DOs

DO prefix the identifiers of all objects with the name of their owner to ensure SQL Server immediately selects the correct context. E.g. Prefix all Stored procedures, Tables, Functions and Views owned by the database owner with “dbo”.
DO use UNION ALL when possible, instead of UNION – adding the modifier “ALL” prevents SQL Server from performing a DISTINCT operation on the data sets during merging.
DO minimize the length of any explicit transactions. The longer a transaction, and the more objects involved, the worse the impact which it will have on concurrency.
DO reference indexed columns in join predicates and where clauses. This will allow SQL Server to leverage indexes and minimize the size of the data set being processed earlier rather than later.
DO keep code as simple as possible. Several short, simple SQL statements will almost always operate more efficiently than one huge query. This also improves maintainability.

Conclusion: Query performance tuning is an important part of today’s database applications.
Often you can achieve large savings in both time and money with proper query performance tuning.

SQL Server Best Practices (DOs, DONTs)

2012-02-15T19:46:00.000+05:30

Introduction

Although every application is different, in general, the procedures which can be followed to resolve performance problems residing in or around SQL Server are generally the same.

This article suggests several approaches which can be taken to tackle such problems, although it is preferable to try and avoid them in the first place by following a series of best practices and by avoiding some common pitfalls.

Best Practices

This section lists some best practices which should be applied when writing SQL, and also to ADO/ADO.NET code interfacing with databases. It is broken up into three sections:

Vital - Critical performance issues can result where these guidelines are disregarded. Occasionally there may be situations where these rules cannot be enforced, but these must be viewed as "rare exceptions".

Important - Implementing these guidelines can help avoid or alleviate serious performance issues.

Recommended - Although not always relevant or practical, it is often worth evaluating these guidelines.

Brief justification is given for each rule, however, the underlying issues are often complex and they should not be viewed as conclusive.

Vital

DO NOT use "inline" SQL Statements – use Stored Procedures in all points of contact between the database and application layer(s). Stored Procedures have compiled and cached execution plans.
DO NOT use dynamic SQL statements. Dynamic SQL must be compiled every time it is executed, wasting resources (and Stored Procedures containing dynamic SQL will also recompile, which can cause object-level locking issues).
DO NOT use "Table Valued" functions. These will always process the entire query contained within, and generate extra overhead through table variable population – often much data is discarded upon joining to the results of a function.
DO NOT use Cursors against "permanent" tables. The locking strategies employed by cursors can cause rapid lock escalation and some severe contention issues. Use the "while loop" construct, or cursors based on temporary objects instead.
DO consider indexing strategy carefully when creating or amending schema. Poorly indexed tables are inefficient – the query optimizer will not be able to efficiently retrieve the rows that it requires.
DO include a "WHERE" clause. This is refered to as "SARGability" – Search ARGuments allow SQL Server to prune the result set at the earliest opportunity.
DO use table variables instead of standard temporary tables. Temporary tables can cause recompiles due to statistical updates and context switches, leading to problems with object-level locking. Table variables are also stored in heap memory when possible.
DO NOT drop temporary tables explicitly. SQL Server will automatically clean these up when they fall "out of scope" (e.g. execution chain has completed). An explicit DROP TABLE operation on a temporary table will cause a recompile event to occur.
DO set the "NOCOUNT" option on for all stored procedures which do not need to be aware of the number of records affected. This prevents SQL Server returning extra statistics to the client.
DO use disconnected ADO record set objects. Retaining an open record set can cause ADO to run through cursors on the SQL Server when retrieving data.
DO close all connection and record set objects as soon as they are no longer required. Retaining an unnecessary open connection can cause errors (only one record set can be open for one connection at a time in some modes of operation), and can also tie up valuable system resources.
DO NOT use the OR operator in JOIN predicates and WHERE clauses. This operator can cause poor index use (or table scans), as SQL attempts to match up and merge alternative sets of data. Consider use of UNION [ALL] if this sort of behaviour is definitely required.
DO NOT use the IN operator. Make use of "EXISTS" instead, as this allows SQL Server to look up the data in the condition clause, rather than forcing it to spool through a set of values. This is particularly relevant when the clause is a co-related sub-query.
DO NOT use distributed transactions unless there is no other option. Locking data in a local (explicit) transaction has serious enough implications, without extending the locking to another database which is physically separated from the current context.

Important

DO NOT use scalar functions in queries which process a large number of rows. Due to the way SQL Server processes this sort of operation, long query durations can result (although typically without heavy resource use – a common cause of slow running queries).
DO NOT use sub-queries. The entire sub-query result set is often evaluated, even if many of the rows are eliminated immediately by the way the sub-query is joined to the main query.
DO prefix the identifiers of all objects with the name of their owner to ensure SQL Server immediately selects the correct context. E.g. Prefix all Stored procedures, Tables, Functions and Views owned by the database owner with "dbo".
DO NOT use LEFT JOIN if possible – due to the nature of this operation, SQL Server is often unable to make efficient use of the available indexes. Consider using UNION [ALL] and two INNER JOINed queries instead.
DO NOT use the DISTINCT operator. In many cases duplicate rows can be eliminated through restructuring of a query – there are very few circumstances where DISTINCT is actually necessary.
DO use UNION ALL when possible, instead of UNION – adding the modifier "ALL" prevents SQL Server from performing a DISTINCT operation on the data sets during merging.
DO minimise the length of any explicit transactions. The longer a transaction, and the more objects involved, the worse the impact which it will have on concurrency.
DO reference indexed columns in join predicates and where clauses. This will allow SQL Server to leverage indexes and minimise the size of the data set being processed earlier rather than later.
DO NOT apply the ORDER BY clause to a set of data unless it is absolutely required (e.g. for front-end presentation). Never sort data into a table where the clustered index sequence differs from the ORDER BY. Sorting data is expensive, and sorting against the clustered index is pointless as SQL Server will automatically re-sequence the data upon insertion.
DO NOT use triggers to maintain data in regularly accessed tables. While useful for change-logging, firing triggers at (or from) busy tables can cause serious contention issues.

Recommended

DO use table variables to break up large queries, avoid repetition of work throughout a Stored Procedure and minimize contention through locking. Preparing data ahead of time can allow you to complete ACID safe work in a transaction without taking out locks on source data.
DO use locking hints to avoid lock escalation when the affected record count is known to be small, or to ensure other scenarios (dirty reads, retained locks, etc). Be careful though, as inappropriate use of locking hints can cause excessive contention (or insufficient contention, resulting in an error when data being read with NOLOCK is modified during the read operation).
DO NOT join together datasets where the clustered index contains similar columns in a different order; this can cause a bookmark lookup or heavy sort operation to occur. Remember that clustered indexes define the order of the data in a table.
DO keep code as simple as possible. Several short, simple SQL statements will almost always operate more efficiently than one huge query. This also improves maintainability.
DO NOT use RPC unless there is no other option. While useful, RPC is phenomenally expensive when compared to a normal query, because both the query execution request and result set (even if only return value or output parameters) are crossing a physical network boundary.

Introduction to SQL Server Profiler

2012-02-07T18:26:00.000+05:30

Introduction

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring T-SQL Statements of Database Engine. We can save and reuse the state at a later point of time.

We can do the following using SQL Server Profiler
- Create a trace
- Watch the trace results as the trace runs
- Store the trace results in a table
- Start, stop, pause, and modify the trace results as necessary
- Replay the trace results

Use SQL Server Profiler to monitor only the events in which you are interested.

Menu Path: Start | All Programs | Microsoft SQL Server 2005 | Performance Tools | SQL Server Profiler.
The following screen will come:

Figure 1.0

Click on <Connect> Button. New Properties Screen will come:

Figure 1.1

It has two selection tabs:

General: It is used for general setting for Trace Database Engine.
Event: It is used to add or remove some selected event for monitor.

In General Section (as given in Figure 1.1), it is divided into four sections.
Section 1: In this section, you have to just specify the name of your trace, Trace provider name and server name are predefined and based upon your SQL Server.
And it is not editable.
Section 2: It is the template section. You can choose different type of Templates based upon your requirements. It is the configuration for trace. By default, it is "Standard (Default)" templates. Others templates are T-SQL, T-SQL Duration, T-SQL Reply, T-SQL SPs, etc. You can create your own custom Templates by selecting different Events and Event Class. It is saved as ".tdf" Extension.
Section 3: This section is related to save your trace. Either as File (.trc) or in a database. as table. While clicking on Save to file check box, File save dialog box should open and you can save that file (with .trc extension).
If you check the "Save to Table", it will connect with your server and ask you to which database you want to save that trace table information.

Figure 1.2

Section 4: You can stop your trace on a particular time. Check the "Enable trace stop time" checkbox and give the time at which you want to stop track, SQL Server will automatically stop trace on that time.
Now Move To "Event Section" Tab.
Now we need to know some definition with respect to SQL Server Profiler.

What is an Event?

An Event is an action or operation that is performed in your SQL Server 2005 Database Engine.
Some examples of Events are:

Transact-SQL SELECT, INSERT, UPDATE, and DELETE statements.
User login and logout
Execution of Stored procedures
Operation with cursor

SQL Server profiler is able to trace all of these events and all these events are categories on a particular Event class.

What is an Event Class?

Event class is a type of event that can be traced.
Some examples are:

SQL: BatchCompleted
SQL: Batch Starting
Audit Login
Audit Logout
Lock: Acquired
Lock: Released

Now you can select events from this screen:

Figure 1.3

In section 1, we can select the proper check box based upon our requirement, section 2 will display the details of Selected events and Events class. If you check in the check box of section 3, you will get all the list of Events and Columns in Section 1.
Section 4 is something like customization. Just click on the "Column Filter Button". In this section, you can specify some condition (like or Not like).

Figure 1.4

By clicking on "Organize Column" button, you can change the sequence of order of selected events.
Now Click on the "Run" Button, then Trace window will come:

Figure 1.5

Using these windows, you will get the detailed time duration of a query and all other events information that you have selected.
You can save this result and use it in future. Or you can extract a particular query from the trace, just right click and click on "Extract Event Data". And save this as a SQL Script.

Reply in SQL Server Profiler

SQL Server profiler has a Reply facility which has the ability to save a trace and replay it later.
Replay is useful to troubleshoot an application. Trace replay supports debugging by using Toggle Breakpoint and the Run to Cursor options on the SQL Server Profiler Replay menu.
Anything changed in SQL Server Management Studio will be traced by the SQL Profiler. So it can basically be used for database performance check. We also have "SQL Server Performance Monitor" to monitor the System and Server performance too.

Time difference between two columns in sql server

2011-12-22T18:29:00.001+05:30

Suppose our requirement is to calculate working hours for Employee in particular company.
Below example is shows the how to create the table and update the Working hours for every employee.

USE tempdb
GO

/*****************************************
Copyright      :
Author           : Raju M
Date              : 12/22/2011
Module         :
Desc          : Script to Create EmpTime table.

Change History :
Name        Date        Description

SELECT * FROM EmpTime

******************************************/

IF NOT EXISTS(SELECT * FROM sys.objects WHERE TYPE= 'U' AND Name = 'EmpTime')
BEGIN
    /*    Creating the EmpTime Table    */
    CREATE TABLE EmpTime
    (
        EmployeeID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
        StartingTime DATETIME NOT NULL,
        FinishingTime DATETIME NOT NULL,
        DeductBreakMM BIGINT NOT NULL,
        WorkingTime TIME(3) NULL
    )

    /*    Inserting the Data into EmpTime Table    */
    INSERT INTO EmpTime
        (
            StartingTime,
            FinishingTime,
            DeductBreakMM
        )

    SELECT '2011-12-22 09:58:07.943' AS StartingTime,
           '2011-12-22 19:18:07.003' AS FinishingTime,
           45 DeductBreakMM

    UNION ALL

    SELECT '2011-12-22 08:28:07.943' AS StartingTime,
           '2011-12-22 21:18:07.003' AS FinishingTime,
           51 DeductBreakMM

    /*    Updating the EmpTime Column in EmpTime Table    */
    UPDATE EmpTime
    SET WorkingTime = CAST(DATEADD(SS,(DATEDIFF(SS,StartingTime, FinishingTime)) - (DeductBreakMM*60),0) AS TIME(3))

END
GO

SQL Server 2012 (Denali)

2011-12-21T13:13:00.000+05:30

New Features in SQL Server 2012 - RC0 Released

Microsoft SQL Server 2012 RC0 was recently released. RC stands for Release Candidate which is basically the version virtually production ready. Microsoft referred to this release as SQL Server Code Named "Denali" but has settled on SQL Server 2012 as the final name for the product.. Business intelligence (BI) is critically important to organizations both large and small. In the latest release of SQL Server, there is no shortage of BI enhancements in addition to many other enhancements. This article will give you a preview of the requirements, new features and enhancements in SQL Server 2012 (code named Denali) including:

Hardware and Software Requirements
Multi-Subnet Failover Clustering
Programming Enhancements, including sequences, ad-hoc query paging and full-text search tweaks
BI and Web Development Environment Improvements
Web-based Visualization
Data Quality Services

Keep in mind that this information is for preview only and is subject to change by Microsoft.

Hardware and Software Requirements

Microsoft recommends using NTFS file format instead of FAT32. FAT32 will work but you should probably not use it.
You can't install SQL Server 2012 (code-named Denali) on mapped drives or compressed drives.
You have to have the "no-reboot" package installed prior to installing SQL Server 2012 (code-named Denali). This is included in Windows 7 SP1 and Windows Server 2008 R2. Otherwise, you can download the no-reboot package from Microsoft.
SQL Server 2012 (code-named Denali) requires the .NET Framework 4.0.
Virtualization is supported using Microsoft's Hyper-V technology.
You will need at least 3.6 GB of free disk space.
Microsoft recommends that you do not install SQL Server 2012 (code-named Denali) on a domain controller.
Recommended Processors & RAM
- 64-bit version: AMD Opteron, AMD Athlin 64, Intel Xeon with Intel EM64T Support or Intel Pentium IV with EM64T support running 2.0 GHz or faster. Recommended RAM is maximum that operating system supports or at least 2 GB.
- 32-bit version: Pentium III or compatible running at 2.0 GHz of faster. Recommended RAM is maximum that operating system supports or at least 2 GB.
Windows PowerShell 2.0 is a pre-requisite for installing SQL Server 2012 (code-named Denali). You can get this software from the Windows Management Framework page.
Check out the step by step installation guide with screenshots to get a preview of the SQL Server 2012 install and configuration process.

Multi-Subnet Failover Clustering

With SQL Server 2012 (code-named Denali), you can configure SQL Server where failover cluster nodes can be connected to a completely different subnet. The subnets can be spread out to different geographical locations providing disaster recovery along with high availability. In order for this to work correctly, you will need to replicate the data across the databases involved in this configuration. The SQL Server failover cluster is dependent on the Windows Server failover cluster so this has to be set up first. Keep in mind that all of the subnets involved in this configuration must be in the same Active Directory domain.

Programming Enhancements

Sequences: Sequences have been requested by the SQL Server community for years, and it's included in this release. Sequence is a user defined object that generates a sequence of a number. Here is an example using Sequence.
/****** Create Sequence Object ******/
CREATE SEQUENCE MySequence
START WITH 1
INCREMENT BY 1;
/****** Create Temp Table ******/
DECLARE @Person TABLE
(
ID int NOT NULL PRIMARY KEY,
FullName nvarchar(100) NOT NULL
);
/****** Insert Some Data ******/
INSERT @Person (ID, FullName)
VALUES (NEXT VALUE FOR MySequence, 'Jim Johnson'),
(NEXT VALUE FOR MySequence, 'Bob Thompson'),
(NEXT VALUE FOR MySequence, 'Tim Perdue');
/****** Show the Data ******/
SELECT * FROM @Person;
The results would look like this:
ID FullName
1 Jim Johnson
2 Bob Thompson
3 Tim Perdue
- Things a Developer Needs to Know About Sequences in SQL Server 2012 (code-named Denali)
Ad-Hoc Query Paging: Paging results in SQL Server has been discussed for years. The Order By option in the SQL SELECT statement has been enhanced in SQL Server 2012. Using a combination of OFFSET and FETCH along with ORDER BY gives you control of paging through a result set. Using this technique can really help performance by bring back only the results you want to show to your users when they are needed. The following TSQL code runs against the Person table in the AdventureWorks sample database (available from Microsoft). In the sample query below, SQL Server would return 10 records beginning with record 11. The OFFSET command provides a starting point for the SELECT statement in terms of paging, and the FETCH command provides how many records to return at a time.
SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
Full Text Search: The Full Text Search in SQL Server 2012 has been enhanced by allowing you to search and index data stored in extended properties or metadata. Consider a PDF document that has "properties" filled in like Name, Type, Folder path, Size, Date Created, etc. In the newest release of SQL Server, this data could be indexes and searched along with the data in the document itself. The data does have to be exposed to work, but it's possible now.

BI and Web Development Environment Improvements
Microsoft moved BI (Business Intelligence) closer to the end user with SQL Server 2008 R2. The Excel PowerPivot tool help users by creating a self-service reporting model. To understand the impact of SQL Server 2008 R2, check out the About.com article Why SQL Server 2008 R2 Matters to Small Business. The good news is PowerPivot is being enhanced in SQL Server 2012 (code-named Denali). Microsoft is adding KPIs and drill through, which will be really useful for all users.
Analysis Services will include a new BI Semantic Model (BISM). BISM is a 3-layer model that includes:

Data Model
Business Logic
Data Access

BISM will enhance Microsoft's front end analysis experiencing including Excel, Reporting Services and SharePoint Insights. Microsoft has said that BISM is not a replacement for the current BI Models but more of an alternative model. In simple terms, BISM is a relation model that includes BI artifact such as KPIs and hierarchies.
Web Based Visualization - Project Crescent
Project Crescent is the Microsoft code name for the new reporting and visualization tool expected in SQL Server 2012 (code-named Denali). Project Crescent provides drag and drop ad-hoc reporting functionality and was built entirely on Silverlight. It includes a powerful query tool and interactive storyboarding to allow a user to share visualizations of large datasets.

Read my article Project Crescent Reporting and Visualization in SQL Server 2012 (code-named Denali) for more on Crescent.

Data Quality Services
Data Quality Services is a knowledge-based approach that runs in SSIS (SQL Services Integration Services). Data quality is one of those things that you never get perfect. Microsoft is introducing "Impact Analysis and Lineage" which will give you information about what your data depends on. It also shows the lineage of the data, including where it comes from and the systems that are behind it.

SQL Server 2012 Resources (SQL Server 2011/Denali)

SQL Server 2011 Downloads

How to Encrypt/Decrypt the passward in SQL Server..?

2011-12-16T17:25:00.000+05:30

DECLARE @Name VARCHAR(100) = 'RAJU'
DECLARE @EncName VARCHAR(500)

SELECT EncryptByPassphrase('A',@Name) EncryptByPassphrase

OutPut
======
0x010000001AB718D8402648B2CCD2C46DACFCDF7844ADD7243B8C9ECA

SELECT @EncName = EncryptByPassphrase('A',@Name)

SELECT CAST(DecryptByPassphrase('A', @EncName) AS VARCHAR(100)) DecryptByPassphrase

OutPut
======
RAJU

SCRIPT TO CHECK QUERY

2011-12-14T14:41:00.000+05:30

Below query to check the current running SQL statement for a Stored Procedure.

All you need to do is, give the ‘SPID’ of the session in which the Stored Procedure is running.

SELECT SDER.[session_id], SDER.[request_id],SDER.[statement_start_offset],
SDER.[statement_end_offset],
CASE
     WHEN SDER.[statement_start_offset] > 0 THEN
        --The start of the active command is not at the beginning of the full command text
        CASE SDER.[statement_end_offset]
           WHEN -1 THEN
              --The end of the full command is also the end of the active statement
              SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, 2147483647)
           ELSE
              --The end of the active statement is not at the end of the full command
              SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, (SDER.[statement_end_offset] - SDER.[statement_start_offset])/2)
        END
     ELSE
        --1st part of full command is running
        CASE SDER.[statement_end_offset]
           WHEN -1 THEN
              --The end of the full command is also the end of the active statement
              RTRIM(LTRIM(DEST.[text]))
           ELSE
              --The end of the active statement is not at the end of the full command
              LEFT(DEST.TEXT, (SDER.[statement_end_offset]/2) +1)
        END
     END AS [executing statement],
DEST.[text] AS [full statement code]
FROM sys.[dm_exec_requests] SDER CROSS APPLY sys.[dm_exec_sql_text](SDER.[sql_handle]) DEST
WHERE SDER.session_id = 65
ORDER BY SDER.[session_id], SDER.[request_id]

INTRODUCTION TO SQL SERVER

2011-10-12T16:51:00.000+05:30

SQL Server is a client-server Relational Database Management System developed by Microsoft Corporation. A client-server RDBMS is an RDBMS that can be shared. SQL Server is the RDBMS chosen as back end by most of the developers on Windows operating system. This is because

It is tightly integrated with Windows
It is easy to use as it provides wizards to perform various tasks like object creation, database tuning, backup etc.,
It scales from mobile laptop to symmetric multi-processor systems.
It provides business intelligence concepts, which are until now available only in oracle and other expensive DBMS’s.
It is a bundle of at least four products Relational Database Engine, Analysis Services, Reporting Services and Integration Services. Relational Database Engine is used to work with database and database objects, analysis service is used to work with business intelligence concepts like data warehousing and data mining. Reporting Services is used to create and deploy enterprise reports and Integration Services is used to integrate data from different types of data sources to SQL Server.

History Of SQL Server

SQL Server was developed and implemented by Sybase Corporation. In 1988, Microsoft licensed SQL Server for OS/2 operating system and began developing it for windows in early 1990. At almost the same time, further development of SQL Server for OS/2 has been cancelled. In 1994, Microsoft cancels the agreement with Sybase and starts developing SQL Server for windows. Since then several versions of SQL Server are released and the new version in the market is SQL Server 2005.

10 reasons why SQL Server 2008 is going to rock

2011-09-16T19:04:00.000+05:30

10. Plug-in model for SSMS. SSMS 2005 also had a plug-in model, but it was not published, so the few developers that braved that environment were flying blind. Apparently for 2008, the plug-in model will be published and a thousand add-ins will bloom.

9. Inline variable assignment. I often wondered why, as a language, SQL languishes behind the times. I mean, it has barely any modern syntactic sugar. Well, in this version, they are at least scratching the the tip of the iceberg.

Instead of:

DECLARE @myVar int

SET @myVar = 5

you can do it in one line:

DECLARE @myVar int = 5

Sweet.

8. C like math syntax. SET @i += 5. Enough said. They finally let a C# developer on the SQL team.

7. Auditing. It's a 10 dollar word for storing changes to your data for later review, debugging or in response to regulatory laws. It's a thankless and a mundane task and no one is ever excited by the prospect of writing triggers to handle it. SQL Server 2008 introduces automatic auditing, so we can now check one thing off our to do list.

6. Compression. You may think that this feature is a waste of time, but it's not what it sounds like. The release will offer row-level and page-level compression. The compression mostly takes place on the metadata. For instance, page compression will store common data for affected rows in a single place.

The metadata storage for variable length fields is going to be completely crazy: they are pushing things into bits (instead of bytes). For instance, length of the varchar will be stored in 3 bits.

Anyway, I don't really care about space savings - storage is cheap. What I do care about is that the feature promised (key word here "promises") to reduce I/O and RAM utilization, while increasing CPU utilization. Every single performance problem I ever dealt with had to do with I/O overloading. Will see how this plays out. I am skeptical until I see some real world production benchmarks.

5. Filtered Indexes. This is another feature that sounds great - will have to see how it plays out. Anyway, it allows you to create an index while specifying what rows are not to be in the index. For example, index all rows where Status != null. Theoretically, it'll get rid of all the dead weight in the index, allowing for faster queries.

4. Resource governor. All I can say is FINALLY. Sybase has had it since version 12 (that's last millennium, people). Basically it allows the DBA to specify how much resources (e.g. CPU/RAM) each user is entitled to. At the very least, it'll prevent people, with sparse SQL knowledge from shooting off a query with a Cartesian product and bringing down the box.

Actually Sybase is still ahead of MS on this feature. Its ASE server allows you to prioritize one user over another - a feature that I found immensely useful.

3. Plan freezing. This is a solution to my personal pet peeve. Sometimes SQL Server decides to change its plan on you (in response to data changes, etc...). If you've achieved your optimal query plan, now you can stick with it. Yeah, I know, hints are evil, but there are situations when you want to take a hammer to SQL Server - well, this is the chill pill.

2. Processing of delimited strings. This is awesome and I could have used this feature...well, always. Currently, we pass in delimited strings in the following manner:

exec sp_MySproc 'murphy,35;galen,31;samuels,27;colton,42'

Then the stored proc needs to parse the string into a usable form - a mindless task.

In 2008, Microsoft introduced Table Value Parameters (TVP).

CREATE TYPE PeepsType AS TABLE (Name varchar(20), Age int)

DECLARE @myPeeps PeepsType

INSERT @myPeeps SELECT 'murphy', 35

INSERT @myPeeps SELECT 'galen', 31

INSERT @myPeeps SELECT 'samuels', 27

INSERT @myPeeps SELECT 'colton', 42

exec sp_MySproc2 @myPeeps

And the sproc would look like this:

CREATE PROCEDURE sp_MySproc2(@myPeeps PeepsType READONLY) ...

The advantage here is that you can treat the Table Type as a regular table, use it in joins, etc. Say goodbye to all those string parsing routines.

1. Intellisense in the SQL Server Management Studio (SSMS). This has been previously possible in SQL Server 2000 and 2005 with use of 3rd party add-ins like SQL Prompt ($195). But these tools are a horrible hack at best (e.g. they hook into the editor window and try to interpret what the application is doing).

Built-in intellisense is huge - it means new people can easily learn the database schema as they go.

Built In Functions

2011-09-16T17:26:00.004+05:30

Built In Functions

As in other languages like C and C++, SQL also provides Built in functions. Built in functions in SQL are classified into following two categories.

1. Scalar Functions

2. Aggregate Functions

3. Ranking Functions

I. Scalar Functions : The built in functions in SQL Server that work on a single value are called as scalar functions. Scalar functions are again classified into following categories based on data type on which they work.

a) Numeric Functions

b) String Functions

c) Date Functions

d) Conversion Functions

e) System Functions

a) Numeric Functions : The scalar functions of SQL that work on numeric type of data are called as numeric functions. Numeric functions in SQL are as follows.

Abs(n) : Returns absolute value of the given n i.e. n value without any sign.
Cos(n) : Returns cosine value of given n in radians.
Ceiling(n) : Returns smallest integer greater than or equal to n.
Floor(n) : Returns Largest integer less than or equal to n.
Radians(n) : Converts the given n in degrees to radians.
Degrees(n) : Converts the given n in radians to degrees.
Exp(n) : Returns e raised to the power of n. e is exponential whose value is constant and is 2.713
Log(n) : Returns natural logarithm of n. i.e. base e logarithm.
Log10(n) : Returns base 10 logarithm of n.
Power(m,n) : Returns m raised to the power of n.
Square(n) : Returns Square of given n.

Sqrt(n) : Returns Square Root of given n.
Sin(n) : Returns sine value of given n in radians.
Tan(n) : Returns tangent value of given n in radians.
Sign(n) : Return -1 if n is negative, 1 if n is positive and 0 if n is zero.
Pi() : Return mathematical constant pi value i.e. 3.14
Round(n,p,t) : Rounds the given in n to p decimal places. If t is specified as 1 then it truncates n to p decimal places instead of round the value.

b) String Functions : The built-in functions of SQL that work on string type of data are called as string functions. String functions in SQL are as follows.

ASCII(char) : Converts the specified character to the equivalent ascii code.
Char(int) : Converts ascii code to the equivalent character.
CharIndex(s1,s2) : returns the starting position where the partial string s1 first occurs in the string s2. Returns zero if s1 does not occur in s2.
Lower(S1) : Converts all uppercase letters of the string s1 to lower case letters.
Upper(s1) : Converts all lowercase letters of the string s1 to upper case letters.
Ltrim(s1) : removes leading blank spaces in the string s1.
Rtrim(S1) : Removes trailing blank spaces in the string s1.
Reverse(S1) : Reverses the string S1.
Right(S1,Len) : returns last Len characters from the string S1.
Left(S1,Len) : returns first len characters from the string S1.
Soundex(a) : Returns how the given string is pronounced in a four character soundex code.
Stuff(s1,a,len,s2) : Replaces the partial string s1 with the partial string s2 starting at position a, replacing len characters of s1.
Substring(S1,a,len) : creates a partial string from string s1 starting at the position a with a length of len.

c) Date Functions : The built-in functions of SQL that work on date type of data are called as date functions and date functions in SQL server are as follows.

GetDate() : Returns the current system date and time.
DatePart(item,D) : returns the specified part item of a date D as an integer.
DateName(item,D) : returns the specified part item of the date D as a string.
DateDiff(item,D1,D2) : calculates the difference between the two date parts D1 and D2 and returns the result as an integer in units specified by the value item.
DateAdd(item,n,D) : Adds the number n of units specified by the value item to the given date D.

d) Conversion Functions : The Built-In functions of SQL Server that are used to convert one type of data to another are called as conversion functions and conversion functions in SQL Server are as follows.

Cast(a as type[(length)]) : converts an expression a into the specified data type type.
Convert(type[(length}],a) : Equivalent to cast, but the arguments are specified differently.

e) System Functions : The SQL Server system functions provide extensive information about database objects.

Object_ID(objname) : returns the identifier of the database object objname.
Object_name(obj_id) : returns the name of the database object with id obj_id.
Col_Name(Obj_Id,S_Id) : returns the name of the column belonging to the object obj_id with the identifier s_id.
Col_Length(obj,Col) : returns the length of the column belonging to a database object obj.
Datalength(s1) : calculates the length of the result of the expression s1.
DB_Id(Db_name) : returns the id of the database db_name.
DB_Name(Db_id) : returns the name of the database with id Db_id.
User_Id(username) : returns the id of specified user name.
user_name(uid) : returns the name of the user with id uid.
Current_TimeStamp : returns the current date and time.

II. Ranking functions

1. Row_number: Displays row numbers

Select row_number() over (order by orderdate desc) as rownum,

Salesorder_id,customer_id,orderdate

From Sales.SalesOrderHeader

Order by OrderDate Desc

Select row_number() over (order by orderdate desc) as rownum,

Salesorder_id,customer_id,orderdate

From Sales.SalesOrderHeader

Where rownum between 101 and 120

Order by OrderDate Desc

Create proc info1 @a int, @b int

Select * from (select row_number() over (order by orderdate desc) as rownum,

Salesorderid,customerid,orderdate,from sales.salesorderheader) 0

Where rownum between @a and @a+@b-1

Order by orderdate desc

Other Ranking functions

Select row_number() over (order by city) as rownum,

Rank() over( order by city) as rank,

Dense_Rank() over (order by city as DenseRank,

Ntile(3) over( order by city) as ntile_3,

Ntile(4) over(order by city) as ntile_4,

City, stateprovincecode

From person.address as address

Inner join person.stateprovince as stateprov

On address.stateprovinceid=stateprov.stateprovinceID where countryregioncode=‘US’ and StateprovinceCode=‘AZ’

Cloud Computing

2011-09-08T20:36:00.002+05:30

1 Cloud Computing Deployment Models

2 What does a Shift Towards Cloud Computing Mean?

3 References

WIKI Cloud computing is a technology that uses the internet and central remote servers to maintain data and applications. Cloud computing allows consumers and businesses to use applications without installation and access their personal files at any computer with internet access. This technology allows for much more efficient computing by centralizing storage, memory, processing and bandwidth.

A simple example of cloud computing is Yahoo email or Gmail etc. You dont need a software or a server to use them. All a consumer would need is just an internet connection and you can start sending emails. The server and email management software is all on the cloud ( internet) and is totally managed by the cloud service provider Yahoo , Google etc. The consumer gets to use the software alone and enjoy the benefits. The analogy is , 'If you only need milk , would you buy a cow ?' All the users or consumers need is to get the benefits of using the software or hardware of the computer like sending emails etc. Just to get this benefit (milk) why should a consumer buy a (cow) software /hardware ?

Cloud computing is broken down into three segments: "applications," "platforms," and "infrastructure." Each segment serves a different purpose and offers different products for businesses and individuals around the world. In June 2009, a study conducted by VersionOne found that 41% of senior IT professionals actually don't know what cloud computing is and two-thirds of senior finance professionals are confused by the concept,^[1] highlighting the young nature of the technology. In Sept 2009, an Aberdeen Group study found that disciplined companies achieved on average an 18% reduction in their IT budget from cloud computing and a 16% reduction in data center power costs.^[2]

Cloud Computing Deployment Models

Public Cloud

Public cloud or external cloud describes cloud computing in the traditional mainstream sense, whereby resources are dynamically provisioned on a fine-grained, self-service basis over the Internet, via web applications/web services, from an off-site third-party provider who bills on a fine-grained utility computing basis. In simple, For use by multiple organizations on a shared basis and hosted and managed by a third party service provider.it is very useful. Its free of cost.

Talk to a broker of cloud solutions, Get independent advice here: http://www.dcx.com.au

Community Cloud

A community cloud may be established where several organizations have similar requirements and seek to share infrastructure so as to realize some of the benefits of cloud computing. The costs are spread over fewer users than a public cloud (but more than a single tenant). This option offers a higher level of privacy, security, and/or policy compliance. In addition, it can be economically attractive as the resources (storage, workstations) utilized and shared in the community are already exploited and have reached their return of investment. Examples of community clouds include Google's Web App hosting service.

It was dark when I woke. This is a ray of sunshine.

Um, are you really just giving this info out for nothing?

What does a Shift Towards Cloud Computing Mean?

A paradigm shift to cloud computing will affect many different sub-categories in computer industry such as software companies, internet service providers (ISPs) and hardware manufacturers. While it is relatively easy to see how the main software and internet companies will be affected by such a shift, it is more difficult to predict how companies in the internet and hardware sectors will be affected.

Who Gains?

Consulting/Software/Hardware and Services companies that could gain from a shift towards cloud computing include:

Software Producers that could gain from a shift towards cloud computing include:

Internet-based companies that could gain from a shift towards cloud computing include:

Internet Infrastructure providers that could gain from a shift towards cloud computing include:

Consulting companies that could gain from a shift towards cloud computing include:

Healthcare providers that have embarked their solutions onto the clouds include:

eKlinik Healthcare Cloud Computing

Who Loses Out?

Traditional software producers that could have some catching up to do if cloud computing ultimately wins out include:

SQL World

TRIGGERS

Populating a Hierarchical Table Using Hierarchical Methods in SQL Server 2012

Normalization in SQL Server

ON UPDATE CASCADE and ON DELETE CASCADE in SQL Server

How to Add a Linked Server

Adding a Linked server can be done by either using the GUI interface or the sp_addlinkedserver command.

Adding a linked Server using the GUI

Execute Keyword in SQL Server 2012

Magic of Derived Tables

Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database

SQL Tuning/SQL Optimization Techniques:

Table Variables Vs Temporary Tables

Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT

Magic of the OUTPUT Clause with INSERT, UPDATE, DELETE

Working with CTE (Common Table Expressions)

Types of Indexes in SQL Server 2012

Database Performance Tuning

SQL Server Best Practices (DOs, DONTs)

Introduction to SQL Server Profiler

Introduction

What is an Event?

What is an Event Class?

Reply in SQL Server Profiler

Time difference between two columns in sql server

SQL Server 2012 (Denali)

Hardware and Software Requirements

Multi-Subnet Failover Clustering

Programming Enhancements

SQL Server 2012 Resources (SQL Server 2011/Denali)

SQL Server 2011 Downloads

How to Encrypt/Decrypt the passward in SQL Server..?

SCRIPT TO CHECK QUERY

INTRODUCTION TO SQL SERVER

10 reasons why SQL Server 2008 is going to rock

Built In Functions

Cloud Computing

Contents

Cloud Computing Deployment Models

Public Cloud

Community Cloud

What does a Shift Towards Cloud Computing Mean?

Who Gains?

Who Loses Out?