SqlTutorial-1 : Lesson-1 : Class-1: Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Basic Db-Table Operations: Creating, droping, finding, and renaming Db-Tables

A database can contain several tables and a table physically stores your information as collection of records. A table can be created using CREATE TABLE Clause. Before creating a table you should know about what types of data you can store into the Table's-column and what's the range and size of each data-item.

1. Data Types offered by SqlServer

Table: Data Types

Data type	Range/Description	Storage
1.Integer type Values
Bit	0 , 1 or NULL	1 byte
Tinyint	0 to 255	1 byte.
Smallint	-215 to 215-1	2 bytes.
Int	-231 to 231-1	4 bytes.
Bigint	-263 to 263 - 1	8 bytes
2. Real Values (floating point values)
Real	-1.18E-31,0 and 1.18E-38 to 3.40E+38	4 bytes.
Float	-1.79E + 38 to -2.23E-38.0	4 or 8 bytes, depending on mantissa.
Decimal	-1038+1 to 10-38-1 double precession.	Based on precision
Numeric(p,s)	-1038+1 though 1038-1	Based on precession.
3. Character type value
Char	Upto 8,000 characters(1 char-1byte)	1 byte per character
Varchar(n)	Upto 8,000 variable length chars	1 byte per character n=1 to 8,000bytes
Varchar(max)	Non-unicode characters upto the maximum storage capacity.	1 byte per character. Max 231-1 bytes.
Text = varchar(max)	Up to 231-1 characters	231-1 (2,147,483,647 bytes).
nChar	Up to 4,000	Unicode characters
nVarchar(n)	Up to 4,000	Unicode characters.
nVarchar(Max)	Unicode characters up to the maximum storage capacity.	231-1 chars.
nText	Up to 230-1	Unicode characters
4.Date and Time type values
Datetime	January1,1753 through Dec 31,9999.	8 bytes; accurate to 3.33 milliseconds
Smalldatetime	January1,1900 through June 6, 2079.	4 bytes; accurate to 1 minute.
5.Binary Data Type values
Binary(n)	Binary data with length of n-bytes	N = 1 to 8,000 bytes (i.e, nearly 8kb)
Image	Variable-length binary data	Upto 231-1 (2,147,483,647) bytes.
Varbinary(n)	Same as binary but Variable length	N = 1 to 8,000 bytes
Varbinary(max)	Binary data up to the maximum storage	Two times the number of characters entered plus 2 bytes, upto 230-1
6. Special data Type
Timestamp	A unique binary number. Typically used for version stamping rows.	8-bytes.
Uniqueidentifier	A 16-byte GUID	16-bytes.
Xml	XML instances or a variable of xml type	2 GB
Sql_variant	Any data SQL data type except text, ntext, image, timestamp, and sql_varient.	Upto 8016 bytes
Money	-922,337,203,685,477.5808 to 922,337,203,685,477.5808	8 bytes.
Smallmoney	-214,748,3648 to 214,748,3647 (no floating values)	4 bytes.
7. Differences: Varchar(max) Vs Text
Varchar(max)	Non-unicode characters upto the maximum storage capacity.
Text = varchar(max)	Up to 231-1 characters
8. Differences: Decimal Vs Numeric(s,p)
Decimal	-1038+1 to 10-38-1 double precession.	Based on precision
Numeric(p,s)	-1038+1 though 1038-1	Based on precession.
9. Differences: Text/Varchar vs DateTime regarding to symbols /, -
Both hold the data represented in quotes ‘ ‘ or “” but Text cannot include symbols ‘/’, ‘ –‘ etc. e.g., Declare @t varchar(15) ; Set @t=’abc/d’ ‘abc’

2. Creating Db-Tables

Syntax

CREATE TABLE table_name
  ( column_name_1 data_type [column_Constraints]
      [, column_name_2 data_type [column_Constraints]]...
      [, table_Constraints])

Column Syntax:

Column_name type [ COLLATE collation_definition ]
      [ [ DEFAULT constant_expression ] | IDENTITY [ ( seed , increment ) ] ]
      [ ROWGUIDCOL ]
      { [ NULL | NOT NULL ] | [ PRIMARY KEY | UNIQUE ]
             | CHECK ( logical_expression )
      }

• The following are mutually exclusive: DEFAULT + IDENTITY, NULL+NOT NULL, PRIMARY KEY+UNIQUE
• NULL|NOT NULL : Indicates whether or not the column can accept null values. If omitted, NULL is the default unless PRIMARY KEY is specified.
• Primary Key | UNIQUE: Identifies the primary key or a unique key for the table. If PRIMARY is specified, the NULL attribute isn't allowed.
• IDENTITY: Identifies an identity column. Only one identity column can be created per table.
• DEFAULT : default_value Specifies a default value for the column.

Examples:

(1) Creation of table with Primary key as column constraints.

CREATE TABLE Employee(
     emp_no INTEGER NOT NULL CONSTRAINT prim_empl PRIMARY KEY,
     emp_name CHAR(20) NOT NULL,
     dept_no CHAR(4) NULL);

(2) Creating table with Computational Column

CREATE TABLE SampleTable ( A int, B bit, C AS (A+B) )

3. Finding existence of table

By querying System-defined tables you can determine the existance of particular table.

Examples:

1. Finding existence of table say "Employee"

If EXISTS(select * from sys.Tables where name='Employee')
Begin
raiserror('Dropping existing table...',0,1); DROP TABLE Employee;
End

4. Dropping tables

Using DROP-TABLE clause, you can remove the Db-Table permanently irrespected to whether table is empty or loaded with data

Syntax:

DROP TABLE <TableName>

Examples:

1. Simple-Droping table say "Employee"

--CREATE TABLE customer(emp_id char(4),name char(30), phone char(12));
 Drop table customer;

2. SmartWay -Droping table say "Employee"

If EXISTS(select * from sys.Tables where name='Employee') begin raiserror('Dropping existing table...',0,1); DROP TABLE Employee; end

5. Renaming table, column, database and Index (sp_rename)

There is no clause avialable to rename Db-tables instead use predefined stored procedures for this called "sp_rename".

 

 Syntax

sp_rename "oldName", "newName", "{Table(default) | Column |Database|Index }"

Examples:

1. Renaming a table

   Exec sp_rename 'oldTable', 'newTable'

2. Renaming a column

   Exec sp_rename 'tableName.col1', 'newColumn', 'column'

3. Renaming a database

   Exec sp_rename 'oldDatabase', 'newDatabase', 'Database'

4. Renaming a Index

   Exec sp_rename 'oldINDEX', 'newINDEX', 'Index'