DDL
Create | create table emp ( slno tinyint name varchar(10)); |
Rename Table | Exec sp_rename ’emp’,’employee’ |
Alter Data type | Alter table emp Alter column slno smallint; |
ADD Column | Alter table emp ADD DOB Datetime |
Drop column | Alter table emp drop column name; |
Rename column | Exec sp_rename ’emp.slno’,’Rno’,’COLUMN’ |
Drop | Drop table emp; |
SQL SERVER STUDIES
Data type
DATA TYPE | FROM | TO | |
bigint | -9,223,372, 036,854,775,808 | 9,223,372, 036,854,775,807 | |
int | -2,147,483,648 | 2,147,483,647 | |
smallint | -32,768 | 32,767 | |
tinyint | 0 | 255 | |
bit | 0 | 1 | |
decimal | -10^38 +1 | 10^38 -1 | |
numeric | -10^38 +1 | 10^38 -1 | |
money | -922,337,203, 685,477.5808 | +922,337,203, 685,477.5807 | |
smallmoney | -214,748.3648 | +214,748.3647 | |
float | -1.79E + 308 | 1.79E + 308 | |
real | -3.40E + 38 | 3.40E + 38 | |
datetime | Jan 1, 1753 | Dec 31, 9999 | |
smalldatetime | Jan 1, 1900 | Jun 6, 2079 | |
date | Stores a date like June 30, 1991 | ||
time | Stores a time of day like 12:30 P.M. | ||
Sr.No. | DATA TYPE & Description |
Binary | Maximum length of 8,000 bytes(Fixed-length binary data ) |
Varbinary | Maximum length of 8,000 bytes.(Variable length binary data) |
Varbinary(max) | Maximum length of 2E + 31 bytes (SQL Server 2005 only). ( Variable length Binary data) |
image | Maximum length of 2,147,483,647 bytes. ( Variable length Binary Data) |
Character Strings Data Types
Sr.No. | DATA TYPE & Description |
Char | Maximum length of 8,000 characters.( Fixed length non-Unicode characters) |
Varchar | Maximum of 8,000 characters.(Variable-length non-Unicode data). |
Varchar(Max) | Maximum length of 2E + 31 characters, Variable-length non-Unicode data (SQL Server 2005 only). |
Text | Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters. |
Nchar | Maximum length of 4,000 characters.( Fixed length Unicode) |
Nvarchar | Maximum length of 4,000 characters.(Variable length Unicode) |
Nvarchar(max) | Maximum length of 2E + 31 characters (SQL Server 2005 only).( Variable length Unicode) |
ntext | Maximum length of 1,073,741,823 characters. ( Variable length Unicode ) |
Sr.No. | DATA TYPE & Description |
Sql_variant | Stores values of various SQL Server-supported data types, except text, ntext, and timestamp. |
Timestamp | Stores a database-wide unique number that gets updated every time a row gets updated |
Uniqueidentifier | Stores a globally unique identifier (GUID) |
Xml | Stores XML data. You can store xml instances in a column or a variable (SQL Server 2005 only). |
cursor | Reference to a cursor object |
table | Stores a result set for later processing |