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



No comments: