Identity Columns in Table Object with DBCC Checkident

Identity Columns : When a row is inserted into a table the column will take the value of the current seed incremented by the step.

An identity column is not guaranteed to be unique nor consecutive. You should always place a unique index on an identity column if your system requires uniqueness.

CREATE TABLE table_name

(col1 INT IDENTITY(1,1),

 col2 INT)

How to reset Identity column.

DBCC CHECKIDENT can reseed (reset) the identity value of the table. For example, YourTable has 25 rows with 25 as last identity. If we want next record to have identity as 35 we need to run following T SQL script in Query Analyzer.




DBCC CHECKIDENT (yourtable, reseed, 34)

If table has to start with an identity of 1 with the next insert then table should be reseeded with the identity to 0




DBCC CHECKIDENT (yourtable, reseed, 0)

insert into table_name (col2) values(20)

insert into table_name (col2) values(30)

select * from table_name

col1        col2

----------- -----------

1           20

2           30

(2 row(s) affected)

How to find current identity seed? (using DBCC checkident)

DBCC checkident (table_name)

Checking identity information: current identity value '2', current column value '2'.

Remember the next value will be the current seed plus the step

Post Reference: Vikram Aristocratic Elfin Share

Logical Query Processing Phase

Lets delve into the Logical Query Processing Phases, below given is the general form of query, along with the steps according to the order in which different clause are logically processed.

(8) SELECT (9) DISTINCT (11) TOP

(1) FROM <left_table>

(3) <join_type> JOIN <right_table>

(2) ON <join_condition>

(4) WHERE <where_condition>

(5) GROUP BY <group_by_list>

(6) WITH {CUBE | ROLLUP}

(7) HAVING <having_codition>

(10) ORDER BY <order_by_list>

In most of the programming language the code is executed in the order in which it is writer. In SQL, the first clause that is processed is the FROM clause, while the SELECT clause which appears first processed almost last.

Each step in query processing generates VIRTUAL TABLE that is used as the input for the next step to process. These virtual tables are not available to the caller. Only the table generated by the final step is returned to the caller.

Brief on various phases of Logical Query Processing

(1)   FROM: A Cartesian product(cross join) is performed between the tables in the FROM clause, and as a result a virtual table gets generated (VT1).

(2)   ON: The ON filter is applied to VT1. Only rows for which the join condition matches or / for which the join condition is true are inserted to form VT2.

(3)   OUTER (join): If outer join is specified, matching rows from the VT1 along with all the rows of VT2 are picked up and inserted to form VT3.

(4)   WHERE: The where filter is applied to VT3. Only rows for which the <where_condition> is evaluate to TRUE are inserted to form VT4.

(5)   GROUP BY: The rows from the VT4 are arranged by groups based on the column list specified in the GROUP BY Clause. VT5 is generated.

(6)   CUBE | ROLLUP: Subgroups are generated and added to the rows of VT5 to form VT6.

(7)   HAVING: The having filters are applied to VT6. Only groups for which the <having_condition> is true are inserted to VT7.

(8)   SELECT: The select is performed on VT7 to generate VT8.

(9)   DISTINCT: Duplicate rows are removed from VT8 to for VT9.

(10)ORDER BY: The rows from the VT9 are sorted according to the column list specified in the  ORDER BY clause to form VT10

(11)TOP: The specified number or percentage of rows are selected from top to form VT11.

And this VT11 is return to the caller.

In next post I will explain phases of query processing taking an example. By then Have gr8 coding. J  

Post Reference: Vikram Aristocratic Elfin Share

Backup and Restore with Split and Mirror

Backup and Restore your SQL Database

You can take the backup of your database by few line of t-sql like the one below

BACKUP DATABASE PracticeDatabase TO

DISK = N'D:\PracticeDatabase.bak'

, DISK = N'D:\try\PracticeDatabase.bak'

WITH FORMAT

NAME = N'PracticeDatabase-Full Database Backup'

GO

WITH FORMAT : means if there exist any backup with the name u defined in the query then it will delete/format that backup.

How to Split your database backup

There are situation where you want to split your database backup fine and want one file to stored in say d drive and another in e drive. You can do this very easily. Look at the code below

BACKUP DATABASE PracticeDatabase TO

DISK = N'D:\PracticeDatabase.bak'

, DISK = N'D:\try\PracticeDatabase.bak'

WITH FORMAT

NAME = N'PracticeDatabase-Full Database Backup'

GO

Here the backup is not going to stored in two location instead the whole backup get divided into two parts. One part get stored in D:/ and another will in D:/try

What is mirror backup and how can you take this?

Mirror backup is nothing but taking the same backup in two different locations.

BACKUP DATABASE [PracticeDatabase] TO
DISK = N'D:\PracticeDatabase.bak'
MIRROR TO DISK = N'D:\try\PracticeDatabase.bak'
WITH FORMAT
NAME = N'PracticeDatabase-Full Database Backup'

How to restore database

Restoring a database is very simple you just need to write few line of sql statement specifying the database name where you want to restore your database and the path where the backup file is lying.

RESTORE DATABASE TempPractice
FROM DISK = 'C:\Backup\SingleFile\AdventureWorks.bak'

Here we are restoring our database backup file at TempPractice database. And the backup file is lying at C:\Backup\SingleFile\.

How to restore Split database

Now let us see an example where we restore a database from a split file. This method is very similar to restoring a database from a single file; just add an additional DISK option.

RESTORE DATABASE [TempPractice]
FROM DISK = N'C:\Backup\MultiFile\AdventureWorks1.bak',
DISK = N'C:\Backup\MultiFile\AdventureWorks2.bak',
DISK = N'C:\Backup\MultiFile\AdventureWorks3.bak'
GO

Here the database AdventureWork split into three part AdventureWork 1, AdventureWork 2, AdventureWork 3.  So to restore such split database you need to specify each and every split backup file in Disk option.    

Post Reference: Vikram Aristocratic Elfin Share

Cursoring CTE : Using Cursor with CTE

When I tried to create cursor with the result of CTE, I was greated with the following error:

Incorrect syntax near the keyword 'declare'.

My piece of code was like below:

with myCTE(objId, name) as

(

      select top 5 object_id, name from sys.columns

)

declare myCur cursor for

Select objId, name From myCTE

Msg 156, Level 15, State 1, Line 6

Incorrect syntax near the keyword 'declare'.

This means my expected syntax was incorrect, after a few minute of google I found in one of the blog that  the declare cursor statement needs to be on the top of CTE declarations. So, here’s the correct syntax to define cursor with CTEs:

declare @objId int

declare @name varchar(10)

declare myCur cursor for

with myCTE(objId, name) as

(

      select top 4 object_id, name from sys.columns

)

Select objId, name From myCTE

Open myCur

Fetch next from myCur into @objId, @name

while @@fetch_status <> -1

begin

    print @objId

      print @name

      Fetch next from myCur into @objId, @name

end

close myCur

deallocate myCur

------------OUTPUT---------

117575457

xserver_na

117575457

xdttm_ins

117575457

xdttm_last

117575457

xfallback_

cool finicky learn… 

Post Reference: Vikram Aristocratic Elfin Share

Replicate : prefix '0' (Zero) with numbers

In most of the scenario, we need to prefix '0' (Zero) with numbers.

If my result is 15 then, it should be resulted as 00015
If my result is 2010 then, it should be resulted as 02010

So, the total length should be 5.

If the total length is 5 then '0' should be prefixed based on the result.

declare @para AS varchar(5)

select @para = '15'

select replicate ('0', 5 - len(@para)) +  @para

Result

--------------------

00015

(1 row(s) affected)

/* I want 5.20 should display like 05.20 This means i will append one zero

before the given indexNo */

declare @indexNo as numeric(8,2)

select @indexNo = 5.20

select Cast(Replicate(0,6-Len(@indexNo)) AS varchar(5)) + Cast(@indexNo AS varchar(5))

Result

----------

005.20

(1 row(s) affected)

So Replicate is nothing but repeats a string value a specified number of times

Post Reference: Vikram Aristocratic Elfin Share

Batch Delete – “One spoon at a time” the smart DELETE

BATCH DELETE

Delete in SQL server is not as simple as it looks.

Yes it has simple line of statement.

delete BatchDeleteDemo_Tab where name='Aadarshini'

Then what is the issue, there are several problem like

• transaction log
• disk space
• time consuming
• locking

It depends how many rows are affected by the DELETE statement above, but generally, it is almost never a good idea to run a ‘straight forward’ deletes.

SQL Server is working in a transactional manner, the rows which are to be deleted have to be logged in the transaction log, and if there is not enough disk space for the log, then we have a real problem – the log is full, no other transactions can be run… you get the idea.

When you delete rows from a table, the rollback transaction information is always written fully to the log file, and it will grow until the transaction is committed or rolled back.

So what is the option left for us:

while (1=1)

begin

      delete top 3 BatchDeleteDemo_Tab where name='Aadarshini'

      If @@rowcount = 0

            BREAK

      ELSE

            Continue

End

The while loop will delete 3 record at a time and then check if there are more rows to delete, if there are not then the loop gets terminated

CONCLUSION: This method is better and more efficient for obvious reasons: the log does not grow by too much and the log space is reused; it is easy to terminate the delete quickly and only the transaction of the last batch will be reverted; the lock escalations do not have a huge impact on the entire system since it is for few rows at a time.  

Post Reference: Vikram Aristocratic Elfin Share