Data
types in SQL Server are organized into the following categories:
·
Exact numerics
·
Unicode character
strings
·
Approximate
numerics
·
Binary strings
·
Date and time
·
Other data types
·
Character strings
In SQL Server, based on their storage characteristics, some
data types are designated as belonging to the following groups:
·
Large value data types: VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX)
·
Large object data types: TEXT, NTEXT, IMAGE, VARCHAR(MAX), NVARCHAR(MAX),
VARBINARY(MAX), and XML.
sp_help returns -1 as the length for the large-value and XML data
types.
Example:
sp_help INT
sp_help XML
Exact Numerics
- BIT
- TINYINT
- SMALLINT
- INT
- BIGINT
- NUMERIC
- DECIMAL
- SMALLMONEY
- MONEY
Approximate
Numerics
- FLOAT
- REAL
Date and Time
- DATE
- DATETIMEOFFSET
- DATETIME2
- SMALLDATETIME
- DATETIME
- TIME
Character Strings
- CHAR
- VARCHAR
- TEXT
Unicode Character
Strings
- NCHAR
- NVARCHAR
- NTEXT
Binary Strings
- BINARY
- VARBINARY
- IMAGE
Other Data Types
- CURSOR
- TIMESTAMP
- HIERARCHYID
- UNIQUEIDENTIFIER
- TABLE
- XML
- SQL_VARIANT
Spatial Types
·
GEOGRAPHY
·
GEOMETRY
Differences among BIT, TINYINT, SMALLINT, INT and BIGINT data
types.
BIT
|
TINYINT
|
SMALLINT
|
INT
|
BIGINT
|
|
Bytes
|
1 byte
Optimizes storage of bit columns. If there
are 8 or less bit columns in a table, the columns are stored
as 1 byte. If there are from 9 up to 16 bit columns, the
columns are stored as 2 bytes, and so on
|
1 byte
|
2 bytes
|
4 bytes
|
8 bytes
|
Min Value
|
FALSE (it can be
converted to 0)
|
0
|
-32768
- 2^15
|
-2147483648
- 2^31
|
-9,223,372,036,854,775,808
- 2^63
|
Max Value
|
TRUE(it can be
converted to 1)
|
255
|
32767
2^15-1
|
2147483647
2^31-1
|
9,223,372,036,854,775,808
2^63-1
|
Max Length
|
Depends on its value
|
3 digits
|
5 digits
|
10 digits
|
19 digits
|
Type
|
unsigned integer
(allows only 0 and positive
value)
|
signed integer
(allows 0, (–) and (+)
numbers)
|
signed integer
(allows 0, (–) and (+)
numbers)
|
signed integer
(allows 0, (–) and (+)
numbers)
|
|
Example
|
DECLARE @Bit BIT = 'True'
PRINT @Bit
OUTPUT:1
DECLARE @Bit BIT = 0
PRINT @Bit
OUTPUT:1
|
DECLARE @TinyInt TINYINT
= 255
PRINT @TinyInt
OUTPUT:255
DECLARE @TinyInt TINYINT
= -1
PRINT @TinyInt
OUTPUT:
Error
|
DECLARE
@SmallInt SMALLINT =
32767
PRINT @SmallInt
OUTPUT: 32767
|
DECLARE
@Int INT = 2147483647
PRINT
@Int
OUTPUT: 2147483647
|
DECLARE @BigInt BIGINT
= 9223372036854775807
PRINT @BigInt
OUTPUT: 9223372036854775807
|
Differences among DECIMAL/NUMERIC, FLOAT and REAL data types.
DECIMAL/
NUMERIC
|
FLOAT
|
REAL
|
||||||||||||||||||||
Bytes
|
5 bytes - 17 bytes
Maximum storage
sizes vary, based on the precision
|
4 bytes to 8 bytes
depends on nvvalue
|
8 bytes
|
|||||||||||||||||||
Minimum Value
|
- 10^38 +1
|
- 1.79E+308 to -2.23E-308, 0
|
- 3.40E + 38 to -1.18E - 38, 0
|
|||||||||||||||||||
Maximum Value
|
10^38 - 1
|
2.23E-308 to 1.79E+308
|
1.18E - 38 to 3.40E + 38
|
|||||||||||||||||||
Max Length
|
38 digits
The default precision is 18.
|
|||||||||||||||||||||
Format
|
DECIMAL(p)
DECIMAL(p,s)
p- precision
s- scale
|
FLOAT(s)
|
REAL
|
|||||||||||||||||||
Example
|
DECLARE @Decimal DECIMAL(38) = 92233720368547758079223372036854775807
SELECT @Decimal
DECLARE @Decimal DECIMAL(38,2) =
122337203685477580792233720368547758.07
SELECT @Decimal
DECLARE @Decimal DECIMAL(38,3) =
12233720368547758079223372036854775.807
SELECT @Decimal
DECLARE @Numeric NUMERIC(38) = 92233720368547758079223372036854775807
SELECT @Numeric
DECLARE @Numeric NUMERIC(38,2) =
122337203685477580792233720368547758.07
SELECT @Numeric
DECLARE @Numeric NUMERIC(38,3) =
12233720368547758079223372036854775.807
SELECT @Numeric
54775 – Unit
. – Decimal point
807 - Mantissa
|
DECLARE @Float FLOAT(53) = 92233720368547758079223372036854775807
SELECT @Float
DECLARE @Float FLOAT(1) = 922337.47
PRINT @Float
OUTPUT: 922338
DECLARE @Float FLOAT(1) = 922337.46
PRINT @Float
OUTPUT: 922337
|
DECLARE @Real REAL = 92233720368547758079223372036854775807
SELECT @Real
DECLARE @Real REAL = 922337.47
PRINT @Real
OUTPUT: 922338
DECLARE @Real REAL = 922337.46
PRINT @Real
OUTPUT: 922338
|
What
are the differences between SMALLMONEY and MONEY [SMALLMONEY VS MONEY]?
Which
data type should use for defining a column in a table [SMALLMONEY VS MONEY]?
SMALLMONEY
|
MONEY
|
|
Bytes
|
4 bytes
|
8 bytes
|
Minimum Value
|
- 214,748.3648
|
-922,337,203,685,477.5808
|
Maximum Value
|
214,748.3647
|
922,337,203,685,477.5807
|
Max Length
|
||
Format
|
SMALLMONEY - Round of 2 digit
|
MONEY – Round of 2 digit
|
Example
|
DECLARE @smallmoney SMALLMONEY = 210000.36125
PRINT @smallmoney
OUTPUT: 210000.36
DECLARE @smallmoney SMALLMONEY = $210000.36125
PRINT @smallmoney
OUTPUT: 210000.36
210000
– Unit
.
– Decimal point
36
- Mantissa
|
DECLARE @money MONEY = 2102333434000.365251232
PRINT @money
OUTPUT: 2102333434000.37
DECLARE @money MONEY = $2102333434000.365251232
PRINT @money
OUTPUT: 2102333434000.37
|
What
are the differences between SMALLDATETIME and DATETIME [SMALLDATETIME VS
DATETIME]?
Which
data type should use for defining a column in a table [SMALLDATETIME VS
DATETIME]?
SMALLDATETIME
|
DATETIME
|
|
Bytes
|
It consumes 4 bytes
|
It consumes 8 bytes
|
Minimum Value
|
1900-01-01 00:00:00
YYYY-mm-DD HH:MM:SS
Data accuracy max in
seconds
Round off logic for
seconds:
29.998 sec = 29:00
seconds
29.999 sec or more =
30:00 seconds
|
1753-01-01
00:00:00.000
YYYY-mm-DD HH:MM:SS.ms
Data accuracy is
maximum 3 digits of milliseconds
Rounded to increments
of .000, .003, or .007 second
23:59:58.990 or
23:59:58.991 = 23:59:58.990
23:59:58.992 or
23:59:58.994 = 23:59:58.993
|
Maximum Value
|
2079-12-31 23:59:00
|
9999-12-31 23:59:59.997
|
Data
Storage
Type
|
Used to store the date
time data without fractional seconds
precision
|
Used to store the date
time data with maximum 3 digits fractional seconds precision
|
Storage
|
Storage size is fixed
4bytes
|
Storage size is fixed
8bytes
|
Use When?
|
When fractional
seconds precision is not needed and
date value does not exceed 2079-12-31 23:59:00
|
When 3 digits fractional
seconds precision and date value exceeds 2079-12-31 23:59:00
SELECT GETDATE()
OUTPUT:
2016-11-24 17:25:22.040
|
Optional Parameter
value
|
No optional parameter for this data type
DECLARE @Now SMALLDATETIME = GETDATE()
PRINT @Now
OUTPUT:
Nov 24 2016 5:30PM
|
No optional parameter for this data type
DECLARE @Now DATETIME = GETDATE()
PRINT @Now
OUTPUT:
Nov 24 2016 5:30PM
|
+ OR - Days
|
It supports + or – for adding to or subtracting from
and also use DATEADD function to add or subtract.
DECLARE @Now SMALLDATETIME
SET @Now = DATEADD(D, -1, GETDATE())
PRINT @Now
OUTPUT:
Nov 23 2016 6:29PM
DECLARE @Now SMALLDATETIME
SET @Now = DATEADD(D, -96000, GETDATE())
PRINT @Now
OUTPUT:
ERROR:
Msg 242, Level 16, State 3, Line 2
The conversion of
a datetime data type to a smalldatetime data type resulted in an out-of-range
value
|
It supports + or – for adding to or subtracting from
and also use DATEADD function to add or subtract.
DECLARE @Now DATETIME = DATEADD(D, -96000, GETDATE())
PRINT @Now
OUTPUT:
Jan 22 1754 5:43PM
DECLARE @Now DATETIME = GETDATE() - 96000
PRINT @Now
OUTPUT:
Jan 22 1754 5:43PM
|
Date and Time Styles
SQL Server supports the following
various date format
|
||
Symbol
|
Expression
|
Output Date Format
|
1
|
CONVERT(VARCHAR(20), GETDATE(), 1)
|
11/30/16
|
2
|
CONVERT(VARCHAR(20), GETDATE(), 2)
|
16.11.30
|
3
|
CONVERT(VARCHAR(20), GETDATE(), 3)
|
30/11/16
|
4
|
CONVERT(VARCHAR(20), GETDATE(), 4)
|
30.11.16
|
5
|
CONVERT(VARCHAR(20), GETDATE(), 5)
|
30-11-16
|
6
|
CONVERT(VARCHAR(20), GETDATE(), 6)
|
30 Nov 16
|
7
|
CONVERT(VARCHAR(20), GETDATE(), 7)
|
Nov 30, 16
|
8
|
CONVERT(VARCHAR(20), GETDATE(), 8)
|
13:12:44
|
9
|
CONVERT(VARCHAR(20), GETDATE(), 9)
|
Nov 30 2016
1:12:44:673PM
|
10
|
CONVERT(VARCHAR(20), GETDATE(), 10)
|
11-30-16
|
11
|
CONVERT(VARCHAR(20), GETDATE(), 11)
|
16/11/30
|
12
|
CONVERT(VARCHAR(20), GETDATE(), 12)
|
161130
|
13
|
CONVERT(VARCHAR(30), GETDATE(), 13)
|
30 Nov 2016 13:12:44:673
|
14
|
CONVERT(VARCHAR(30), GETDATE(), 14)
|
13:12:44:673
|
20
|
CONVERT(VARCHAR(30), GETDATE(), 20)
|
2016-11-30 13:12:44
|
21
|
CONVERT(VARCHAR(30), GETDATE(), 21)
|
2016-11-30 13:12:44.673
|
101
|
CONVERT(VARCHAR(20), GETDATE(), 101)
|
11/30/2016
|
102
|
CONVERT(VARCHAR(20), GETDATE(), 102)
|
2016.11.30
|
103
|
CONVERT(VARCHAR(20), GETDATE(), 103)
|
30/11/2016
|
104
|
CONVERT(VARCHAR(20), GETDATE(), 104)
|
30.11.2016
|
105
|
CONVERT(VARCHAR(20), GETDATE(), 105)
|
30-11-2016
|
106
|
CONVERT(VARCHAR(20), GETDATE(), 106)
|
30 Nov 2016
|
107
|
CONVERT(VARCHAR(20), GETDATE(), 107)
|
Nov 30, 2016
|
108
|
CONVERT(VARCHAR(20), GETDATE(), 108)
|
13:12:44
|
109
|
CONVERT(VARCHAR(20), GETDATE(), 109)
|
Nov 30 2016
1:12:44:673PM
|
110
|
CONVERT(VARCHAR(20), GETDATE(), 110)
|
11-30-2016
|
111
|
CONVERT(VARCHAR(20), GETDATE(), 111)
|
2016/11/30
|
112
|
CONVERT(VARCHAR(20), GETDATE(), 112)
|
20161130
|
113
|
CONVERT(VARCHAR(30), GETDATE(), 113)
|
30 Nov 2016 13:12:44:673
|
114
|
CONVERT(VARCHAR(30), GETDATE(), 114)
|
13:12:44:673
|
120
|
CONVERT(VARCHAR(30), GETDATE(), 120)
|
2016-11-30 13:12:44
|
121
|
CONVERT(VARCHAR(30), GETDATE(), 121)
|
2016-11-30 13:12:44.673
|
126
|
CONVERT(VARCHAR(30), GETDATE(), 126)
|
2016-11-30T13:12:44.673
|
127
|
CONVERT(VARCHAR(30), GETDATE(), 127)
|
2016-11-30T13:12:44.673
|
130
|
CONVERT(NVARCHAR(30), GETDATE(), 130)
|
1 ربيع الاول 1438 1:12:44:67
|
131
|
CONVERT(VARCHAR(30), GETDATE(), 131)
|
1/03/1438
1:12:44:673PM
|
What are the differences between DATETIME and DATETIME2(N) [DATETIME VS DATETIME2(N)?
Which
data type should use for defining a column in a table [DATETIME VS
DATETIME2(N)?
DATETIME
|
DATETIME2(N)
|
|
Bytes
|
It consumes 8 bytes
|
It consumes 6 to 8 bytes based on the optional parameter (N)
for fractional seconds precision storage
|
Minimum Value
|
1753-01-01
00:00:00.000
YYYY-mm-DD HH:MM:SS.ms
Gives 3 digits of milliseconds
|
0001-01-01
00:00:00.0000000
YYYY-mm-DD HH:MM:SS.ms
Gives 7 digits of
milliseconds
|
Maximum Value
|
9999-12-31 23:59:59.997
|
9999-12-31
23:59:59.9999999
|
Data
Storage
Type
|
Used to store the date
time data with maximum 3 digits fractional seconds precision
|
Used to store date
time data with maximum 7 digits fractional seconds precision
|
Storage
|
Storage size is fixed
8bytes
|
Storage size is
varying based on the optional parameter (N)
N – Specifies the
number of digit fractional seconds precision would be stored
N=0 or then consumes 8 bytes
N<3 then consumes 6
bytes
N>=3 and <=4
then consumes 7 bytes
N>4 then consumes 8
bytes
|
Use When?
|
3 digits fractional
seconds precision will full fill your requirements
SELECT GETDATE()
OUTPUT:
2016-11-24 17:25:22.040
|
More than 3 digits fractional
seconds precision will full fill your requirements
SELECT SYSDATETIME()
OUTPUT:
2016-11-24
17:26:45.5312071
|
Optional Parameter
value
|
No optional parameter for this data type
DECLARE @Now DATETIME = GETDATE()
PRINT @Now
OUTPUT:
Nov 24 2016 5:30PM
|
The optional parameter value N [DATETIME2(N)] is not
specified in the variable declaration or column definition then it is
considered as 7.
DECLARE @Now DATETIME2 = SYSDATETIME()
PRINT @Now
OUTPUT:
2016-11-24
17:26:45.5312071
DECLARE @Now DATETIME2(3) = SYSDATETIME()
PRINT @Now
OUTPUT:
2016-11-24
17:26:45.531
|
+ OR - Days
|
It supports + or – for adding to or subtracting from
and also use DATEADD function to add or subtract.
DECLARE @Now DATETIME = DATEADD(D, -96000, GETDATE())
PRINT @Now
OUTPUT:
Jan 22 1754 5:43PM
DECLARE @Now DATETIME = GETDATE() - 96000
PRINT @Now
OUTPUT:
Jan 22 1754 5:43PM
|
It does not support + or – for adding days to or subtracting days from date.
Use DATEADD function for
DECLARE @Now DATETIME2(2)
SET @Now = SYSDATETIME()- 96000
PRINT @Now
Error:
Msg
206, Level 16, State 2, Line 2
Operand
type clash: datetime2 is incompatible with int
DECLARE @Now DATETIME2(7) = DATEADD(D, -700000, SYSDATETIME())
PRINT @Now
OUTPUT:
0100-05-13 17:46:17.8562603
|
Data Conversion
|
DECLARE @Now DATETIME = GETDATE()
PRINT CONVERT(VARCHAR(20), @Now, 101)
PRINT CONVERT(VARCHAR(20), @Now, 102)
PRINT CONVERT(VARCHAR(20), @Now, 103)
PRINT CONVERT(VARCHAR(20), @Now, 104)
PRINT CONVERT(VARCHAR(20), @Now, 105)
PRINT CONVERT(VARCHAR(20), @Now, 106)
PRINT CONVERT(VARCHAR(20), @Now, 107)
PRINT CONVERT(VARCHAR(20), @Now, 108)
OUTPUT:
11/24/2016
2016.11.24
24/11/2016
24.11.2016
24-11-2016
24 Nov
2016
Nov 24,
2016
17:35:34
|
DECLARE @Now DATETIME2(7) = SYSDATETIME()
PRINT CONVERT(VARCHAR(20), @Now, 101)
PRINT CONVERT(VARCHAR(20), @Now, 102)
PRINT CONVERT(VARCHAR(20), @Now, 103)
PRINT CONVERT(VARCHAR(20), @Now, 104)
PRINT CONVERT(VARCHAR(20), @Now, 105)
PRINT CONVERT(VARCHAR(20), @Now, 106)
PRINT CONVERT(VARCHAR(20), @Now, 107)
PRINT CONVERT(VARCHAR(20), @Now, 108)
PRINT CONVERT(VARCHAR(20), @Now, 109)
PRINT CONVERT(VARCHAR(20), @Now, 110)
… 115
OUTPUT:
11/24/2016
2016.11.24
24/11/2016
24.11.2016
24-11-2016
24 Nov
2016
Nov 24,
2016
17:56:57
Nov 24
2016 5:56:57
11-24-2016
|
Differences on DATETIMEOFFSET, DATE, TIME, SMALLDATETIME, DATETIME SYSUTCDATETIME and SYSUTCDATE.
DATETIMEOFFSET
|
|||||||||||||||||||
Syntax
|
DATETIMEOFFSET [ (fractional seconds precision) ]
|
||||||||||||||||||
Usage
|
DECLARE
@MyDatetimeoffset datetimeoffset(7)
|
||||||||||||||||||
CREATE TABLE Table1 (
Column1 datetimeoffset(7) )
|
|||||||||||||||||||
Default string literal
formats (used for down-level client)
|
YYYY-MM-DD
hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]
|
||||||||||||||||||
For more information,
see the "Backward Compatibility for Down-level Clients" section
that follows.
|
|||||||||||||||||||
Date range
|
0001-01-01 through
9999-12-31
|
||||||||||||||||||
January 1, 1 CE
through December 31, 9999 CE
|
|||||||||||||||||||
Time range
|
00:00:00 through
23:59:59.9999999 (fractional seconds are not supported in Informatica)
|
||||||||||||||||||
Time zone offset range
|
-14:00 through +14:00
(the time zone offset is ignored in Informatica)
|
||||||||||||||||||
Element ranges
|
YYYY is four digits,
ranging from 0001 through 9999, that represent a year.
|
||||||||||||||||||
MM is two digits,
ranging from 01 to 12, that represent a month in the specified year.
|
|||||||||||||||||||
DD is two digits,
ranging from 01 to 31 depending on the month, that represent a day of the
specified month.
|
|||||||||||||||||||
hh is two digits,
ranging from 00 to 23, that represent the hour.
|
|||||||||||||||||||
mm is two digits,
ranging from 00 to 59, that represent the minute.
|
|||||||||||||||||||
ss is two digits,
ranging from 00 to 59, that represent the second.
|
|||||||||||||||||||
n* is zero to seven
digits, ranging from 0 to 9999999, that represent the fractional seconds.
Fractional seconds are not supported in Informatica.
|
|||||||||||||||||||
hh is two digits that
range from -14 to +14. The time zone offset is ignored in Informatica.
|
|||||||||||||||||||
mm is two digits that
range from 00 to 59. The time zone offset is ignored in Informatica.
|
|||||||||||||||||||
Character length
|
26 positions minimum
(YYYY-MM-DD hh:mm:ss {+|-}hh:mm) to 34 maximum (YYYY-MM-DD hh:mm:ss.nnnnnnn
{+|-}hh:mm)
|
||||||||||||||||||
Precision, scale
|
See the table below.
|
||||||||||||||||||
Storage size
|
10 bytes, fixed is the
default with the default of 100ns fractional second precision.
|
||||||||||||||||||
Accuracy
|
100 nanoseconds
|
||||||||||||||||||
Default value
|
1900-01-01 00:00:00
00:00
|
||||||||||||||||||
Calendar
|
Gregorian
|
||||||||||||||||||
User-defined
fractional second precision
|
Yes
|
||||||||||||||||||
Time zone offset aware
and preservation
|
Yes
|
||||||||||||||||||
Daylight saving aware
|
No
|
||||||||||||||||||
DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10.1236 +01:0';
DECLARE @time time(3) = @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset ',
@time AS 'time';
@datetimeoffset time
---------------------------------- ----------------
2025-12-10 12:32:10.1236 +01:00 12:32:10.124
DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10 +01:00';
DECLARE @date date= @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset ',
@date AS 'date';
@datetimeoffset date
---------------------------------- ----------
2025-12-10 12:32:10.0000 +01:00 2025-12-10
DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10 +01:00';
DECLARE @datetimeoffset1 datetimeoffset=
@datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset ',
@datetimeoffset1 AS '@datetimeoffset1';
@datetimeoffset @datetimeoffset1
---------------------------------- ----------------------------------
2025-12-10 12:32:10.0000 +01:00 2025-12-10 12:32:10.0000000 +01:00
DECLARE @date date= getdate();
DECLARE @datetimoffset datetimeoffset =
@date;
SELECT @date AS '@date ',
@datetimoffset AS '@datetimeoffset';
@date @datetimeoffset
---------- ----------------------------------
2016-11-25 2016-11-25 00:00:00.0000000 +00:00
SELECT
CAST('2007-05-08
12:35:29.1234567 +12:15' AS time(3)) AS 'time3'
,CAST('2007-05-08 12:35:29.1234567 +12:15' AS time) AS 'time'
,CAST('2007-05-08 12:35:29.1234567 +12:15' AS time(7)) AS 'time7'
,CAST('2007-05-08 12:35:29.1234567 +12:15' AS date) AS 'date'
,CAST('2007-05-08 12:35:29.123' AS
smalldatetime)
AS 'smalldatetime'
,CAST('2007-05-08 12:35:29.123' AS
datetime) AS 'datetime'
,CAST('2007-05-08 12:35:29.1234567+12:15' AS datetime2(7)) AS 'datetime2'
,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 'datetimeoffset'
,CAST('2007-05-08 12:35:29.1234567+12:15' AS datetimeoffset(7)) AS 'datetimeoffset IS08601';
Refer for more details
https://msdn.microsoft.com/en-us/library/bb630289.aspx
A.
Get the Current System Date and Time
SELECT SYSDATETIME()
,SYSDATETIMEOFFSET()
,SYSUTCDATETIME()
,CURRENT_TIMESTAMP
,GETDATE()
,GETUTCDATE();
/* Returned:
SYSDATETIME() 2007-05-08 13:10:02.0474381
SYSDATETIMEOFFSET() 2007-05-08 13:10:02.0474381 -07:00
SYSUTCDATETIME() 2007-05-08 07:50:02.0474381 in INDIA subtract 5:30 hrs
from current time
CURRENT_TIMESTAMP 2007-05-08
13:10:02.047
GETDATE() 2007-05-08 13:10:02.047
GETUTCDATE() 2007-05-08 07:50:02.047 in
INDIA subtract 5:30 hrs from current time
B.
Get the Current System Date
SELECT CONVERT (date, SYSDATETIME())
,CONVERT (date, SYSDATETIMEOFFSET())
,CONVERT (date, SYSUTCDATETIME())
,CONVERT (date, CURRENT_TIMESTAMP)
,CONVERT (date, GETDATE())
,CONVERT (date, GETUTCDATE());
/* Returned
SYSDATETIME() 2007-05-03
SYSDATETIMEOFFSET() 2007-05-03
SYSUTCDATETIME() 2007-05-03
in INDIA subtract 5:30 hrs from current time
CURRENT_TIMESTAMP 2007-05-03
GETDATE() 2007-05-03
GETUTCDATE() 2007-05-03
in INDIA subtract 5:30 hrs from
current time
*/
C.
Get the Current System Time
SELECT CONVERT (time, SYSDATETIME())
,CONVERT (time, SYSDATETIMEOFFSET())
,CONVERT (time, SYSUTCDATETIME())
,CONVERT (time, CURRENT_TIMESTAMP)
,CONVERT (time, GETDATE())
,CONVERT (time, GETUTCDATE());
/* Returned
SYSDATETIME() 13:18:45.3490361
SYSDATETIMEOFFSET() 13:18:45.3490361
SYSUTCDATETIME() 07:48:45.3490361 in INDIA subtract 5:30 hrs from current
time
CURRENT_TIMESTAMP 13:18:45.3470000
GETDATE() 13:18:45.3470000
GETUTCDATE() 07:48:45.3470000 in INDIA subtract 5:30 hrs
from current time
*/
|
|||||||||||||||||||
What
are the differences between CHAR and NCHAR [CHAR VS NCHAR]?
Which
data type should use for defining a column in a table (CHAR or NCHAR)?
CHAR
|
NCHAR
|
|||||||||||||
Bytes
|
It consumes 1 byte per character
|
It consumes 2 bytes per character
|
||||||||||||
Data Length
|
The maximum number of
characters for this data type can hold
8000 characters by defining as CHAR(8000)
|
The maximum number of
characters for this data type can hold
4000 characters by defining as NCHAR(4000)
|
||||||||||||
Data
Storage
Type
|
Used to store fixed
length character value as String
|
Used to store variable
length value as String
|
||||||||||||
Storage
|
Content is a fixed
size, does not have any UNICODE and not null, get better performance with
CHAR. Content is lesser than defined size, then content filled with spaces. (store
data with wasting the extra space)
|
Content is a fixed
size, has any UNICODE characters and NOT NULL, then content filled with
spaces. (store data with wasting the extra space)
|
||||||||||||
Use When?
|
Use CHAR data type
when content (data) is fixed size, does not have any UNICODE characters and
NOT NULL
Note:
UNICODE Example:
(Japanese, Korean, etc.)
UNICODE exactly
occupies
2 bytes.
|
Use NCHAR data type
when content (data) size is variable, has any UNICODE characters and /
or NULL
|
||||||||||||
Optional Parameter
value
|
The optional parameter value N [CHAR(N)] is not specified in the variable
declaration or column definition then it is considered as 1.
CHAR occupies 1 byte
DECLARE @Char CHAR = 'Char'
PRINT DATALENGTH(@Char)
OUTPUT: 1
|
The optional parameter value N [NCHAR(N)] is not specified in the variable
declaration or column definition then it is considered as 1.
NCHAR occupies 2 bytes
DECLARE @NChar NCHAR = 'NChar'
PRINT DATALENGTH(@NChar)
OUTPUT: 2
|
||||||||||||
Data Conversion
|
DECLARE @Name CHAR = 'Govindaraj Kanniappan'
SELECT CAST(@Name AS VARCHAR) Name,
DATALENGTH(CAST(@Name AS VARCHAR))
NameLength
|
DECLARE @Name VARCHAR = 'Govindaraj Kanniappan'
SELECT CAST(@Name AS NCHAR) Name,
DATALENGTH(CAST(@Name AS NCHAR)) NameLength
NOTE: VARCHAR default length is 30, while converting VARCHAR to NCHAR, so the NameLength is 60 |
||||||||||||
Example
|
Use DATALENGTH() - command would give you the difference of
both types by changing the data type FROM CHAR to NCHAR.
Example:
CREATE TABLE [dbo].[Test](
[City] [char](15) NULL,
[Street] [varchar](15) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Test] ([City], [Street]) VALUES (N'Delhi', N'Car Street')
GO
SELECT
DATALENGTH([City]) AS CharDataLength, DATALENGTH([Street]) AS
VarcharDataLength
FROM [Test]
Here, City content length is 5 but it is
stored to table as 15 lengths with 10 extra spaces. Street content length is
10 it is stored as it is without wasting any spaces
SELECT [City] + '|' AS City , [Street] + '|' AS Street
FROM [Test]
|
|||||||||||||
What are the differences between CHAR and VARCHAR [CHAR VS VARCHAR]?
Which
data type should use for defining a column in a table (CHAR or VARCHAR)?
CHAR
|
VARCHAR
|
|||||||||||||
Bytes
|
It consumes 1 byte per character
|
It consumes 1 byte per character
|
||||||||||||
Data Length
|
The maximum number of
characters for this data type can hold
8000 characters by defining as CHAR(8000)
|
The maximum number of
characters for this data type can hold up to 8000 characters by defining as
VARCHAR(8000) OR
unlimited by defining as VARCHAR(MAX)
|
||||||||||||
Data
Storage
Type
|
Used to store fixed
length character value as String
|
Used to store variable
length value as String
|
||||||||||||
Storage
|
Content is a fixed
size and not null, get better performance with CHAR. Content is lesser than
defined size, then content filled with spaces that the content/data does not
use up.
(store data with
wasting the extra space)
|
Content is dynamic
(Variable Length Size Variable) and null, manage storage space efficiently
with VARCHAR
(store data without
wasting the extra space)
|
||||||||||||
Use When?
|
Use CHAR data type
when content (data) is fixed size, does not have any UNICODE characters and
NOT NULL
Note:
Unicode Example:
(Japanese, Korean, etc.)
UNICODE exactly
occupies
2 bytes.
|
Use VARCHAR data type
when content (data) size is variable, does not have any UNICODE characters
and / or NULL
|
||||||||||||
Optional Parameter
value
|
The optional parameter value N [CHAR(N)] is not
specified in the variable declaration or column definition then it is
considered as 1.
CHAR occupies 1 byte
DECLARE @Char CHAR = 'Char'
PRINT DATALENGTH(@Char)
OUTPUT: 1
|
The optional parameter value N [VARCHAR(N)]
is not specified in the variable declaration or column definition then it is
considered as 1.
VARCHAR occupies 1
byte
DECLARE @VarChar varchar = 'VarChar'
PRINT DATALENGTH(@VarChar)
OUTPUT: 1
|
||||||||||||
Data Conversion
|
DECLARE @Name CHAR = 'Govindaraj Kanniappan'
SELECT CAST(@Name AS VARCHAR) Name,
DATALENGTH(CAST(@Name AS VARCHAR))
NameLength
|
DECLARE @Name VARCHAR = 'Govindaraj Kanniappan'
SELECT CAST(@Name AS CHAR) Name,
DATALENGTH(CAST(@Name AS CHAR)) NameLength
NOTE: VARCHAR default length is 30, while converting VARCHAR to CHAR, so the NameLength is 30 |
||||||||||||
Example
|
Use DATALENGTH() - command would give you the difference of
both types.
Example:
CREATE TABLE [dbo].[Test](
[City] [char](15) NULL,
[Street] [varchar](15) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Test] ([City], [Street]) VALUES (N'Delhi', N'Car Street')
GO
SELECT
DATALENGTH([City]) AS CharDataLength, DATALENGTH([Street]) AS
VarcharDataLength
FROM [Test]
Here, City content length is 5 but it is
stored to table as 15 lengths with 10 extra spaces. Street content length is
10 it is stored as it is without wasting any spaces
SELECT [City] + '|' AS City , [Street] + '|' AS Street
FROM [Test]
|
|||||||||||||
What are the differences between VARCHAR and NVARCHAR [VARCHAR VS NVARCHAR]?
Which
data type should use for defining a column in a table (VARCHAR VS NVARCHAR)?
VARCHAR
|
NVARCHAR
|
|||||||||||||
Bytes
|
It consumes 1 byte per character
|
It consumes 2 bytes per character
|
||||||||||||
Data Length
|
The maximum number of
characters for this data type can hold up to 8000 characters by defining as
VARCHAR(8000) OR
unlimited by defining as VARCHAR(MAX)
|
The maximum number of
characters for this data type can hold up to 4000 characters by defining as
NVARCHAR(4000) OR
unlimited by defining as NVARCHAR(MAX)
MAX indicates that the
maximum storage size is (2147483647) 2^31-1
bytes (2 GB). The storage size is the actual length of the data entered + 2
bytes.
|
||||||||||||
Data
Storage
Type
|
Used to store variable
length value as String
|
Used to store variable
length value as String
|
||||||||||||
Storage
|
Content is dynamic
(Variable Length Size Variable) and null, manage storage space efficiently
with VARCHAR.
|
Content is dynamic
(Variable Length Size Variable) , has UNICODE and null, manage storage space
efficiently with NVARCHAR.
|
||||||||||||
Use When?
|
Use VARCHAR data type
when content (data) size is variable, does not have any UNICODE characters
and / or NULL
VARCHAR exactly occupies
1 byte.
|
Use NVARCHAR data type
when content (data) size is variable,
has any UNICODE characters and / or
NULL
Note:
Unicode Example:
(Japanese, Korean,
etc.)
NVARCHAR exactly occupies
2 bytes.
|
||||||||||||
Optional Parameter
value
|
The optional parameter value N [VARCHAR(N)]
is not specified in the variable declaration or column definition then it is
considered as 1.
DECLARE @VarChar varchar = 'VarChar'
PRINT DATALENGTH(@VarChar)
OUTPUT: 1
|
The optional parameter value N [NVARCHAR(N)]
is not specified in the variable declaration or column definition then it is
considered as 2.
DECLARE @VarChar nvarchar = '¥arChar'
PRINT DATALENGTH(@VarChar)
OUTPUT: 2
|
||||||||||||
Data Conversion
|
DECLARE @Name VARCHAR(50) = 'Govindaraj Kanniappan'
SELECT CAST(@Name AS VARCHAR) Name,
DATALENGTH(CAST(@Name AS VARCHAR))
NameLength
|
DECLARE @Name NVARCHAR(50) = 'Govindaraj Kanniappan'
SELECT CAST(@Name AS NVARCHAR) Name,
DATALENGTH(CAST(@Name AS NVARCHAR))
NameLength
|
||||||||||||
Example
|
DECLARE @Name VARCHAR(MAX) = ''
DECLARE @Loop INT = 1
WHILE @loop <= 8000
BEGIN
SET
@Name += 'A'
SET
@Loop += 1
END
PRINT DATALENGTH(@Name)
OUTPUT: 8000
|
DECLARE @Name NVARCHAR(MAX) = ''
DECLARE @Loop INT = 1
WHILE @loop <= 8000
BEGIN
SET
@Name += 'A'
SET
@Loop += 1
END
PRINT DATALENGTH(@Name)
OUTPUT: 16000
|
||||||||||||
What are the differences between VARCHAR and VARCHAR(MAX) [VARCHAR VS VARCHAR(MAX)]?
Which
data type should use for defining a column in a table [VARCHAR VS
VARCHAR(MAX)]?
VARCHAR
|
VARCHAR(MAX)
|
|||||||||||||||
Bytes
|
It consumes 1 byte per character
|
It consumes 1 byte per character
|
||||||||||||||
Data Length
|
The maximum number of
characters for this data type can hold up to 8000 characters by defining as
VARCHAR(8000)
|
The maximum number of
characters for this data type can hold unlimited by defining as NVARCHAR(MAX)
MAX indicates that the
maximum storage size is (2147483647) 2^31-1 bytes (2 GB). The storage size is
the actual length of the data entered + 2 bytes
|
||||||||||||||
Data
Storage
Type
|
Used to store variable
length value as String
|
Used to store variable
length value as String
|
||||||||||||||
Index
|
Index can be created
on VARCHAR(N) data type if needed.
|
Index can’t be created
on VARCHAR(MAX) data type even though
it is needed. It throws the exception error when we create an index on this
data type
|
||||||||||||||
Storage
|
SQL server will use
the normal data pages to store the data i.e. it stores the value in
a row.
Content is dynamic
(Variable Length Size Variable) and null, manage storage space efficiently with
VARCHAR.
|
SQL server will use
the normal data pages to store the value in a row but if it could not
then it will store the value out of
row. i.e. It uses the normal data pages until the content actually fills
8k of data. When overflow happens on it, data is stored as old TEXT Data type
and a pointer is replacing the old content.
Does not allow UNICODE
characters and content is variable size for storing
|
||||||||||||||
Query for Checking
whether the data is stored in “in a
row” OR “out of row”.
SELECT alloc_unit_type_desc,
page_count
FROM
sys.dm_db_index_physical_stats
(DB_ID('TestDB'),
OBJECT_ID('dbo.TestVarMax'),
NULL,
NULL , 'DETAILED')
|
||||||||||||||||
Use When?
|
Use VARCHAR data type
when content (data) size is variable, does not have any UNICODE characters
and / or NULL and content length is lesser than or equal 8000 characters.
VARCHAR exactly occupies
1 byte.
|
Use VARCHAR(MAX) data
type when content (data) size is variable, has any UNICODE characters and /
or NULL and content length is greater
than or exceeds 8000 characters
Note:
Unicode Example:
(Japanese, Korean,
etc.)
NVARCHAR exactly occupies
2 bytes.
|
||||||||||||||
Optional Parameter
value
|
The optional parameter value N [VARCHAR(N)]
is not specified in the variable declaration or column definition then it is
considered as 1.
DECLARE @VarChar varchar = 'VarChar'
PRINT DATALENGTH(@VarChar)
OUTPUT: 1
|
The optional parameter value MAX [VARCHAR(MAX)]
is not specified in the variable declaration or column definition then it is
considered as 1.
DECLARE @VarChar varchar = 'VarChar'
PRINT DATALENGTH(@VarChar)
OUTPUT: 1
|
||||||||||||||
Data Conversion
|
DECLARE @Name VARCHAR(50) = 'Govindaraj Kanniappan'
SELECT CAST(@Name AS VARCHAR) Name,
DATALENGTH(CAST(@Name AS VARCHAR))
NameLength
|
DECLARE @Name VARCHAR(MAX) = 'Govindaraj Kanniappan'
SELECT CAST(@Name AS VARCHAR(MAX)) Name,
DATALENGTH(CAST(@Name AS VARCHAR(MAX)))
NameLength
|
||||||||||||||
Example
|
DECLARE @Name VARCHAR(8000) = ''
DECLARE @Loop INT = 1
WHILE @loop <= 19000
BEGIN
SET
@Name += 'A'
SET
@Loop += 1
END
PRINT DATALENGTH(@Name)
OUTPUT: 8000
|
DECLARE @Name NVARCHAR(MAX) = ''
DECLARE @Loop INT = 1
WHILE @loop <= 19000
BEGIN
SET
@Name += 'A'
SET
@Loop += 1
END
PRINT DATALENGTH(@Name)
OUTPUT: 38000
|
||||||||||||||
Performance
|
VARCHAR(N) gives
better performance result compared to VARCHAR(MAX). Please try with the following examples and
get the results for changing the data type from VARCHAR(N) to VARCHAR(MAX).
DECLARE @FirstName VARCHAR(50),
@Loop INT
= 0,
@StartTime DATETIME
= GETDATE()
WHILE(@Loop <
1000000)
BEGIN
SELECT
@FirstName = 'Govindaraj
Kanniappan', @Loop += 1
END
SELECT DATEDIFF(ms,@StartTime,GETDATE()) 'Time Taken in ms'
GO 10
-- GO
Statement can also be used to excute batch of T-Sql statement multiple times
|
|||||||||||||||
What
are the differences between VARCHAR and VARCHAR(MAX) [VARCHAR VS VARCHAR(MAX)]
Which
data type should use for defining a column in a table [VARCHAR VS VARCHAR(MAX)]?
VARCHAR
|
VARCHAR(MAX)
|
|||||||||||||||
Bytes
|
It consumes 1 byte per character
|
It consumes 1 byte per character
|
||||||||||||||
Data Length
|
The maximum number of
characters for this data type can hold up to 8000 characters by defining as
VARCHAR(8000)
|
The maximum number of
characters for this data type can hold unlimited by defining as NVARCHAR(MAX)
MAX indicates that the
maximum storage size is (2147483647) 2^31-1 bytes (2 GB). The storage size is
the actual length of the data entered + 2 bytes
|
||||||||||||||
Data
Storage
Type
|
Used to store variable
length value as String
|
Used to store variable
length value as String
|
||||||||||||||
Index
|
Index can be created
on VARCHAR(N) data type if needed.
|
Index can’t be created
on VARCHAR(MAX) data type even though
it is needed. It throws the exception error when we create an index on this
data type
|
||||||||||||||
Storage
|
SQL server will use
the normal data pages to store the data i.e. it stores the value in
a row.
Content is dynamic
(Variable Length Size Variable) and null, manage storage space efficiently
with VARCHAR.
|
SQL server will use
the normal data pages to store the value in a row but if it could not
then it will store the value out of
row. i.e. It uses the normal data pages until the content actually fills
8k of data. When overflow happens on it, data is stored as old TEXT Data type
and a pointer is replacing the old content.
Does not allow UNICODE
characters and content is variable size for storing
|
||||||||||||||
Query for Checking
whether the data is stored in “in a
row” OR “out of row”.
SELECT alloc_unit_type_desc,
page_count
FROM
sys.dm_db_index_physical_stats
(DB_ID('TestDB'),
OBJECT_ID('dbo.TestVarMax'),
NULL,
NULL , 'DETAILED')
|
||||||||||||||||
Use When?
|
Use VARCHAR data type
when content (data) size is variable, does not have any UNICODE characters
and / or NULL and content length is lesser than or equal 8000 characters.
VARCHAR exactly occupies
1 byte.
|
Use VARCHAR(MAX) data
type when content (data) size is variable, has any UNICODE characters and /
or NULL and content length is greater
than or exceeds 8000 characters
Note:
Unicode Example:
(Japanese, Korean,
etc.)
NVARCHAR exactly occupies
2 bytes.
|
||||||||||||||
Optional Parameter
value
|
The optional parameter value N [VARCHAR(N)]
is not specified in the variable declaration or column definition then it is
considered as 1.
DECLARE @VarChar varchar = 'VarChar'
PRINT DATALENGTH(@VarChar)
OUTPUT: 1
|
The optional parameter value MAX [VARCHAR(MAX)]
is not specified in the variable declaration or column definition then it is
considered as 1.
DECLARE @VarChar varchar = 'VarChar'
PRINT DATALENGTH(@VarChar)
OUTPUT: 1
|
||||||||||||||
Data Conversion
|
DECLARE @Name VARCHAR(50) = 'Govindaraj Kanniappan'
SELECT CAST(@Name AS VARCHAR) Name,
DATALENGTH(CAST(@Name AS VARCHAR))
NameLength
|
DECLARE @Name VARCHAR(MAX) = 'Govindaraj Kanniappan'
SELECT CAST(@Name AS VARCHAR(MAX)) Name,
DATALENGTH(CAST(@Name AS VARCHAR(MAX)))
NameLength
|
||||||||||||||
Example
|
DECLARE @Name VARCHAR(8000) = ''
DECLARE @Loop INT = 1
WHILE @loop <= 19000
BEGIN
SET
@Name += 'A'
SET
@Loop += 1
END
PRINT DATALENGTH(@Name)
OUTPUT: 8000
|
DECLARE @Name NVARCHAR(MAX) = ''
DECLARE @Loop INT = 1
WHILE @loop <= 19000
BEGIN
SET
@Name += 'A'
SET
@Loop += 1
END
PRINT DATALENGTH(@Name)
OUTPUT: 38000
|
||||||||||||||
Performance
|
VARCHAR(N) gives
better performance result compared to VARCHAR(MAX). Please try with the following examples and
get the results for changing the data type from VARCHAR(N) to VARCHAR(MAX).
DECLARE @FirstName VARCHAR(50),
@Loop INT
= 0,
@StartTime DATETIME
= GETDATE()
WHILE(@Loop <
1000000)
BEGIN
SELECT
@FirstName = 'Govindaraj
Kanniappan', @Loop += 1
END
SELECT DATEDIFF(ms,@StartTime,GETDATE()) 'Time Taken in ms'
GO 10
-- GO
Statement can also be used to excute batch of T-Sql statement multiple times
|
|||||||||||||||
What
are the differences between TEXT and VARCHAR(MAX) [TEXT VS VARCHAR(MAX)]
Which
data type should use for defining a column in a table [TEXT VS VARCHAR(MAX)]?
TEXT
|
VARCHAR(MAX)
|
||||||||||
Bytes
|
It consumes 1 byte per character
It is present since
SQL Server 6.5 version.
|
It consumes 1 byte per character
It is introduced in
SQL Server 2005
|
|||||||||
Data Length
|
The maximum number of NON
UNCODE characters for this data type can hold unlimited by defining as TEXT.
Maximum storage size
is (2147483647) 2^31-1 bytes (2 GB).
|
The maximum number of NON
UNICODE characters for this data type can hold unlimited by defining as
NVARCHAR(MAX)
MAX indicates that the
maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual
length of the data entered + 2 bytes
|
|||||||||
Data
Storage
Type
|
Used to store variable
length value as String
|
Used to store variable
length value as String
|
|||||||||
Index
|
Index can’t be created
on TEXT data type if needed. It throws the exception error when we create an
index on this data type
|
Index can’t be created
on VARCHAR(MAX) data type even though
it is needed. It throws the exception error when we create an index on this
data type
|
|||||||||
Storage
|
SQL server will use the
value of TEXT data type that is stored in out of row in a separate LOB (Large Object) data pages. The row in the data table will only have
16 bytes pointer to LOB data pages where the actual data is stored.
Does not allow UNICODE
characters and content is variable size for storing
Note:
Unicode Example:
(Japanese, Korean,
etc.)
NVARCHAR exactly occupies
2 bytes.
Changing Text Data Type Default
Storage Behavior
EXEC sp_tableoption
@TableNamePattern = 'dbo.Employee',
@OptionName =
'text in row',
@OptionValue =
7000
The @OptionValue
parameter value can be:
0/OFF (Default Value): Text
type column value is stored out-of-row
1/ON: Text Type Column
value is stored in-row as long as the Text type column value is less than or
equal to 256 bytes
Integer value from 24
through
7000: specifies the
number bytes up to which the text type column value is stored in row.
|
SQL server will store
the value of VARCHAR(MAX) in a row if it more than 8000 bytes/characters then it
will store the value out of row.
i.e. It uses the
normal data pages until the content actually fills 8k of data. When overflow
happens on it, data would be stored as old TEXT Data type and a pointer is
replacing the old content.
Does not allow UNICODE
characters and content is variable size for storing
Changing Text Data Type Default
Storage Behavior
EXEC sp_tableoption
@TableNamePattern = 'dbo.Employee',
@OptionName =
'text out of row',
@OptionValue =
1
The @OptionValue parameter value can
be:
0/OFF (Default) : Varchar(Max) column
values are stored in-row as long as the value length is less than or equal to 8000 bytes and enough
space is available in row.
1/ON : VARCHAR(MAX) column values are
always stored out-of-row even though when enough space is available in row.
|
|||||||||
Query for Checking
whether the data is stored in “in a
row” OR “out of row”.
|
SELECT alloc_unit_type_desc,
page_count
FROM
sys.dm_db_index_physical_stats
(DB_ID('TestDB'),
OBJECT_ID('dbo.Employee_Text'),
NULL,
NULL , 'DETAILED')
|
SELECT alloc_unit_type_desc,
page_count
FROM
sys.dm_db_index_physical_stats
(DB_ID('TestDB'),
OBJECT_ID('dbo.TestVarMax'),
NULL,
NULL , 'DETAILED')
|
|||||||||
Use When?
|
Use TEXT data type
when content (data) size is variable, does not have any UNICODE characters and
/ or NULL and content length is exceeds 8000 bytes/characters.
NOTE:
Microsoft is
suggesting to use VARCHAR(MAX) instead
of Text data type for storing the
large amount of data in a single column as it will be removed in a future versions of MS
SQL Server.
|
Use VARCHAR(MAX) data
type when content (data) size is variable, does not have any UNICODE
characters and / or NULL and content length is exceeds 8000 bytes/characters.
|
|||||||||
Optional Parameter
value
|
There is no optional parameter
value for TEXT data type.
|
The optional parameter value MAX [VARCHAR(MAX)]
is not specified in the variable declaration or column definition then it is
considered as 1.
DECLARE @VarChar varchar = 'VarChar'
PRINT DATALENGTH(@VarChar)
OUTPUT: 1
|
|||||||||
Data Conversion
|
For read, write or
update TEXT data using the SELECT, INSERT or UPDATE statements and special
statements like READTEXT, WRITETEXT and UPDATETEXT also can be
used
|
You can
directly read, write or update VARCHAR(MAX) data using the SELECT, INSERT or
UPDATE statements no special statements READTEXT,
WRITETEXT and UPDATETEXT can be used
|
|||||||||
Example
|
USE Pubs database
to perform the following examples.
CREATE TABLE [dbo].[Employee_Text](
[EmployeeID] [int] NOT NULL,
[EmployeeName] [varchar](50) NULL,
[EmployeeDetail] Text NULL,
CONSTRAINT
[PK_Employee_Text_EmployeeID] PRIMARY KEY CLUSTERED
(
[EmployeeID] 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
INSERT INTO dbo.Employee_Text VALUES
(1, 'Govindaraj
Kanniappan', 'Govindaraj
Kanniappan text details'),
(2,'Govindaraj
Anusha', 'Govindaraj
Anusha varchar text details')
SELECT alloc_unit_type_desc,
page_count
FROM
sys.dm_db_index_physical_stats
(DB_ID('TestDB'),
OBJECT_ID('dbo.Employee_Text'),
NULL, NULL , 'DETAILED')
SELECT EmployeeName,
EmployeeDetail FROM dbo.Employee_Text WITH (NOLOCK)
UPDATE dbo.Employee_Text SET EmployeeDetail =
'This is text data .....' WHERE EmployeeID =
2
DELETE dbo.Employee_Text WHERE
EmployeeDetail =
'This is text data .....'
Operator = and GROUP BY can’t be
used in TEXT data type, see the error below on DELETE
Error:
Msg 402, Level 16, State 1, Line 1
The data types text and varchar are
incompatible in the equal to operator.
Reads the second
through twenty-sixth characters of the pr_info column in the pub_info table
DECLARE @ptrval varbinary(16);
SELECT @ptrval = TEXTPTR(pr_info)
FROM
pub_info pr INNER JOIN
publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @ptrval 1
25;
Puts the text
pointer into the local variable @ptrval, and then WRITETEXT places the new
text string into the row pointed to by @ptrval.
DECLARE @ptrval binary(16);
SELECT @ptrval = TEXTPTR(pr_info)
FROM pub_info pr, publishers
p
WHERE p.pub_id = pr.pub_id
AND p.pub_name = 'New Moon Books'
WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) has just released another top ten
publication.';
GO
Puts the text pointer into the local
variable @ptrval, and then uses UPDATETEXT to update a spelling error
DECLARE @ptrval binary(16);
SELECT @ptrval = TEXTPTR(pr_info)
FROM
pub_info pr, publishers p
WHERE p.pub_id = pr.pub_id
AND p.pub_name = 'New Moon Books'
UPDATETEXT pub_info.pr_info @ptrval
88 1 'b';
|
CREATE TABLE [dbo].[Employee_VarMax](
[EmployeeID] [int] NOT NULL,
[EmployeeName] [varchar](50) NULL,
[EmployeeDetail] [varchar](max) NULL,
CONSTRAINT
[PK_Employee_VarMax_EmployeeID] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO dbo.Employee_VarMax VALUES
(1, 'Govindaraj
Kanniappan', 'Govindaraj
Kanniappan varchar max details'),
(2,'Govindaraj
Anusha', 'Govindaraj
Anusha varchar max details')
-- Read Varchar
Max data
SELECT EmployeeName,
EmployeeDetail FROM dbo.Employee_VarMax WITH (NOLOCK)
-- Update
Varchar Max data
UPDATE dbo.Employee_VarMax SET EmployeeDetail =
'This is varchar Max data .....' WHERE EmployeeID =
2
-- Delete
using Varchar Max data
DELETE dbo.Employee_VarMax WHERE
EmployeeDetail = 'This
is varchar Max data .....'
|
|||||||||
Performance
|
The VARCHAR(MAX) data
type is a replacement for TEXT data type.
LIKE statement is
identical between the two data types.
Additional
functionality = and GROUP BY can’t be used in TEXT data type but it can be on
VARCHAR(MAX) data type
|
||||||||||
What
are the differences between BINAY and VARBINAY [BINARY VS VARBINARY]?
Which
data type should use for defining a column in a table [BINAY VS VARBINARY]?
BINARY
|
VARBINARY
|
||
Bytes
|
It consumes 1 byte per character
|
It consumes 1 byte per character
It is introduced in
SQL Server 2005
|
|
Data Length
|
Maximum storage size
is (2147483647) 2^31-1 bytes (2 GB).
|
MAX indicates that the
maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual
length of the data entered + 2 bytes
|
|
Data
Storage
Type
|
IMAGE is used to only
used to store the image files (BMP, TIFF, GIF, or JPEG format files)
|
VARBINARY(MAX) data
type is used to store images/pdf/word etc files
|
|
Index
|
Index can’t be created
on IMAGE data type if needed. It throws the exception error when we create an
index on this data type
|
Index can’t be created
on VARBINARY (MAX) data type even
though it is needed. It throws the exception error when we create an index on
this data type
|
|
Use When?
|
Use IMAGE data type
when only used to store the image files
NOTE:
Microsoft is
suggesting to use VARBINARY(MAX) instead of IMAGE data type for storing the large amount
of data in a single column as it will
be removed in a future versions of MS SQL Server.
|
Use VARBINARY(MAX)
data type is used to store images/pdf/word etc files.
|
|
The VARBINAY(MAX) data
type is a replacement for IMAGE data type.
|
|||
What
are the differences between IMAGE and VARBINAY(MAX) [IMAGE VS VARBINARY(MAX)]
Which
data type should use for defining a column in a table [IMAGE VS
VARBINARY(MAX)]?
IMAGE
|
VARBINARY(MAX)
|
||
Bytes
|
It consumes 1 byte per character
|
It consumes 1 byte per character
It is introduced in
SQL Server 2005
|
|
Data Length
|
Maximum storage size
is (2147483647) 2^31-1 bytes (2 GB).
|
MAX indicates that the
maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual
length of the data entered + 2 bytes
|
|
Data
Storage
Type
|
IMAGE is used to only
used to store the image files (BMP, TIFF, GIF, or JPEG format files)
|
VARBINARY(MAX) data
type is used to store images/pdf/word etc files
|
|
Index
|
Index can’t be created
on IMAGE data type if needed. It throws the exception error when we create an
index on this data type
|
Index can’t be created
on VARBINARY (MAX) data type even
though it is needed. It throws the exception error when we create an index on
this data type
|
|
Use When?
|
Use IMAGE data type
when only used to store the image files
NOTE:
Microsoft is
suggesting to use VARBINARY(MAX) instead of IMAGE data type for storing the large amount
of data in a single column as it will
be removed in a future versions of MS SQL Server.
|
Use VARBINARY(MAX)
data type is used to store images/pdf/word etc files.
|
|
The VARBINAY(MAX) data
type is a replacement for IMAGE data type.
|
|||
Data Type CAST and CONVERT (Transact-SQL)
It is used for convert an expression of one data type to
another.
Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function.
Explicit conversions are those
conversions that require the CAST or CONVERT function to be specified.
CAST
|
CONVERT
|
It is ANSII Standard.
|
It is Specific to SQL SERVER.
|
It can’t be used for Formatting Purposes.
For example
PRINT CAST('12/01/2016' AS DATE)
OUTPUT:
2016-12-01
Here, By default it produces result ISO8601
standard.
|
It can be used for Formatting Purposes by passing the
appropriate style as parameter.
For example
PRINT CONVERT(VARCHAR(20), GETDATE(), 101)
OUTPUT:
12/01/2016
Here, We are able to pass style 101 for producing the result
as US standard.
|
The following chart shows all explicit
and implicit data type conversions that are allowed for SQL Server
system-supplied data types. These include xml, bigint, and sql_variant. There
is no implicit conversion on assignment from the sql_variant data type, but
there is implicit conversion to sql_variant.
No comments:
Post a Comment