I imagine you are just looking for simple syntax in order to reseed the identity column of a table you just deleted from. Here is the quick version: DBCC CHECKIDENT(‘##reseed_example’, RESEED, @max_seed) And here is an extended example: — populate a table with identity SELECT ID = IDENTITY(int,1,1) ,name INTO ##reseed_example FROM dbo.sysobjects — delete [...]
Archive of posts filed under the DBA category.
Indexed Views
To explain what an indexed view is, let’s first look at what constitutes a view. A view may sound like a fancy elaborate thing, however all it is, is a saved SELECT statement, nothing else. It is not explicitly compiled, nor does it contain any data. When you select from a view, it goes to [...]
Auto Update Statistics & Auto Create Statistics
The ability to create and update statistics is a crucial liberty for the query optimizer. The optimizer needs to know how it is going to query underlying tables, and when the statistics do not match the data, it is very probable that it will choose a non-efficient method for querying. I’ve seen and heard a [...]
Using DBCC UPDATEUSAGE
When DBCC UpdateUsage is run, page and row counts are updated. When this information is incorrect, it can give incorrect reports for how much space is used by tables and indexes. The most likely cause for incorrect usage stats is when a database is upgraded to a new compatibility level of SQL Server (i.e. 2000 [...]
Using IDENTITY_INSERT
The only way to insert values into a field that is defined as an “IDENTITY” (or autonumber) field, is to set the IDENTITY_INSERT option to “ON” prior to inserting data into the table. To illustrate, let’s create a table that has an identity column defined. — Create table with identity column CREATE TABLE MyOrders ( [...]
Monitor Queries against a Table
I recently had a need to monitor inserts against a particular table in order to determine what kind of locks they were acquiring. Being that we could not run traces on the system, I had to resort to a roll-your-own approach to monitoring the table. The goal was to determine what kind of locking was [...]
Understanding Batch Requests / sec
SQL Server’s Batch Requests represents the number of SQL Statements that are being executed per second. In my opinion this is the one metric that gives the best baseline on how your server is performing. It cannot be looked at by itself however. It is necessary to correlate with other metrics (especially CPU usage) in [...]
How to Export a Table
Often times you have a need to export a table from SQL Server into another format. The most common of these is Excel, CSV (Comma Separated Value), Access, or even to another SQL Server. Luckily, there is one tool native to SQL Server that can export to all these formats, the SQL Server Import and [...]
How to Backup a Table
There is not standard method for backing up a table in SQL Server. A common request I receive from clients is to only backup specific tables within a database. Well, unfortunately there is no out of the box method for doing this akin to a database backup so we need to roll our own way. [...]
How to Backup a Database
Backing up a database is relatively straight forward using SQL Server Management Studio. First open management studio and connect to the Database Server that contains the database you want to backup. Expand the “Databases” folder and right-click on the database you want to make a backup of. Choose Tasks -> Back Up… (now the Back [...]
