Sql Server

Table

Tables are organized into rows and columns; and each table must have a name.

TempTable

SQL Server provides the concept of temporary table which helps the developer in a great way. These tables can be created at runtime and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited. These tables are created inside tempdb database.

Stored Procedure definition

SQL Server provides two types of temp tables based on the behavior and scope of the table. These are
                                 1.Local Temp Table
                                 2.Global 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 instances. Local temporary table name is stared with hash ("#") sign.

CREATE TABLE #LocalTempTable(

UserID int,

UserName varchar(50),

UserAddress varchar(150)

)

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

CREATE TABLE ##NewGlobalTempTable(

UserID int,

UserName varchar(50),

UserAddress varchar(150)

)

View
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table

Stored Procedure

A stored procedure is a group of sql statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure.

Functions

Function is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can’t use function to Insert, Update, Delete records in the database table(s).

There are two types of functions

1.System Defined
 Scalar Functions
Scalar functions operates on a single value and returns a single value

Scalar Function

Description

abs(-10.67)

This returns absolute number of the given number means 10.67.

rand(10)

This will generate random number of 10 characters.

round(17.56719,3)

This will round off the given number to 3 places of decimal means 17.567

upper('dotnet')

This will returns upper case of given string means 'DOTNET'

lower('DOTNET')

This will returns lower case of given string means 'dotnet'

ltrim(' dotnet')

This will remove the spaces from left hand side of 'dotnet' string.

convert(int, 15.56)

This will convert the given float value to integer means 15.

 Aggregate Functions
Aggregate functions operates on a collection of values and returns a single value.

max()

min()

Avg()

Count()

2.UserDefined Functions
These functions are created by user in system database or in user defined database. We three types of user defined functions.

Diff Between Functions & Procedures

Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).

Functions can have only input parameters for it whereas Procedures can have input/output parameters .

Function takes one input parameter it is mandatory but Stored Procedure may take o to n input parameters.

Functions can be called from Procedure whereas Procedures cannot be called from Function. 

Creating dynamic tables using sql server 2008

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Shankar Parshimoni

-- Create date: 05/19/2014

-- Description:   Creating dynamic tables

-- =============================================

ALTER PROCEDURE sproc_CreateTableAtRuntime

      @TableName NVARCHAR(128)

      ,@Column1Name NVARCHAR(32)

      ,@Column1DataType NVARCHAR(32)

      ,@Column1Nullable NVARCHAR(32)

AS

BEGIN TRY

      DECLARE @SQLString NVARCHAR(MAX)

      SET @SQLString = 'CREATE TABLE ' + @TableName + '( '+ @Column1Name + ' ' + @Column1DataType + ' '+ @Column1Nullable +')'

      EXEC (@SQLString)

END TRY

BEGIN CATCH

      SELECT ERROR_NUMBER() AS ErrorNumber

     ,ERROR_SEVERITY() AS ErrorSeverity

     ,ERROR_STATE() AS ErrorState

     ,ERROR_PROCEDURE() AS ErrorProcedure

     ,ERROR_LINE() AS ErrorLine

     ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

GO

SQL Server Provides with the foloowing Clauses

--WHERE CLAUSE

SELECT MAX(employeeId) from employeeTable

SELECT employeeId from employeeTable WHERE employeeId = (SELECT MAX(employeeId) from employeeTable)

SELECT MAX(employeeId) from employeeTable where employeeId < (SELECT MAX(employeeId) from employeeTable)

--ORDER By CLAUSE

SELECT * FROM employeeTable ORDER BY employeeId DESC

--GROUP By CLAUSE

SELECT MAX(employeeId) AS ID,name FROM employeeTable GROUP BY name

SELECT MIN(employeeId) AS ID,name FROM employeeTable GROUP BY name

--HAVING CLAUSE

SELECT MAX(employeeId) AS ID,name FROM employeeTable GROUP BY name HAVING MAX(employeeId)>150

SELECT MIN(employeeId) AS ID,name FROM employeeTable GROUP BY name HAVING MIN(employeeId)>103

--DISTINCT CLAUSE

SELECT DISTINCT name FROM employeeTable

SELECT DISTINCT email FROM employeeTable

SELECT DISTINCT name,email FROM employeeTable

JOINS

SELECT * FROM [dbo].[tbl_CustomerForJoin]

SELECT * FROm [dbo].[tbl_OrdersForJoin]

--SELF JOIN  

SELECT o.OrderId,c.Customername,c.CustomerAddress,o.ProductName from tbl_CustomerForJoin c,tbl_OrdersForJoin o

WHERE c.CustomerId=o.CustomerId

--INNER JOIN:   Returns all rows when there is at least one match in BOTH tables

SELECT c.CustomerName,c.CustomerAddress,o.OrderId,o.ProductName from tbl_CustomerForJoin c

INNER JOIN tbl_OrdersForJoin o

ON c.CustomerId=o.CustomerId

--LEFT JOIN:  Return all rows from the left table, and the matched rows from the right table

SELECT c.CustomerName,c.CustomerAddress,o.OrderId,o.ProductName from tbl_CustomerForJoin c

LEFT JOIN tbl_OrdersForJoin o

ON c.CustomerId=o.CustomerId

--RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

SELECT c.CustomerName,c.CustomerAddress,o.OrderId,o.ProductName from tbl_CustomerForJoin c

RIGHT JOIN tbl_OrdersForJoin o

ON c.CustomerId=o.CustomerId

--FULL JOIN: Return all rows when there is a match in ONE of the tables

SELECT c.CustomerName,c.CustomerAddress,o.OrderId,o.ProductName from tbl_CustomerForJoin c

FULL JOIN tbl_OrdersForJoin o

ON c.CustomerId=o.CustomerId