Datatype- A datatype defines a column contain what kind of value.
Create Table Student
(
Student_Id Int Identity(1,1) Primary Key,
Name Varchar(50) Not Null,
City Varchar(50) Null,
PhoneNumber Int
)
(
Student_Id Int Identity(1,1) Primary Key,
Name Varchar(50) Not Null,
City Varchar(50) Null,
PhoneNumber Int
)
Int, varchar(50) - These are data type.
We are using datatype in below (some sample) cases.
1) while creating Table.
2) while Altering Table.
3) while Declaring variable.
Declare @num float
Set @num=195.32
Print @num
4) Convert function - Cast function
Select Convert(varchar(5),Student_Id) As StudentId1 From Student
Select Cast(Student_Id As varchar(5)) As StudentId2 From Student
example
Student_Id column contain 1,2,3,4 Numbers it means we can store Numeric/ Integer values in this column.
Column value , variable acquire some memory in Database & it is dependent on Datatype Size. Student_Id Int , to store 1/2/3/4 Student_Id is of INT datatype, it consume 4 Bytes Space for each Student_Id value.
Different Datatypes in SQL server -
Number / Integer Data Type - it is used for to save Integer Number.
TinyInt 1 Byte =0 to 255
SmallInt 2 Bytes = -32,768 to 32,767
Int 4 Bytes =-2^31 to 2^31
Bigint 8 Bytes =-2^63 to 2^63
Declare @Number TinyInt
Set @Number=255
Select @Number AS Number
Declare @Number SmallInt
Set @Number=32767
Select @Number AS Number
Declare @Number Int
Set @Number=451344333
Select @Number AS Number
Declare @Number BigInt
Set @Number=4513443335564545454
Select @Number AS Number
Decimal Datatype - used for to store decimal / Numeric Number, for example 125.545
Decimal & Numeric are the same One.Decimal (Precision, Scale) = 125.36 = Precision is 5 and scale is 2. Precision will be 1 to 38 number based on that storage bytes require 5 to 17 resp.
float & real are used for to store small number.
float - 4 Bytes or 8 Bytes , real - 4 Bytes
SmallMoney 4 Bytes
Money 8 Bytes
it represent monetary or currency values.
Declare @num1 Real
Set @num1=241444545423232355.32
Print @num1
Declare @num float
Set @num=5667567567544.32
Print @num
Declare @num3 Numeric(18,2)
Set @num3=4545455545454545.25
Print @num3
Declare @num4 Decimal(32,2)
Set @num4=154595476.32
Print @num4
String Datatype
char and varchar
Character data types that are either fixed-size, char, or variable-size, varchar.
nchar and nvarchar
Unicode character data types that are either fixed-size, nchar, or variable-size, nvarchar.
text and ntext - will be removed in a future version of SQL Server.
Create Table CheckcharDatatype
(
ID Int Primary Key Identity(1,1),
Name_Char char(100),
Name_NChar Nchar(100),
Name_varchar varchar(100),
Name_Nvarchar Nvarchar(100),
Name_varcharmax varchar(max),
Name_Nvarcharmax Nvarchar(max),
Name_Text Text,
Name_Ntext NText
)
Insert Into CheckcharDatatype values ('India','India','India','India','India','India','India','India')
one tricky example of unicode character
Declare @name nvarchar(50)
Set @name='आप कैसे हैं'
Print @Name
Output-
?? ??? ?????
Declare @name nvarchar(50)
Set @name=N'आप कैसे हैं'
Print @Name
Output-
आप कैसे हैं
Boolean - to save 0 or 1 value - true (1) & False(0) -Datatype is Bit.
1 Byte storage required.
Declare @isgraduate bit
Set @isgraduate=1
Select @isgraduate
if @isgraduate=1
Select 'Graduate Candidate'
Else
Select 'Ungraduate Candidate'
Declare @number bit
Set @number=-454
Select @number
Date - This Datatype is used for to save Date , time & Datetime.
Data type Output
--------------------------------------------------------------------------------
date 2023-05-10
time 20:35:29.1234567
smalldatetime 2023-05-10 20:35:00
datetime 2023-05-10 20:35:29.123
datetime2 2023-05-10 20:35:29.1234567
datetimeoffset 2023-05-10 20:35:29.1234567 +05:30
Declare @Currentdate Date
Set @Currentdate=getdate()
Select @Currentdate As _Date
output - 2023-05-17
Declare @Currenttime Time
Set @Currenttime=getdate()
Select @Currenttime As _Time
output - 11:07:26.9300000
Declare @CurrentsmallDatetime SmallDateTime
Set @CurrentsmallDatetime=getdate()
Select @CurrentsmallDatetime As _DateTime
output - 2023-05-17 11:07:00
Declare @CurrentDatetime DateTime
Set @CurrentDatetime=getdate()
Select @CurrentDatetime As _DateTime
output - 2023-05-17 11:07:26.930
Declare @CurrentDatetime2 DateTime2
Set @CurrentDatetime2=getdate()
Select @CurrentDatetime2 As _DateTime2
output - 2023-05-17 11:07:26.9300000
Declare @CurrentDatetimeoffset datetimeoffset
Set @CurrentDatetimeoffset=sysdatetimeoffset()
Select @CurrentDatetimeoffset As _DateTimeofset
output - 2023-05-17 11:07:26.9317754 +05:30
Binary - to Save binary Data like 10101000001100111 in Table.
binary - fixed length binary data.
varbinary - variable length binary data.
varbinary(max) - variable length binary data. 1 through 8,000.
Other Datatype
Cursor - Cursor is a database object to retrieve data from a result set one row at a time, We use cursor when we need to do perform operation row by row basis.
Example
Declare @Cursor Cursor
Declare @name varchar(50)
Set @Cursor=Cursor Forward_only For
Select Name from Student
Open @Cursor
fetch Next From @Cursor into @name
While @@FETCH_STATUS=0
Begin
Print @name
fetch Next From @Cursor into @name
End
Close @cursor
Deallocate @cursor
output
-----------------------
Rahul
Kiran
Manish
Ketan
UniqueIdentifier ---
GUID- Global Unique Identification number.
36 characters length
16 bytes taking to store UniqueIdentifier value.
Identity is used to uniquely identify records within a table.
& GUIDs can be used to uniquely identify records across tables, databases, and servers.
38FFA969-EB4D-41EF-AA0E-F1E255A36DC5
4 -2 -2 -2 -6 =16 byte
16 Byte * 8 bit = 128 bit
CREATE TABLE GUID_Example
(
SeqCol uniqueidentifier DEFAULT NewSequentialID(),
IDCol uniqueidentifier DEFAULT NEWID()
)
GO
INSERT INTO GUID_Example DEFAULT VALUES
INSERT INTO GUID_Example DEFAULT VALUES
SeqCol IDCol
----------------------------------------------------------------------------------------------------------------------------------------
7B63D241-F8EF-ED11-8A05-508492BBAC14 97997469-3D9B-492E-886B-D0CFBC95AF71
7C63D241-F8EF-ED11-8A05-508492BBAC14 96195494-F36A-4182-8F46-86F3655F4507
Why UniqueIdentifier come into the picture?
1) identity have specific limit.
2) UniqueIdentifier is used for uniquely identify records across tables, databases, and servers. example
Use practice
Create Table Student
(
Student_Id Int Identity(1,1) Primary Key,
Name Varchar(50) Not Null,
City Varchar(50) Null,
PhoneNumber Int
)
Insert Into Student(Name,City,PhoneNumber) Values ('Rahul','Pune',45454)
Insert Into Student(Name,City,PhoneNumber) Values ('Kiran','Mumbai',54545)
Insert Into Student(Name,City,PhoneNumber) Values ('Manish','Delhi',42121)
Insert Into Student(Name,City,PhoneNumber) Values ('Ketan','Pune',12112)
Use practice1
Create Table Student
(
Student_Id Int Identity(1,1) Primary Key,
Name Varchar(50) Not Null,
City Varchar(50) Null,
PhoneNumber Int
)
Insert Into Student(Name,City,PhoneNumber) Values ('mayank','Pune',45454)
Insert Into Student(Name,City,PhoneNumber) Values ('gopal','Mumbai',54545)
Insert Into Student(Name,City,PhoneNumber) Values ('krish','Delhi',42121)
Insert Into Student(Name,City,PhoneNumber) Values ('shree','Pune',12112)
Create Table Studentcollection
(
Student_Id Uniqueidentifier Primary Key,
Name Varchar(50) Not Null,
City Varchar(50) Null,
PhoneNumber Int
)
Insert into [practice1].dbo.Studentcollection
Select * from [practice].dbo.Student
union all
Select * from [practice1].dbo.Student
below error we will get because 1 value in both result set, we can resolve it using Uniqueidentifier.
--Msg 2627, Level 14, State 1, Line 28
--Violation of PRIMARY KEY constraint 'PK__Studentc__A2F4E98CC412D729'.
--Cannot insert duplicate key in object 'dbo.Studentcollection'. The duplicate key value is (1).
--The statement has been terminated.
example of uniqueidentifier,
Use practice
Create Table Studentguid
(
Student_Id uniqueidentifier Primary Key default NewSequentialID(),
Name Varchar(50) Not Null,
City Varchar(50) Null,
PhoneNumber Int
)
Insert Into Studentguid(Name,City,PhoneNumber) Values ('Rahul','Pune',45454)
Insert Into Studentguid(Name,City,PhoneNumber) Values ('Kiran','Mumbai',54545)
Insert Into Studentguid(Name,City,PhoneNumber) Values ('Manish','Delhi',42121)
Insert Into Studentguid(Name,City,PhoneNumber) Values ('Ketan','Pune',12112)
Use practice1
Create Table Studentguid
(
Student_Id uniqueidentifier Primary Key default NewSequentialID(),
Name Varchar(50) Not Null,
City Varchar(50) Null,
PhoneNumber Int
)
Insert Into Studentguid(Name,City,PhoneNumber) Values ('mayank','Pune',45454)
Insert Into Studentguid(Name,City,PhoneNumber) Values ('gopal','Mumbai',54545)
Insert Into Studentguid(Name,City,PhoneNumber) Values ('krish','Delhi',42121)
Insert Into Studentguid(Name,City,PhoneNumber) Values ('shree','Pune',12112)
Create Table Studentcollection
(
Student_Id Uniqueidentifier Primary Key,
Name Varchar(50) Not Null,
City Varchar(50) Null,
PhoneNumber Int
)
Insert into [practice1].dbo.Studentcollection
Select * from [practice].dbo.Studentguid
union all
Select * from [practice1].dbo.Studentguid
sql_variant - A column of type sql_variant may contain rows of different data types.
A data type that stores values of various SQL Server-supported data types.
example
CREATE TABLE [dbo].[Tab_sql_variant](
[ID] [int] IDENTITY(1,1) NOT NULL,
[VarDatatype] [sql_variant] NULL,
[Datatype] varchar(20) NULL
)
Insert into Tab_sql_variant Values ('Sagar','char/varchar')
Insert into Tab_sql_variant Values (555,'Number/int')
Insert into Tab_sql_variant Values (25.62,'Decimal point')
Insert into Tab_sql_variant Values ('2023-05-21','Date')
Insert into Tab_sql_variant Values ('2023-05-21 10:23:08.300','Datetime')
Insert into Tab_sql_variant Values ('10:23:08.300','Time')
Insert into Tab_sql_variant Values (0,'bit')
Insert into Tab_sql_variant Values (1,'bit')
Insert into Tab_sql_variant Values ($25,'Money')
Insert into Tab_sql_variant Values (NEWID(),'UniqueIdentifier')
Select * from Tab_sql_variant
XML - It's the data type that stores XML data. You can store xml instances in a column, or a variable of xml type.
max size is 2GB.
---------Write
Select * from Student
for XMl path ('Student'), Root ('StudentTable')
<StudentTable>
<Student>
<Student_Id>1</Student_Id>
<Name>Rahul</Name>
<City>Pune</City>
<PhoneNumber>45454</PhoneNumber>
</Student>
<Student>
<Student_Id>2</Student_Id>
<Name>Kiran</Name>
<City>Mumbai</City>
<PhoneNumber>54545</PhoneNumber>
</Student>
<Student>
<Student_Id>3</Student_Id>
<Name>Manish</Name>
<City>Delhi</City>
<PhoneNumber>42121</PhoneNumber>
</Student>
<Student>
<Student_Id>4</Student_Id>
<Name>Ketan</Name>
<City>Pune</City>
<PhoneNumber>12112</PhoneNumber>
</Student>
</StudentTable>
-----------Assign xml data to XML DataType
Declare @x XML
set @x=(
Select * from Student
for XMl path ('Student'), Root ('StudentTable')
)
select @x
Image - Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.
Spatial -
The geometry type represents data in a Euclidean (flat) coordinate system.
The geography type represents data in a round-earth coordinate system.
Table variable - Table as Datatype
Declare @Student Table
(
Student_Id Int identity(1,1),
Name Varchar(50) Not Null
)
Insert Into @Student(Name) Values ('Rahul')
Select * from @Student
Student_Id Name
------------------------------
1 Rahul
Comments
Post a Comment
If you have any doubt then please let me know in comment section.