USE master
USE master
EXEC xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\80\registration',
'CD_KEY'
SELECT FKConstraintName = a.CONSTRAINT_NAME,
FKTable = b.TABLE_NAME,
FKColumn = c.COLUMN_NAME,
PKTable = d.TABLE_NAME,
PKColumn = e.COLUMN_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
ON a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS d
ON a.UNIQUE_CONSTRAINT_NAME = d.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
ON a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
INNER JOIN
(
SELECT
f.TABLE_NAME, g.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS f
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE g
ON f.CONSTRAINT_NAME = g.CONSTRAINT_NAME
WHERE f.CONSTRAINT_TYPE = 'PRIMARY KEY'
) e
ON e.TABLE_NAME = d.TABLE_NAME
ORDER BY a.CONSTRAINT_NAME
| DATE FORMATS | ||
| Format # | Query (current date: 12/30/2006) | Sample |
| 1 | select convert(varchar, getdate(), 1) | 12/30/06 |
| 2 | select convert(varchar, getdate(), 2) | 06.12.30 |
| 3 | select convert(varchar, getdate(), 3) | 30/12/06 |
| 4 | select convert(varchar, getdate(), 4) | 30.12.06 |
| 5 | select convert(varchar, getdate(), 5) | 30-12-06 |
| 6 | select convert(varchar, getdate(), 6) | 30 Dec 06 |
| 7 | select convert(varchar, getdate(), 7) | Dec 30, 06 |
| 10 | select convert(varchar, getdate(), 10) | 12-30-06 |
| 11 | select convert(varchar, getdate(), 11) | 06/12/30 |
| 101 | select convert(varchar, getdate(), 101) | 12/30/2006 |
| 102 | select convert(varchar, getdate(), 102) | 2006.12.30 |
| 103 | select convert(varchar, getdate(), 103) | 30/12/2006 |
| 104 | select convert(varchar, getdate(), 104) | 30.12.2006 |
| 105 | select convert(varchar, getdate(), 105) | 30-12-2006 |
| 106 | select convert(varchar, getdate(), 106) | 30 Dec 2006 |
| 107 | select convert(varchar, getdate(), 107) | Dec 30, 2006 |
| 110 | select convert(varchar, getdate(), 110) | 12-30-2006 |
| 111 | select convert(varchar, getdate(), 111) | 2006/12/30 |
| TIME FORMATS | ||
| 8 or 108 | select convert(varchar, getdate(), 8) | 00:38:54 |
| 9 or 109 | select convert(varchar, getdate(), 9) | Dec 30 2006 12:38:54:840AM |
| 14 or 114 | select convert(varchar, getdate(), 14) | 00:38:54:840 |
| Data Type | Syntax | Description |
| integer | integer | Integer number. |
| smallint | smallint | Small integer number. |
| numeric | numeric(p,s) | Where p is a precision value; s is a scale value. For example, numeric(7,3) is a number that has 4 digits before the decimal and 3 digits after the decimal. |
| decimal | decimal(p,s) | Where p is a precision value; s is a scale value. |
| real | real | Single-precision floating point number |
| double precision | double precision | Double-precision floating point number |
| float | float(p) | Where p is a precision value. |
| character | char(x) | Where x is the number of characters to store. This data type is space padded to fill the number of characters specified. |
| character varying | varchar2(x) | Where x is the number of characters to store. This data type does NOT space pad. |
| bit | bit(x) | Where x is the number of bits to store. |
| bit varying | bit varying(x) | Where x is the number of bits to store. The length can vary up to x. |
| date | date | Stores year, month, and day values. |
| time | time | Stores the hour, minute, and second values. |
| timestamp | timestamp | Stores year, month, day, hour, minute, and second values. |
| time with time zone | time with time zone | Exactly the same as time, but also stores an offset from UTC of the time specified. |
| timestamp with time zone | timestamp with time zone | Exactly the same as timestamp, but also stores an offset from UTC of the time specified. |
DataType
|
Storage Size (Bytes)
|
Values
|
Comment
|
Tinyint
|
1
|
0 to 255
| |
Smallint
|
2
|
-2 x to 2 x - 1
(X: 15)
| |
Int
|
4
|
-2 x to 2 x - 1
(X: 31)
| |
Bigint
|
8
|
-2 x to 2 x - 1
(X: 63)
| |
Numeric
|
5 to 17
| ||
Datetime
|
8
|
01/01/1753 to 12/31/9999
|
Accuracy: 3 miliseconds
|
smalldatetime
|
4
|
01/01/1900 to 06/06/2079
|
Accuracy: 1 minute
|
Datetime2
|
6 to 8
|
01/01/0001 to 12/31/9999
|
Accuracy: 100 nanoseconds
|
datetimeoffset
|
8 to 10
|
01/01/0001 to 12/31/9999
|
Accuracy: 100 nanoseconds
|
Date
|
3
|
01/01/0001 to 12/31/9999
|
Accuracy: 1 Day
|
Time
|
3 to 5
|
00:00:00.0000000 to
23:59:59.9999999
|
Accuracy: 100 nanoseconds
|
Char
|
As specified
|
Only english characters are allowed
| |
Text
|
generally not used now
| ||
Varchar
|
as specified
max 2 GB |
Only english characters are allowed
| |
Nchar
|
As specified
(double the char) |
Non-english characters are allowed
| |
Ntext
|
generally not used now
| ||
Nvarchar
|
as specified
(double the varchar) max: upto 2 GB |
Can store non-english characters
| |
Binary
|
As specified
| ||
Varbinary
|
Variable As specified
max: upto 2 GB |
FILESTREAM can be used with VARBINARY(MAX)
| |
Image
|
16
|
generally not used now
| |
Cursor
| |||
Hierarchyid
|
892 bytes
|
Binary number maintains the
hierarchy | |
sql_variant
| |||
timestamp/
rowversion |
nullable : varbinary(8)
non-nullable: binary(8) |
Unique Binary number
throught database | |
uniqueidentifier
|
16
|
NEWID function can be used
| |
Xml
|
max: upto 2 GB
|
Maximum 128 levels
| |
Geography
|
max: upto 2 GB
| ||
Geometry
|
max: upto 2 GB
|
Datatype
|
Min
|
Max
|
Storage
|
Type
|
Notes
|
Bigint
|
-2^63
|
2^63-1
|
8 bytes
|
Exact numeric
| |
Int
|
-2,147,483,648
|
2,147,483,647
|
4 bytes
|
Exact numeric
| |
Smallint
|
-32,768
|
32,767
|
2 bytes
|
Exact numeric
| |
Tinyint
|
0
|
255
|
1 bytes
|
Exact numeric
| |
Bit
|
0
|
1
|
1 to 8 bit columns in the same table requires a total of 1 byte, 9 to 16 bits = 2 bytes, etc...
|
Exact numeric
| |
Decimal
|
-10^38+1
|
10^38–1
|
Precision 1-9 = 5 bytes, precision 10-19 = 9 bytes, precision 20-28 = 13 bytes, precision 29-38 = 17 bytes
|
Exact numeric
|
Decimal and numeric data type is exactly the same. Precision is the total number of digits. Scale is the number of decimals. For both the minimum is 1 and the maximum is 38.
|
Numeric
|
no
| ||||
Money
|
-2^63 / 10000
|
2^63-1 / 10000
|
8 bytes
|
Exact numeric
| |
Smallmoney
|
-214,748.3648
|
214,748.3647
|
4 bytes
|
Exact numeric
| |
Float
|
-1.79E + 308
|
1.79E + 308
|
4 bytes when precision is less than 25 and 8 bytes when precision is 25 through 53
|
Approximate numeric
|
Precision is specified from 1 to 53.
|
Real
|
-3.40E + 38
|
3.40E + 38
|
4 bytes
|
Approximate numeric
|
Precision is fixed to 7.
|
Datetime
|
1753-01-01 00:00:00.000
|
9999-12-31 23:59:59.997
|
8 bytes
|
Date and time
|
If you are running SQL Server 2008 and need milliseconds precision, use datetime2(3) instead to save 1 byte.
|
Smalldatetime
|
1900-01-01 00:00
|
2079-06-06 23:59
|
Date and time
| ||
Date
(SQL2008)
|
0001-01-01
|
9999-12-31
|
Date and time
| ||
Time
(SQL2008)
|
00:00:00.0000000
|
23:59:59.9999999
|
Date and time
|
Specifying the precision is possible. TIME(3) will have milliseconds precision. TIME(7) is the highest and the default precision. Casting values to a lower precision will round the value.
| |
Datetime2
(SQL2008)
|
0001-01-01 00:00:00.0000000
|
9999-12-31 23:59:59.9999999
|
Presicion 1-2 = 6 bytes precision 3-4 = 7 bytes precision 5-7 = 8 bytes
|
Date and time
|
Combines the date data type and the time data type into one. The precision logic is the same as for the time data type.
|
Datetimeoffset
(SQL2008)
|
0001-01-01 00:00:00.0000000 -14:00
|
9999-12-31 23:59:59.9999999 +14:00
|
Presicion 1-2 = 8 bytes precision 3-4 = 9 bytes precision 5-7 = 10 bytes
|
Date and time
|
Is a datetime2 data type with the UTC offset appended.
|
Char
|
0 chars
|
8000 chars
|
Defined width
|
Character string
|
Fixed width
|
Varchar
|
0 chars
|
8000 chars
|
2 bytes + number of chars
|
Character string
|
Variable width
|
Varchar(max)
(SQL2005/2008)
|
0 chars
|
2^31 chars
|
2 bytes + number of chars
|
Character string
|
Variable width
|
Text
|
0 chars
|
2,147,483,647 chars
|
4 bytes + number of chars
|
Character string
|
Variable width
|
Nchar
|
0 chars
|
4000 chars
|
Defined width x 2
|
Unicode character string
|
Fixed width
|
Nvarchar
|
0 chars
|
4000 chars
|
Unicode character string
|
Variable width
| |
Nvarchar(max)
(SQL2005/2008)
|
0 chars
|
2^30 chars
|
Unicode character string
|
Variable width
| |
Ntext
|
0 chars
|
1,073,741,823 chars
|
Unicode character string
|
Variable width
| |
Binary
|
0 bytes
|
8000 bytes
|
Binary string
|
Fixed width
| |
Varbinary
|
0 bytes
|
8000 bytes
|
Binary string
|
Variable width
| |
Varbinary(max)
(SQL2005/2008)
|
0 bytes
|
2^31 bytes
|
Binary string
|
Variable width
| |
Image
|
0 bytes
|
2,147,483,647 bytes
|
Binary string
|
Variable width
| |
Sql_variant
|
Other
|
Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
| |||
Timestamp
|
Other
|
Stores a database-wide unique number that gets updated every time a row gets updated.
| |||
Uniqueidentifier
|
Other
|
Stores a globally unique identifier (GUID).
| |||
Xml
(SQL2005/2008)
|
Other
|
Stores XML data. You can store xml instances in a column or a variable.
| |||
Cursor
|
Other
|
A reference to a cursor.
| |||
Table
|
Other
|
Stores a result set for later processing.
|
Options
|
DATETIME
|
DATETIME2[(n)]
|
Min Value
|
1753-01-01 00:00:00
|
0001-01-01 00:00:00
|
Max Value
|
9999-12-31 23:59:59.997
|
9999-12-31 23:59:59.9999999
|
Storage Size
|
8 Bytes
|
6 to 8 bytes
Parameter n is optional and if it is not specified then fractional seconds precision is 7 digit and it can be from 0 to 7 digit. For fractional seconds precision <3, takes 6 bytes For fractional seconds precision 3 or 4 it will take 7 bytes For fractional seconds precision >4 it will take 8 bytes |
How to use it
|
DECLARE @param DATETIME
|
DECLARE @param DATETIME2(7)
|
Compliance
|
Is not an ANSI/ISO compliant
|
Is an ANSI/ISO compliant
|
Function Used
|
GETDATE() – It returns DB Current Date and Time of DateTime Data Type
|
SYSDATETIME()- It returns DB Current Date and Time of DateTime2 Data Type
Example: SELECT SYSDATETIME() |
+/- of (days)
|
WORKS
Example: DECLARE @nowDateTime DATETIME = GETDATE() SELECT @nowDateTime + 1 Result: 2011-09-17 13:44:31.247 |
FAILS – Need to use only DATEADD function
Example: DECLARE @nowDateTime2 DATETIME2= SYSDATETIME() SELECT @nowDateTime2+1 Result: Msg 206, Level 16, State 2, Line 2 Operand type clash: datetime2 is incompatible with int |
To provide a real support of hierarchies, SQL Server 2008 introduces a new type of data: HierarchyID.
It is a managed type (.NET), handled by the SQLCLR of SQL Server. It not only stores the parent elements but also stores the set of information to identify the entire hierarchy.
My main purpose of this article to convert our existing table named "my_EMP" to newly created table which contains data type HierarchyID.
We are taking a new columns named "OrgNode" to understand the migration properly. So let's create the new table.
CREATE TABLE My_EEMP_Converted
(
OrgNode hierarchyid,
empid int,
empname varchar(50),
managerid int
CONSTRAINT PK_My_EEMP_Converted
PRIMARY KEY CLUSTERED (OrgNode)
);
Now create a temporary table in the name of "#children" to contain columns name "Num" that contains the number of children's in each node. Create index on it.
CREATE TABLE #Children
(
empid int,
managerid int,
Num int
);
GO
CREATE CLUSTERED INDEX tmpind ON #Children(managerid, empid);
Now populate the temporary table. Here ROW_NUMBER function is used to populate the "Num" columns. I know you have idea on it.
INSERT #Children (empid, managerid, Num)
SELECT empid, managerid,
ROW_NUMBER() OVER (PARTITION BY managerid ORDER BY managerid)
FROM my_EMP
GO
Now review the child table.
SELECT * FROM #Children ORDER BY ManagerID, Num
The desired result set is displayed.
empid managerid Num
1 NULL 1
2 1 1
3 2 1
4 2 2
Now populate the table "My_EEMP_Converted"
WITH paths(path, empid)
AS
(
-- This section provides the value for the root of the hierarchy
SELECT hierarchyid::GetRoot() AS OrgNode, empid
FROM #Children C
WHERE ManagerID IS NULL
UNION ALL
-- This section provides values for all nodes except the root
SELECT CAST(CONVERT(varchar,p.path)
+ CONVERT(varchar(30),C.Num)
+ '/' AS hierarchyid),
C.empid
FROM #Children C
INNER JOIN paths p ON C.managerid = p.empid
)
INSERT My_EEMP_Converted (OrgNode, O.empid, O.empname, O.managerid)
SELECT P.path, O.empid, O.empname, O.managerid
FROM my_EMP AS O
INNER JOIN Paths AS P ON O.empid = P.empid
GO
SELECT * FROM My_EEMP_Converted
To more understanding of "OrgNode" convert it to string.
SELECT CONVERT(VARCHAR,OrgNode)OrgNode , empid, empname, managerid
FROM My_EEMP_Converted
Now drop the temp table
DROP TABLE #Children
GO
To help queries at the same level in the hierarchy, use the Get Level method to create a computed column that contains the level in the hierarchy. Create compound index on level and the " Hierarchyid".
ALTER TABLE My_EEMP_Converted
ADD H_Level AS OrgNode.GetLevel()
CREATE UNIQUE INDEX My_EEMP_Converted_idx
ON My_EEMP_Converted(H_Level, OrgNode)
GO
SELECT * FROM My_EEMP_Converted
Now create unique index of empid.
Complete view
SELECT OrgNode, CONVERT(varchar,OrgNode) logical_level,
empid, empname, managerid, H_Level
FROM My_EEMP_Converted
OrgNode logical_level empid empname managerid H_Level
0x / 1 Sudip Das NULL 0
0x58 /1/ 2 Joydeep Das 1 1
0x5AC0 /1/1/ 3 Tufan 2 2
0x5B40 /1/2/ 4 Sangram 2 2
Hope you like it.
Trick- Date Range
To display continues date between 2 date ranges uses this trick
SET DATEFORMAT DMY
GO
IF NOT EXISTS(SELECT * FROM sysobjects WHERE xtype='U' and name like'Numbers')
BEGIN
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO Numbers
FROM sys.columns s1
CROSS JOIN sys.columns s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEYCLUSTERED (Number)
END
GO
DECLARE @Start DATETIME
DECLARE @End DATETIME
SET @Start='01-01-2011'
SET @End='28-02-2011'
SELECT
@Start+Number-1
FROM Numbers
WHERE Number<=DATEDIFF(day,@Start,@End)+1
GO
Which Data Type is good For Primary Key
The most important thing of a table is defining the primary key; it could be single column based or the composite primary key.
In my development life, I saw a lot of primary key with different type or patterns.
Such as single decimal number, some use character with decimal numbers and some others use character+decimal+special character like "SOM/001/2011-2012" like this.
When I am working with SQL server, it always my mind, that what is the better method to implement primary key rather what data type I choose to implement. For searching the suitable answer I read several articles related to it.
One thing is clear, Microsoft provide you the data type UNIQUE IDENTIFITRE witNEWID() function to generate the world wide unique primary key. It 16 byte binary value and called globally unique identifiers (GUIDs). It takes your processor ID, NIC card ID etc to generate the Primary key.
But is it a good data type for a primary key?
To get the suitable answer, we have to understand the definition of the primary key.
1. It is unique and NOT NULL.
2. The primary key contains the default clustered index (Physically sorted environment).
So, is the UNIQUE IDENTIFIRE is the good choice for cluster indexed data? In my view it is not. If your primary key is not well indexed is it going for good quality of performance? No not at all.
So in my view
For primary key always choose the Integer (int) as best data type.
If you're primary key range is very large and not supported the range of Integer then go to Big integer (bigint).
Please review my article, and give me suitable comments related to this as all that I mentioned is not listed in MSDN or any other MS guide line. It is just my point of view only.
The NEW Data Types MS SQL 2008
The data type of SQL server is a very important thing to building the table objects. So we must have clear point of view before using different data types.
In my professional life I have seen may DBA uses the wrong type of data type in table objects and results, performance slow down, Time out …expire etc, as they don't have enough idea of data type related to SQL server.
In these articles, I try to define some new data types that came with SQL 2008 and not used in previous version of MS SQL server.
DATE TIME
It's much easier with SQL 2008; MS provide us better control related to Date and time data types. In SQL 2008 introduced: DATE, TIME, DATETIME2, and DATETIMEOFFSET.
[A]
DATE
The DATE data type stores only date in YYYY-MM-DD format.
It stores range of 0001-01-01 through 9999-12-32. It takes 3 bytes to store.
[B]
TIME
It stores only time in the format hh:mm:ss.nnnnnnn, and range between 00:00:00.0000000 through 23:59:59:9999999 and is accurate to 100 nanoseconds. It takes 3 to 5 bytes to store.
[C]
DATETIME2
It is as similar to the SQL 2005 DATETIME but have greater range value. It runs from 6 to 8 bytes to store.
[D]
DATETIMEOFFSET
It is similar to DATETIME2, but includes additional information to track the time zone. The format is YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm with a range of 0001-01-01 00:00:00.0000000 through 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC), and an accuracy of 100 nanoseconds. Storage depends on the precision and scale selected, and runs from 8 to 10 bytes.
SPECIAL
Two new spatial data types have been added- GEOMETRY and GEOGRAPHY-which you can use to natively store and manipulate location-based information, such as Global Positioning System (GPS) data.
[A]
GEOMETRY
The GEOMETRY data type is used to store planar (flat-earth) data. It is generally used to store XY coordinates that represent points, lines, and polygons in a two-dimensional space. For example storing XY coordinates in the GEOMETRY data type can be used to map the exterior of a building.
[B]
GEOGRAPHY
The GEOGRAPHY data type is used to store ellipsoidal (round-earth) data. It is used to store latitude and longitude coordinates that represent points, lines, and polygons on the earth's surface. For example, GPS data that represents the lay of the land is one example of data that can be stored in the GEOGRAPHY data type.
HIERARCHYID
The HIERARCHYID data type is used to enable database applications to model hierarchical tree structures, such as the organization chart of a business.
The HIERARCHYID data type makes it easier to express these types of relationships without requiring multiple parent/child tables and complex joins.
· Organizational structures
· A set of tasks that make up a larger projects (like a GANTT chart)
· File systems (folders and their sub-folders)
· A classification of language terms
· A bill of materials to assemble or build a product
· A graphical representation of links between web pages
FILESTREAM
FILESTREAM is not a data type as such, but is a variation of the VARBINARY(MAX) data type that allows unstructured data to be stored in the file system instead of inside the SQL Server database.
|