Handsome Use Of Table Type Instead Of Temprary Table In MssqlServer2005


Hello Friends Sometime you find an alternative of temprary table which also occupy spaces in our temp database.and you feel other alternative of its in some logics.
Today i will you describe you me in exposer which i used in my project for saving of data in mssqlserver into a normalize table.
Step 1: Run give below script.
USE [master]
GO
/****** Object: Database [dbPatients] Script Date: 03/26/2011 16:15:43 ******/
CREATE DATABASE [dbPatients] ON PRIMARY
( NAME = N’dbPatients’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\dbPatients.mdf’ , SIZE = 2240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’dbPatients_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\dbPatients_log.LDF’ , SIZE = 768KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N’dbPatients’, @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
begin
EXEC [dbPatients].[dbo].[sp_fulltext_database] @action = ‘enable’
end
GO
ALTER DATABASE [dbPatients] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [dbPatients] SET ANSI_NULLS OFF
GO
ALTER DATABASE [dbPatients] SET ANSI_PADDING OFF
GO
ALTER DATABASE [dbPatients] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [dbPatients] SET ARITHABORT OFF
GO
ALTER DATABASE [dbPatients] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [dbPatients] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [dbPatients] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [dbPatients] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [dbPatients] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [dbPatients] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [dbPatients] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [dbPatients] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [dbPatients] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [dbPatients] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [dbPatients] SET ENABLE_BROKER
GO
ALTER DATABASE [dbPatients] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [dbPatients] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [dbPatients] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [dbPatients] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [dbPatients] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [dbPatients] SET READ_WRITE
GO
ALTER DATABASE [dbPatients] SET RECOVERY FULL
GO
ALTER DATABASE [dbPatients] SET MULTI_USER
GO
ALTER DATABASE [dbPatients] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [dbPatients] SET DB_CHAINING OFF
Step 2: now Make Tables with given below script.
USE [dbPatients]
GO
/****** Object: Table [dbo].[tbl_PatientDetail] Script Date: 03/26/2011 16:16:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_PatientDetail](
[intID] [int] IDENTITY(1,1) NOT NULL,
[strPatientID] [int] NULL,
[intHeight] [int] NULL,
[chrBlodGroup] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[intWeight] [int] NULL,
CONSTRAINT [PK_tbl_PatientDetail] PRIMARY KEY CLUSTERED
(
[intID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
STep 3: Make Second Table
USE [dbPatients]
GO
/****** Object: Table [dbo].[tbl_PatientMasters] Script Date: 03/26/2011 16:16:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_PatientMasters](
[intID] [int] NOT NULL,
[strFullName] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strAddress] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[strPhoneNo] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[chrGender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tbl_PatientMasters] PRIMARY KEY CLUSTERED
(
[intID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
STep 4: now Run Given Below Script and view Table Type use
DECLARE @strFullName VARCHAR(40)
DECLARE @strAddress VARCHAR(200)
DECLARE @strPhoneNo VARCHAR(10)
DECLARE @chrGender CHAR(1)
DECLARE @intHeight INT
DECLARE @chrBloodGroup Char(2)
DECLARE @intWeight INT

SET @strFullName=’Lakshya’
SET @strAddress=’H.No 745,St.no 12,Patiala’
SET @strPhoneNo =’9989987156′
SET @chrGender =’M’
SET @intHeight =145
SET @chrBloodGroup =’B+’
SET @intWeight =123

DECLARE @TBLPATIENTINFO TABLE (INTID INT IDENTITY(1,1) PRIMARY KEY,STRNAME VARCHAR(40),STRADD TEXT,STRPHONE VARCHAR(10),CHRGENDER CHAR(1),INTHEIGHT INT,CHRBLOODGROUP CHAR(2),INTWEIGHT INT)
INSERT INTO @TBLPATIENTINFO VALUES(@strFullName,@strAddress,@strPhoneNo,@chrGender,@intHeight,@chrBloodGroup,@intWeight)
IF ((SELECT COUNT(*) FROM @TBLPATIENTINFO)>0)
BEGIN
INSERT tbl_PatientMasters(intID,strFullName,strAddress,strPhoneNo,chrGender)
SELECT
(
SELECT MAX(dbo.tbl_PatientMasters.intID) FROM dbo.tbl_PatientMasters
)+ INTID,STRNAME,STRADD,STRPHONE,CHRGENDER FROM @TBLPATIENTINFO
INSERT tbl_PatientDetail(strPatientID,intHeight,chrBlodGroup,intWeight)
SELECT (
SELECT MAX(dbo.tbl_PatientMasters.intID) FROM dbo.tbl_PatientMasters
),INTHEIGHT,CHRBLOODGROUP,INTWEIGHT FROM @TBLPATIENTINFO
PRINT ‘RECORD IS INSERT,DO it AGAIN GUY!!’
END
ELSE
BEGIN
PRINT ‘NO RECORD IS INSERT,TRY AGAIN GUY!!’
enjoy and learn happy programming!

NTILE FUNCTIon of MSSQLSERVER


NTILE(): This is one of the most coolest function provided by T-SQL. This is very useful while grouping a dataset into multiple sets i.e. in case if you want to distribute the result set into more than one tables or destination, this function is useful in accomplishing this task.

Let’s consider we have a table with 1 million records, and we want to divide this 1 million records across five tables, then we can achieve this by using NTILE () function.

The functionality of NTILE () function is same as other ranking functions i.e. ROW_NUMBER(), RANK() and DENSE_RANK(), except it takes one parameter named “integer_expression” i.e. NTILE(integer_expression). The integer_expression signifies the number of groups the NTILE function will create to distribute the data. integer_expression can be of type int, or bigint but must be positive integer. If you specify any other value like 2.5 or -3, it will throw the below error:

The function ‘ntile’ takes only a positive int or bigint expression as its input.

Let’s consider the example of Employee table, where we have 7 records, and our purpose is to distribute these records into two separate tables, one containing highest salaries and the other with lower salaries. Then the query goes like this:

For the table with Highest Salaries:

[ Copy to Clipboard ] | [ View Source ]
SELECT * FROM (
SELECT NTILE(2) OVER( ORDER BY salary DESC) AS GroupID,* FROM dbo.employee
) InnQ
WHERE InnQ.GroupID = 1
OUTPUT:


For the table with Lowest Salaries:

SELECT * FROM (
SELECT NTILE(2) OVER( ORDER BY salary DESC) AS GroupID,* FROM dbo.employee
) InnQ
WHERE InnQ.GroupID = 2


You might have noticed here that in the first set we have four records while in the second one we have three records. This is because; we have odd number of records in the employee table from where these sets are derived. When the integer_expression parameter is divisible with the total number of records in the source table, then we can get even number of records in all the sets derived from it or else it will start filling the remaining records starting from first set.

Let’s dissect the query a little:

SELECT NTILE(2) OVER( ORDER BY salary DESC) AS GroupID,* FROM dbo.employee;

GroupID column has two distinct numbers on the basis of which the records are divided into two separate sets as shown below.


As the total number of records in the employee table is not divisible by the integer_expression parameter in NTILE (), we have two groups with different sizes differ by one member. The group with higher number of members comes before the group with small number of members.

Using in NTILE():

clause in NTILE () function increases it functionality a little more. Here, we will see how partition by affects NTILE () in the above example. Let’s consider the below query to group records by department wise.

SELECT NTILE(2) OVER(PARTITION BY DeptNo ORDER BY salary DESC) AS GroupID,* FROM dbo.employee;
OUTPUT:

Let’s dissect the above query and see the process behind it.

The OVER clause in the query will partition the employee table as per DeptNo i.e. into three sub-sets. Then the NTILE(2) will divide each partition into groups of two as show in the below figure.