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'

4 comments:

  1. Great List Thank you for sharing such an impressive and useful post

    Dot Net Online Training

    ReplyDelete
  2. That was a great message in my carrier, and It's wonderful commands like mind relaxes with understand words of knowledge by information's.
    Best Devops Training in pune
    Devops Training in Bangalore
    Power bi training in Chennai

    ReplyDelete
  3. That was a great message in my carrier, and It's wonderful commands like mind relaxes with understand words of knowledge by information's.

    devops training in bangalore
    devops course in bangalore
    aws training in bangalore

    ReplyDelete