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'

2 comments: