SELECT statements with CASE expressions:

Normally when we are working with T-SQL queries we may have scenarios to use CASE expression to filter or filter the data based on conditionals. CASE expressions are very use full to acheive the above.

Eg: Example below gives you the breif idea how to use SELECT with CASE expressions.

IF EXISTS:

we may a face situation that need to verify the given 'value' or 'key' exists a table. SQL provides EXISTS key word in order to check the given value. In such scenarios, we can use the follwing IF condintion with EXISTS to procced the process furhter.

Eg: In a SELECT query if the value present in the table then return 1, so that condition will be true.

 

Table Variable:

Table variables are used within the scope of the routine or batch which they are defined. And its an alternative of Temporary table which can do all kinds of operations that we can perform in Temp table.

Example: Below is the syntax of table variable

USE AdventureWorks

GO

SET NOCOUNT ON

DECLARE @FirstTable TABLE (RandomInteger INT)

DECLARE @SecondTable TABLE (RandomInteger INT)

DECLARE @WhenWeStarted DATETIME

DECLARE @ii INT

BEGIN TRANSACTION

SET @ii = 0

WHILE @ii < 100

  BEGIN

    INSERT  INTO @FirstTable VALUES  (RAND() * 10000)

   

    SET @ii = @ii + 1

  END

--SELECT * FROM @FirstTable

 

SET @ii = 0

WHILE @ii < 100

  BEGIN

    INSERT  INTO @SecondTable VALUES  (RAND() * 10000)

   

    SET @ii = @ii + 1

  END

COMMIT TRANSACTION

SELECT  @WhenWeStarted = GETDATE()

SET STATISTICS PROFILE ON

SELECT  COUNT(*)

FROM    @FirstTable first

        INNER JOIN @SecondTable second

        ON first.RandomInteger = second.RandomInteger OPTION (RECOMPILE)

NOTE: We can use table variable when we are dealing with small amount of data or as function parameter in SPs

Temp Tables in SQL Server 2008 r2:

In SQL, developers may come across complex JOIN quires and large amount transactions in a SP, we can handle such type of queries by using Temp table concept.
 
Temporary tables are used by every DB developer, they can improve your code's performance and maintainability. They are used most often to provide workspace for the intermediate results when processing data within a procedure. Once finished with their use, they are discarded automatically or you can write in code itself.

If you are doing more complex processing on temporary data or likely to use more than reasonably small amounts of data in them, then  local temporary tables are likely to be  a better choice. These tables are created inside tempdb database.


SQL Server provides two types of temp tables based on the behavior and scope of the table. Those are:

• local temporary tables (starting with #)
• global temporary tables (starting with ##)

Local Temp Table

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from the instances. Local temporary table name is stared with hash ("#") sign.

Global Temp Table

Global Temporary tables are available to any user by any connection until all the connections have been closed. Global Temp Table name starts with a double hash ("##").

USE AdventureWorks
GO
CREATE TABLE ##GlobalTemp_Table(
ID INT,
Name VARCHAR(50),
City VARCHAR(150))
GO

NOTE: The scope of a global temp table is available for all connections.

How to write a optimistic stored procedure ?

In this article we will see how to write a stored procedure with optimization by making simple changes in the code. Following are the set of statements are helpful to improve the performance of a stored procedure.

SET NCOUNT ON Statement

• Microsoft SQL Server provides a set option called NOCOUNT. It is turned off by default so that each operation returns information regarding number of rows affected. But its useless once we done with result set. And our applications do not this information.
• If you set NCOUNT in ON mode, stored procedure won't return affected rows information, so that we can avoid or reduce the network traffic and load. And It clearly happens when the truncations occur on large amount of records.

• Example: Using NOCOUNT option

  CREATE PROCEDURE dbo.procSample  AS    SET NOCOUNT ON    SELECT   au_id    FROM      author

Access the table with NOLOCK

•  In any stored procedure, if we see the statements without having NOLOCK on table will definitely gain more performance. So that, we can use NOLOCK option when you perform a transaction on tables.

• Example: NOLOCK sample

  SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 2

Use RETURN values

• Whenever you need a single value from a query or update, consider using the return value of the stored procedure.

• Example: Sample that returns a count of the records in the table authors

  CREATE PROCEDURE dbo.procSample AS    SET NOCOUNT ON    DECLARE @recCount int    SELECT   @recCount = COUNT(au_id)    FROM     authors    RETURN @recCount

Tuning the query

• Prepare the query effectively without having sub selects and functions with in the functions

Provide Indexes

• Make sure that the tables are having Indexes when we are going to perform large number of transactions on a single columns

Do Not Use the Sp_ Prefix for Custom Stored Procedures

• If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.

Use IF EXISTS (SELECT 1) instead of (SELECT *):

• To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below: 
•  

  Example: Check If Record Exists

• IF EXISTS (SELECT 1 FROM Production.Product
            WHERE ProductNumber = 'AR-5381'
            )
           
    PRINT 'AR'
ELSE
    PRINT 'A'

String Functions in T-SQL:

1) ASCII (char_expression..) : Returns the ASCII value of the character. See the below example

      

2 ) CHAR(int_expression): Returns the charater of the integer parameter (ASCII value). See the below example

3) CHARINDEX (exp_To_Find, exp_To_Search, start_location_search): Returns the position of the serach value in a givn string.

4) CONCAT(string_value1, string_value2.....string_valueN): Concats all the string specified as parameters

SELECT CONCAT ('Hello','World')

5) LEFT(string_exp, n) : Returns the string with specifed length

SELECT

LEFT('Hello World SQL',10)

6) LEN (string_exp): Returns the length of the string

USE

AdventureWorks;

GO

SELECT

LEN(FirstName) AS Length, FirstName, LastName

FROM

Sales.vIndividualCustomer

WHERE

CountryRegionName = 'Australia';

GO

       

While Loop...SQL

SQL allows to write 'while' loop as like normal program languages. But we should take care of begin and end blocks. All the statements should lie in between BEGIN and END blocks only.

Following are the sample While Loop Examples:

Eg1: Print the numbers upto 100

Example 2

Example 2

LTRIM() & RTRIM() Functions:

We have pre-defined trim funcations in SQL inorder to remove blank spaces in text. LTRIM() funcation removes the blank spaces from left side and RTRIM() function removes the blank spaces from right side. Both the two funcations expect string as parameter.

LTRIM ( character_expression ), RTRIM(character_expression)

Error Handling in SQL: (Try.... Catch...):

 In SQL, we can acheive error handling with the help of  Try.. Catch blocks. If we anticipate errors in a piece of SQL code, we can keep the code between Try..Catch block. If any error occurs, Try block will handle the error and Catch blok will send respective error massage. See the example below with syntax.

Examples:

SQL Server Reporting Services (SSRS) - Part I

In this article we are going to look at how to create a simple report then build and execute the report.Reports can be exported to multiple formats including delimited text, XML,PDF format and Microsoft Excel as well.

Let s look into the process and see the step by step process on how to create and execute an SSRS report.

Architecture:
The Report Manager is the central person who acts as a manager to decide when the reports will be scheduled to run along with maintaining the user profiles on the report server. Also, you can ask the report manager to view or search certain reports. He also helps in site property configuration and folder management in the report server.

There is something known as a Report Server, at which all the reports reside. All other activities pertaining to SSRS is done at report server. I believe it acts like a workstation for the reporting tool.

Report Designer is basically a graphical tool that are hosted within the Microsoft Visual Studio IDE. Report Designer provides a tabbed windows for Data, Layout, and Preview that allow you to design a report interactively. You can add datasets to accommodate a new report design idea, or adjust report layout based on preview results. Also, he provides query builders, an Expression editor, and wizards to step you through the process of creating a simple report.

Step1: Go to SQL Server Business Intelligence Development Studio (All Programs --> Microsoft SQL Server 2008 r2 --> SQL Server Business Intelligent Development Studio) and create a new project as shown below

Step 1: Create a new sample report
 

Step 2: Report Wizard

Step 3: Creating New data source

Step 4: Set up the connection string settings

Step 5: Data Source settings has been done

 

 Step 6: Design the query

 

Step 7: Select the table objects and columns and preparing query

  

Step 8: Select the Report Type

 

Step 9: Design the table

 

Step 10: Select the table style

 

Step 11: Deploy the reports into the Report Server

 

Step 12: Give the report name

 

Step 13: Preview of the sample report