Email Article To a Friend View Printable Version

Resetting IDENTITY

SQL Server 2005

I've been asked plenty of times, how do you reset an IDENTITY column? After inserting data into a table, and then deleting everything, how do I make the IDENTITY to start from 1 again?

Personally I think is a bad idea to reset identity columns to any value lower than they currently are. To find out why, read on. But first, let me show you two ways you can reset an identity column.

Reseeding an IDENTITY column to a new value

-- syntax
DBCC CHECKIDENT ('mytablename', RESEED, new identity value)

-- example
DBCC CHECKIDENT ('Customer', RESEED, 1000)

Resetting IDENTITY to the next highest number not in use

-- syntax
DBCC CHECKIDENT ('mytablename')

-- example
DBCC CHECKIDENT ('Customer')

Check current IDENTITY value without resetting

-- syntax
DBCC CHECKIDENT ('mytablename', NORESEED)

-- example
DBCC CHECKIDENT ('Customer', NORESEED)

Now why would it be a bad idea to reset an Identity column to a lower number? To say change it from 5,000 to 1? Because if your table is not empty, you might create a duplicate. If you have a unique index, you may recieve an error when inserting. And reusing numbers can cause confusion for users/developers.

Identity columns are typically used as unique references. Order Numbers, Customer Number, Receipt Number, Invoice Number, Patient Number, etc. Things where you don't want to confuse two different entities. Used as surrogate keys with referential integrity they are quite handy. Reusing a number means they no longer represent unique things.

But if you're using a unique index, your safe right? Not entirely. Say you only have 100 rows in your table, identities 5,000 to 5,100. For some reason they start at 5,000, and you decide to reset to 1 without truncating the table, because it looks nice. What happens after 4,999 inserts? Identity is set to 5,000. But you already have an 5,000 record. So you next insert will fail. As will all other insert attempts until IDENTITY reaches a number that is not already in use. If those were sales that you missed out on, that could be a serious loss.

What about in your development environment? I still think you should not reset them there either. Can all your applications handle high numbers? If you've declared the identity as an INT, but have used it as a smallint somewhere else, you might never realise until you hit a number smallint doesn't handle. What about testing procedures? Are they expecting certain low values to always exist? What about reporting? Can that handle numbers being reused, or does it change historical reports?

There is one instance where I DO support resetting identity, but only using the DBCC CHECKIDENT ('mytablename') method: after making inserts with IDENTITY_INSERT ON. In this case you ARE very likely to create duplicate numbers / insert errors. Immediately after making inserts with SET IDENTITY_INSERT ON, you should reset the identity. Don't specify a new value and the DBCC CHECKIDENT command will reset it to the maximum current value of the identity column. This way the next insert will be a unique number.

But please, don't reset IDENTITY to a number lower than it's current value. You really don't gain anything by having identity columns that start at 1. It doesn't use any less resources to start at 1 than it does to use 1,000,000.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz




Email Article To a Friend View Printable Version

SQL Server 2005 Template Directories

SQL Server 2005

If you want to be able to have your own template appear in Visual Studio when you add a new item to a project, this is where you will need to save it.

Visual Studio 2005 Database Projects:
C:\Program Files\Microsoft Visual Studio 8\Common7\Tools\Templates\Database Project Items

Reporting Services 2005:
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ ProjectItems\ReportProject

Reporting Services 2005 wizard template stylesheet:
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ Business Intelligence Wizards\ReportsStyles



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

SQL Server Agent (Agent XPs disabled)

SQL Server 2005

When SQL Server 2005 Management Studio's Object Browser shows the SQL Server Agent service with a red down arrow and the text Agent XP's disabled, the service is not started or disabled.

Start the SQL Server Agent service by:

  • using the SQL Server Configuration Manger
    located in Start -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager
  • Or use the Services Management Console
    located in Start -> Control Panel -> Administrative Tools -> Services
  • Or you can start the default service from the command prompt
    net start "SQL Server Agent (instance name)"
    Eg: net start "SQL Server Agent (MSSQLSERVER)"



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

SQL 2005: How to Use ORDER BY in a Derived Table

SQL Server 2005

I was asked today how to select distinct columns while ordering by another column that does not appear in the column output list. For example:

select distinct [name]
from msdb.sys.columns
order by system_type_id

If you try to run this code, you will get the error message:

Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

One way to get around this is to place the ORDER BY in a derived table, used as the source for the DISTINCT query. eg:

select distinct [name]
from (
select [name]
from msdb.sys.columns order by system_type_id
) as dt
However you will get this error:
Server: Msg 1033, Level 15, State 1, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

But what if you don't want to limit your results to just the X TOP rows? What if you want all the rows?

Well not only can TOP be used to limit the number of rows a query returns, it can be used to instruct (some what redundantly) that you want all the rows: TOP 100 PERCENT.

select distinct [name]
from (
select top 100 percent [name]
from msdb.sys.columns order by system_type_id
) as dt

And there you have it! Distinct results ordered by a column that is not output!



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

SQL Server 2005 Goes Gold!

SQL Server 2005

Microsoft has quietly kicked out the final version of SQL Server 2005 and Visual Studio 2005 to MSDN subscribers ahead of the Launch Tour 2005. The .Net 2.0 Framework has also been released, which you will need to install/use either of these great things.

If you've been keeping track of SQL Server 2005's development, you'd know that the last three years have seen a lot of new features added. Service Broker, XQuery, Partitioning, Mirroring, Snapshots, .Net Integration, and new Management tools. Even DTS got a make over so severe it also got a new name: SQL Server Integration Services (SSIS).

If you haven't had the chance to cut your teeth on SQL Server 2005, I'd really recommend installing the RTM. Now that its stable you can install it side-by-side with your existing SQL Server 2000 instances (or by itself in a shiny new server). The new management interface (SQL Server Management Studio) might take a bit of getting used to but it can be used with 2005 or 2000 installations.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

SQL Server 2005 Books Online MSDN Website

SQL Server 2005

Installing the SQL Server 2005 BOL help requires the .Net 2.0 Beta Framework, which you might not wish to install yet. In my case, I have a virtual machine with a full 2005 install, but on my physical machine, I often need to refer to 2005 BOL. If you used to refering to the MSDN website for SQL Server 2000's Books Online (BOL) for help, you'll have probably noticed that the BOL for SQL Server 2005 isn't available there yet.

The reason is that Microsoft are planning to launch a new MSDN website along with Visual Studio 2005 (around the same time SQL Server 2005 is launched). In the meantime the new MSDN website, dubbed MSDN2, is available online in its beta form. It's a bit buggy at times, but is supposed to provide better access than the current MSDN when it is launced.

SQL Server 2005's BOL is available on the new site, along with Visual Studio 2005 help. Take a look and remember to give feedback on any bugs you find so they can be fixed in time.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

SQL Server 2005 September CTP Released

SQL Server 2005

SQL Server 2005 September Community Technology Preview (CTP) has been released today by Microsoft and is available for download now.

The September CTP will be the last CTP/Beta before Microsoft releases the RTM/Gold version in November. Its available in the same five flavours as before: Enterprise, Standard, Workgroup, Express, and Developer.

Microsoft have said that much touted Database Mirroring feature, an alternative to Clustering with Log Shipping which works with physically separated hardware, will slip from the initial release. It's expected to ship in the first half of 2006, which can be thought of as our first Service Pack for SQL Server 2005.

We're also expecting around that time a cut-down version of SQL Server 2005 Managment Studio to replace the existing Express management tools. This should make looking after Express installs much easier, and more inline with the other versions of SQL Server.

This CTP is supposed to play nicely with VS2005 RC. If you are going to use Visual Studio with SQL Server, there is definate install order you must follow. If you have a beta of Visual Studio installed, you should try to auto-uninstall it, otherwise you could be facing a difficult uninstall process.

The September CTP itself should be a seemless upgrade (without uninstall) from the June CTP. June CTP upgrade from April's seemed to go well for me, so I'm hoping the same can be said for what will undoubtably be a more polished product. If you are planning to uninstall your previous ctp/beta installation, there are some things you should consider first.



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

Determining Store Procedure Creation Options

SQL Server 2005

If you've scripted out a stored procdure, you'll have noticed the SET ANSI_NULLS ON or SET QUOTED_IDENTIFIER ON options are also scripted. If your a verteran SQL coder, no doubt you set these before you create any stored procedures. But once a stored procedure is created, how do you determine what options were set?

Well in SQL Server 2005 you will need to use the sys.sql_modules catalogue view. the sys.sql_modules view will show you the options set for definitions of:

  • Defaults
  • Stored Procedures
  • Views
  • DML Trigger
  • Functions (scalar / table)

The code below shows how to view the options of your objects.

SELECT syso.name, type, sysm.*
FROM sys.sysobjects AS syso
INNER JOIN sys.sql_modules AS sysm
   ON syso.id = sysm.object_id


Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

List Extended Properties for all Tables and Columns

SQL Server 2005

Extended Properties in SQL Server give you the ablility to attach a comment or description to an object in the database. When you use Management Studio to add a Description to a table or column, it is stored as an Extended Properties with the name of MS_Description, and the object it relates to.

You can use the fn_listextendedproperty() table function to return the extended properties for the database, or a specific table or column. But what if you want to return all comments for all column and tables in the database? Well you'll need to use the system tables directly to make that happen.

To select all the extended properties for all the columns and tables in the database, use this command:

SELECT OBJECT_NAME(EXP.major_id) AS TableName, 
       C.name                    AS ColumnName, 
       EXP.name                  AS PropertyName, 
       EXP.value                 AS PropertyValue
FROM   sys.extended_properties AS EXP
LEFT OUTER JOIN sys.columns AS C
ON    C.object_id = EXP.major_id
AND   C.column_id = EXP.minor_id
WHERE EXP.class_desc = 'OBJECT_OR_COLUMN'



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Email Article To a Friend View Printable Version

SQL Server 2005 Database Snapshots

SQL Server 2005

This is a really neat addition to SQL Server 2005. Database Snapshots are essentially a read-only, static view of an existing database, without duplicating all the data on disk.

I use this feature a lot when I upgrade database schemas, or make major changes to data, and need to be able to compare the changes afterwards. If you had a 3rd party tool (or an untrusted developer) that upgrades an existing database, you could use this to see what changes occur.

Now database snapshots are only available in the Enterprise and Developer editions of SQL Server 2005, and is really usefull in both these setups. Developers benefit from being able to compare database changes to a prior state. In Enterprise production, you can use the snapshot for reporting purposes or with Analysis Services. The read-only snapshot will not suffer from locking issues that you might experience with active OLTP databases.

Creating a snapshot of an existing database is really simple.

CREATE DATABASE database_snapshot_name 
	ON
		(
		NAME = logical_file_name, 
		FILENAME = 'os_file_name'
		)
	AS SNAPSHOT OF source_database_name 

So why the need for a filename if we're creating a read-only view of the source database? We'll before a data-page is modified in the source database, the original page is copied to the snapshot database. This "copy-on-write operation" is what ensures the data in the snapshot database remains constant.

SQL Server Database Snapshots use the NTFS file system "sparce file" feature to create a file on disk that appears the same size as the original database, but doesn't actually consume disk space until a data-page is modified in the source. You can check this by viewing the snapshot physical file properties in windows explorer (the Size on Disk represents how much disk space is really being consumed). Or you can use the T-SQL fn_virtualfilestats function. Should every page in your source database be modified, your snapshot file will at most consume the size of the original source database at the time of the snapshot creation.

Now if you really wanted to, you can even restore your source database from the snapshot. While you couldn't use snapshots in place of a proper backup stratergy: if you are testing a change script over and over, it might be quicker to restore from the snapshot itself. In that case a simple

RESTORE DATABASE source_database_name 
FROM DATABASE_SNAPSHOT = database_snapshot_name
will bring your source database back to its previous state.

Such a simple elegant solution!



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz







Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 
     
 Copyright © 2017 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.