MSSqlserver Table Queries

Tables

SOME REMEMBERING POINTS ABOUT A TABLE

Maximum column in a Table are 1024(Non-Sparse Columns)

Maximum Column in a Table of SQL 2008 are 30,000(Sparse Columns)

Spars Column was Introduced in SQL2008 and used for Maximum Null values

The Size of a Row is 8060 Byte

1) How Can we Create a Table

First Use Database in Which u Want to Make Table

use dbemp

For A New Table

create table tbemp (empno int,ename varchar(50),eadd varchar(50),esal int)

2) How Can Check Table Sturcutre

exec sp_columns tbemp

1) How Can insert Identity on a Column of A Table

create table tbemp1(empno int identity(2,3),ename varchar(50),eadd varchar(50),esal int)

Now See Table Sturcture

exec sp_columns tbemp1

1) How Can Pick Identity Column Value

select @@IDENTITY

1) How Can Reset Identity Column Value

set Identity_insert tbemp1 on

——————

–3)

delete from tbemp1

–4)

select *From tbemp1

–5)

insert tbemp1(empno,ename,eadd,esal) values(9,’Aryan’,’Patiala’,500)

–6)

set Identity_insert tbemp1 off

–7)

insert tbemp1 values(‘Aryan’,’Patiala’,500)

–8)

select *From tbemp1

Description About Query:- Identity Column could be reset By Given Above Steps. First Make a Table Using 3) Query and then Select his identity Value and set Identity on. Then delete records from  table and then select record from table.You will get output blank.

Then execute –-5) Query and now set identity insert off again insert few values in table and at last select record from table.Now you will see  that Identity Column value will be start from there where u Intialize his value.

But remember here that u will initialize identity value one more from  last value of Identity.

1) How Can Reseed Identity Column Value from Zero

–Now Create a Table and Learn How can we Reseed Identity Value means Again How Can I Set him Zero or Intialize from Zero

create table tbemp7

(empno int identity,ename varchar(200))

–Check Table Sturcture

exec sp_columns tbemp7

–Now Insert Few Records in Your Table

–Here You have no need to specify empno because it is auto generate or Identity Column

insert tbemp7 values(‘Aryan’)

—Select Records

select *from tbemp7

–Now Select Identity Value

select @@IDENTITY

–Delete Recrods from Table

delete from tbemp7

–and now select Records

select *From tbemp7

–Now For Intializing of Identity Value from 0

–First Execution It Will Show U that Current Identity Value is 5 andCurrent Column Value is 0

dbcc checkident(‘tbemp7’,reseed,0)

–Second Execution It Will Show U that Current Identity Value is 0 and Current Column Value is 0

–Now Insert Records in Your Table

insert tbemp7 values(‘Aryan’)

–Select Recrods and U will see that empno will be start from 1

select *From tbemp7

Description About Queries :- These Queries Execute Step by Step Then U Can Check out ur Concept About Identity Column.

@@Identity is a parameterless function or Global Variable.dbcc means database Consistency check Command.reseed will reset to seed value from 0

1) How Can we Create a Unique column across mulitiple databases

How Can we Make a Unique Column across Mulitiple Databases using uniqueidentifier Datatype

Create a Table

create table tbemp6

(empno uniqueidentifier rowguidcol,ename varchar(50))

When u will execute This Query It Will Create a Table now insert few records

insert tbemp6 (ename) values(‘Pawan Verma’)

View your Table U Will See NULL Value in Empno because uniqueidentifier won’t tabke initate value

select *From tbemp6

Now Learn How Can Make Unique Empno Randomly like GUID method of Asp.net using uniqueidentifier datatype

create table tbemp7

(empno uniqueidentifier default newsequentialid() rowguidcol,ename varchar(50))

Note:- newsequentialid() is method which will initialize uniqueidentifier value randomly and default is a constraint,rowguidcol means a unique column will guide to a Row

———————————————————–

Insert few Records and View Your Table

insert tbemp7 (ename) values(‘Pawan Verma’)

How Can We Select Unique Empno all

select $rowguid from tbemp6

Be Happy and Enjoy in Queries World!!!!

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s