Using isnull in where clause is expensive in SQL Server

The statement was similar to this :
(This is just an example)

Select * from CustomerMaster where StateId = isnull(@StateID, [StateId]);

After replacing isnull with ‘Case’  like below, the statement executed faster and the stored procedure ran in few milli seconds.

Select * from CustomerMaster where StateId = (Case @StateID when null then [StateId] else @StateID End);

How many Clustered Index can be created on a single table?

Only one

How many Non Clustered Index can be created on a single table?

So the correct answer is you can have upto 999 non clustered index per table from SQL Server 2008 onwards but in pervious versions this was limited to 249.

Explain different constraints to maintain data integrity in SQL Server?

Check constraints:
Check constraints will be useful to limit the range of possible values in a column.
We could create check constraints at two different levels
a) Column-level check constraints are applied only to the column and cannot reference data in another other column
b) Table-level check constraints can reference any column within a table but cannot reference columns in other tables
Default constraints:
Default constraints enable the SQL Server to write default value to a column when user doesn’t specify a value.
Unique constraints:
A unique constraint restricts a column or combination of columns from allowing duplicate values.
Primary key constraints:
Primary key constraints will allow a row to be uniquely identified. This will perform by primary key on the table.
Foreign key constraints:
Foreign keys constraints will ensure that the values that can be entered in a particular column exist in a specified table.

Indexes in SQL Server

What is Index?
Indexes are database objects designed to improve query performance.
By applying indexes to one or more columns in table or views, we could see faster data retrieval from these tables.
Explain the structure of Index in SQL server?
An index is structured by the SQL Server Index Manager as a balanced tree (or Btree). A B-tree is similar to an upside-down tree, means with the root of the tree at the top, the leaf levels at the bottom, and intermediate levels in between.
Each object in the tree structure is a group of sorted index keys called an index page.
All search requests begin at the root of a B-tree and then move through the tree to the appropriate leaf level.
What are the different types of indexes in SQL Server?
There are two types of indexes
• Clustered index
• Non Clustered index
Both types of indexes are indexes are structured as B-Trees.
Explain the difference between clustered index and non clustered index?
Clustered index:

• A clustered index contains table records in the leaf level of the B-tree.
• There can be only one clustered index on a table or view, because the clustered index key physically sorts the table or view.

Non Clustered index:

• A non clustered index contains a bookmark to the table records in the leaf level.
• If a clustered index exists on a table, a non clustered index uses it to facilitate data lookup.
• We could create 249 non clustered indexes on a single table.

SQL SERVER – Example of DDL, DML, DCL and TCL Commands

DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

SELECT – Retrieves data from a table
INSERT -  Inserts data into a table
UPDATE – Updates existing data into a table
DELETE – Deletes all records from a table

DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

CREATE – Creates objects in the database
ALTER – Alters objects of the database
DROP – Deletes objects of the database
TRUNCATE – Deletes all records from a table and resets table identity to initial value.

DCL

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

GRANT – Gives user’s access privileges to database
REVOKE – Withdraws user’s access privileges to database given with the GRANT command

TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

COMMIT – Saves work done in transactions
ROLLBACK – Restores database to original state since the last COMMIT command in transactions
SAVE TRANSACTION – Sets a savepoint within a transaction

How to get Dropdown Selected value using javascript in ASP.NET?

HTML

So first add an aspx page into your project & paste the below code for asp:dropdownlist under the form div section:

<asp:DropDownList ID="DropDownList1" runat="server" OnChange="javascript:GetDropDownValue()">

<asp:ListItem Value="1">C#</asp:ListItem>

<asp:ListItem Value="2">ASP</asp:ListItem>

<asp:ListItem Value="3">WPF</asp:ListItem>

<asp:ListItem Value="4">WCF</asp:ListItem>

<asp:ListItem Value="5">C++</asp:ListItem>

asp:DropDownList>

Javascript

Add javascript function in head tag

function GetDropDownValue()

{

var IndexValue = document.getElementById('<%=DropDownList1.ClientID %>').selectedIndex;

var SelectedVal = document.getElementById('<%=DropDownList1.ClientID %>').options[IndexValue].text;

alert(SelectedVal);

}

Output

What is Cursor with examples in SQL Server 2008?

What is cursor?

A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.

To use cursors in SQL procedures, you need to do the following:

1. Declare a cursor that defines a result set.
2. Open the cursor to establish the result set.
3. Fetch the data into local variables as needed from the cursor, one row at a time.
4. Close the cursor when done

To work with cursors you must use the following SQL statements:

• DECLARE CURSOR
• OPEN CURSOR
• FETCH ROW By ROW
• CLOSE CURSOR

Example

Create the table of Employee

CREATE TABLE Employee

(

EID INT PRIMARY KEY IDENTITY,

ENAME VARCHAR(50),

SALARY DECIMAL(10,2),

DEPT VARCHAR(50)

)

INSERT INTO Employee(ENAME,SALARY,DEPT)VALUES('ABC',2000.00,'HR')

INSERT INTO Employee(ENAME,SALARY,DEPT)VALUES('XYZ',4000.00,'SUPPORT')

INSERT INTO Employee(ENAME,SALARY,DEPT)VALUES('DEF',6000.00,'SUPPORT')

INSERT INTO Employee(ENAME,SALARY,DEPT)VALUES('PQR',1000.00,'HR')

INSERT INTO Employee(ENAME,SALARY,DEPT)VALUES('MNL',7000.00,'MARKETING')

INSERT INTO Employee(ENAME,SALARY,DEPT)VALUES('OPQ',6000.00,'HR')

INSERT INTO Employee(ENAME,SALARY,DEPT)VALUES('RST',9000.00,'ACCOUNT')

SELECT * FROM Employee

Now we are updating salary 20% row by row

DECLARE @EID VARCHAR(50)

DECLARE db_cursor CURSOR FOR

SELECT EID FROM Employee

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @EID

WHILE @@FETCH_STATUS = 0

BEGIN

UPDATE Employee SET SALARY=SALARY+(SALARY*0.20)

WHERE EID=@EID

FETCH NEXT FROM db_cursor INTO @EID

END

CLOSE db_cursor

DEALLOCATE db_cursor

Result

Currency Format with comma's in SQL Server-2008

Sometimes you want to have your money fields properly formatted with commas like this: 10,000,000.34

DECLARE @amount AS DECIMAL

SET @amount=11000000

SELECT CONVERT(VARCHAR,CAST(@amount AS MONEY),-1) AS 'Amount'

Output

Difference Temporary Table and Table Variable –SQL SERVER?

Temporary Table	Table Variable
create table #T (…)	declare @T table (…)
Temporary Tables are real tables so you can do things like CREATE INDEX,	Table variable is not real table but you can have indexes by using PRIMARY KEY or UNIQUE constraints.
CREATE TABLE statement. SELECT INTO statement.	DECLARE statement  Only
Maximum 116 characters.	Maximum 128 characters
Temp tables might result in stored procedures being recompiled,	Table variables will not.
#temp_tables are created explicitly when the TSQL CREATE TABLE statement is encountered and can be dropped explicitly with DROP TABLE or will be dropped implicitly when the batch ends.	@table_variables are created implicitly when a batch containing a DECLARE @.. TABLE statement is executed (before any user code in that batch runs) and are dropped implicitly at the end.
User-defined data types and XML collections must be in tempdb to use	Can use user-defined data types and XML collections.
Explicitly with DROP TABLE statement. Automatically when session ends. (Global: also when other sessions have no statements using table.)	Automatically at the end of the batch.
Last for the length of the transaction. Uses more than table variables.	Last only for length of update against the table variable. Uses less than temporary tables.
Creating temp table and data inserts cause procedure recompilations.	Stored procedure recompilations Not applicable.
Data is rolled back	Data not rolled back
Optimizer can create statistics on columns. Uses actual row count for generation execution plan.	Optimizer cannot create any statistics on columns, so it treats table variable has having 1 record when creating execution plans.
The SET IDENTITY_INSERT statement is supported.	The SET IDENTITY_INSERT statement is not supported.
INSERT statement, including INSERT/EXEC. SELECT INTO statement.	INSERT statement (SQL 2000: cannot use INSERT/EXEC).
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.	PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.
Indexes can be added after the table has been created.	Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.

Example

CREATE TABLE #Temp

(

          Col1 INT IDENTITY,

          Col2 VARCHAR(100)

)

DECLARE @Temp TABLE

(

          Col1 INT IDENTITY,

          Col2 VARCHAR(100)

)

INSERT INTO #Temp(Col2) select 'Temp Table'

INSERT INTO @Temp(Col2) select 'Table Variable'

SELECT * FROM #Temp

SELECT * FROM @Temp

DROP TABLE #Temp

Difference between union and union all in SQL-Server- 2008?

UNION 

·         UNION is used to select distinct values from two tables.

·         Union are slow as compare to Union ALL

·         UNION similar to JOIN command.

·         When using the UNION command all selected columns need to be of the same data type.

Example

SELECT 'TEST'

      UNION

SELECT 'TEST'

RESULT:
 TEST


UNION ALL

·         UNION ALL will not eliminate duplicate rows; instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

·         UNION ALL is faster

Example

SELECT 'TEST'

      UNION ALL

SELECT 'TEST'

RESULT:
TEST
TEST

To remove or replace multiple special character from string using sql queries.

CREATE FUNCTION [FN_REMOVE_SPECIAL_CHARACTER] (  
 @INPUT_STRING varchar(300))
RETURNS VARCHAR(300)
AS 
BEGIN
 
--declare @testString varchar(100),
DECLARE @NEWSTRING VARCHAR(100) 
-- set @teststring = '@san?poojari(darsh)'
 SET @NEWSTRING = @INPUT_STRING ; 
With SPECIAL_CHARACTER as
(
SELECT '>' as item
UNION ALL 
SELECT '<' as item
UNION ALL 
SELECT '(' as item
UNION ALL 
SELECT ')' as item
UNION ALL 
SELECT '!' as item
UNION ALL 
SELECT '?' as item
UNION ALL 
SELECT '@' as item
UNION ALL 
SELECT '*' as item
UNION ALL 
SELECT '%' as item
UNION ALL 
SELECT '$' as item
 )
SELECT @NEWSTRING = Replace(@NEWSTRING, ITEM, '') FROM SPECIAL_CHARACTER  
return @NEWSTRING 
END 

select dbo.[FN_REMOVE_SPECIAL_CHARACTER] ('@s()antosh')

CREATE FUNCTION [dbo].[udfGetCharacters](@inputString VARCHAR(MAX), @validChars VARCHAR(100))
RETURNS VARCHAR(500) AS
BEGIN
 
 WHILE @inputString like '%[^' + @validChars + ']%'
  SELECT @inputString = REPLACE(@inputString,SUBSTRING(@inputString,PATINDEX('%[^' + @validChars + ']%',@inputString),1),'')

 RETURN @inputString
END

--Usage of the function
select [dbo].udfGetCharacters('utkarsh puranik`s blog' ,'0-9a-z ')

--output
utkarsh puraniks blog 

Send SMTP Email using SQL Server

Many times it is needed to send a email from the database. The important reason is that you do not

need to pull the data in front end and then send emails from front end.

Also if the database server and application server are separate, it takes of the load from the application server.

Collaboration Data Objects (CDO)

 

For sending emails through SMTP Server I will be using Collaboration Data Objects (CDO).

CDO are part of Windows and are useful in sending SMTP Emails.

For more information on CDO Read here.

In SQL Server 2000, I’ll create a stored procedure that will be used to send emails using CDO.

I’ll explain how to send emails using GMAIL SMTP Server.

Here I have created a stored procedure sp_send_cdosysmail which accepts the following parameters

 

Parameter	Relevance
@from	Email Address of the Sender
@to	Email Address of the Recipient
@subject	Subject of the Email
@body	Body of the Email
@bodytype	Type of Body (Text or HTML)
@output_mesg	Output parameter that returns the status (Success / Failed)
@output_desc	Output parameter that returns the Error description if an error occurs

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

 

CREATE PROCEDURE [dbo].[sp_send_mail]

            @from varchar(500) ,

            @to varchar(500) ,

            @subject varchar(500),

            @body varchar(4000) ,

            @bodytype varchar(10),

            @output_mesg varchar(10) output,

            @output_desc varchar(1000) output

AS

DECLARE @imsg int

DECLARE @hr int

DECLARE @source varchar(255)

DECLARE @description varchar(500)

In the above SQL Snippet I have created the stored procedure and declared some variables that will be used later.

Create an OLE Instance of CDO

EXEC @hr = sp_oacreate 'cdo.message', @imsg out

SendUsing

SendUsing Specifies Whether to send using port (2) or using pickup directory (1)

EXEC @hr = sp_oasetproperty @imsg,

'configuration.fields("http://schemas.microsoft.com/cdo/configuration/

sendusing").value','2'

smtpserver

Specify your SMTP Server that you will use. Here I am using gmail SMTP Server.

EXEC @hr = sp_oasetproperty @imsg,

'configuration.fields("http://schemas.microsoft.com/cdo/configuration/

smtpserver").value', 'smtp.gmail.com'

 

sendusername

Specify the sender’s email address here. The account that will be used to send emails.

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/

sendusername").value', sender@gmail.com'

sendpassword

Specify the password of the account here.

EXEC @hr = sp_oasetproperty @imsg,

'configuration.fields("http://schemas.microsoft.com/cdo/configuration/

sendpassword").value', 'xxxxxxxxxxx'

smtpusessl

Specify where the SMTP server requires SSL (True) or not (False)

EXEC @hr = sp_oasetproperty @imsg,

'configuration.fields("http://schemas.microsoft.com/cdo/configuration/

smtpusessl").value', 'True'

 

smtpserverport

Specify the Port Number foy your SMTP Server (465 or 587)

EXEC @hr = sp_oasetproperty @imsg,

'configuration.fields("http://schemas.microsoft.com/cdo/configuration/

smtpserverport").value', '587'

 

 

smtpauthenticate

Specify the Type of Authentication Required None (0) / Basic (1) 

EXEC @hr = sp_oasetproperty @imsg,

'configuration.fields("http://schemas.microsoft.com/cdo/configuration/

smtpauthenticate").value', '1'

 

Send Email

Execute the OLE object to send email

EXEC @hr = sp_oamethod @imsg, 'configuration.fields.update', null

EXEC @hr = sp_oasetproperty @imsg, 'to', @to

EXEC @hr = sp_oasetproperty @imsg, 'from', @from

EXEC @hr = sp_oasetproperty @imsg, 'subject', @subject

EXEC @hr = sp_oasetproperty @imsg, @bodytype, @body

EXEC @hr = sp_oamethod @imsg, 'send', null

 

Error Handling

Below snippet is checking if the mail is send successfully. If not it captures the Error message and the

Error Description in the output variables

SET @output_mesg = 'Success'

IF @hr <>0

      SELECT @hr

      BEGIN

            EXEC @hr = sp_oageterrorinfo null, @source out, @description out

            IF @hr = 0

            BEGIN

                  set @output_desc =  @description

            END

      ELSE

      BEGIN

            SET @output_desc = ' sp_oageterrorinfo failed'

      END

      IF not @output_desc is NULL

                  SET @output_mesg = 'Error'

END

Destroy the OLE Object Instance

EXEC @hr = sp_oadestroy @imsg

 

Calling and Execute the Stored Procedure

Below I am calling the Stored Procedure and passing the parameters.

Note: the Bodytype can be HTML (htmlbody) or Text (textbody)

DECLARE @out_desc varchar(1000),

        @out_mesg varchar(10)

 

EXEC sp_send_mail 'sender@gmail.com',

      'receiver@gmail.com',

      'Hello',

      '<b>This is s Test Mail</b>',

      'htmlbody', @output_mesg = @out_mesg output,

      @output_desc = @out_desc output

 

PRINT @out_mesg

PRINT @out_desc

 

Enable OLE Automation in SQL Server 2005

OLE Automation is disabled by default in SQL Server 2005 hence to make this stored procedure work you will need to run the following script.

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO