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: [cc lang=”sql”] DBCC CHECKIDENT(‘##reseed_example’, RESEED, @max_seed) [/cc] And here is an extended example: [cc lang=”sql”] — populate a table with identity SELECT ID = IDENTITY(int,1,1) ,name INTO ##reseed_example FROM dbo.sysobjects — delete some records DELETE FROM ##reseed_example WHERE ID > 5 — find the current max identity DECLARE @max_seed int = ISNULL((SELECT MAX(ID) FROM ##reseed_example),0) — use the current max as the seed DBCC CHECKIDENT(‘##reseed_example’, RESEED, @max_seed) — let’s test INSERT INTO ##reseed_example ( name ) SELECT ‘newobject’ — done SELECT * FROM ##reseed_example [/cc] it should be noted that in order to use DBCC CHECKIDENT you need to be dbo (db_owner). This does present an issue sometimes because often the reason the user is performing a delete instead of a truncate is because they do not have dbo rights. To overcome this, you can will need to create a procedure that that uses: WITH EXECUTE AS ‘dbo’. And reseed from there.
Continue reading ...
My first introduction to the APPLY operator was using the DMVs. For quite a while after first being introduced, I didn’t understand it or see a use for it. While it is undeniable that it is has some required uses when dealing with table valued functions, it’s other uses evaded me for a while. Luckily, I started seeing some code that used it outside of table valued functions. It finally struck me that it could be used as a replacement for correlated sub queries and derived tables. That’s what we’ll discuss today. I never liked correlated subqueries because it always seemed like adding full blown queries in the select list was confusing and improper. [cc lang=”sql”] SELECT SalesOrderID = soh.SalesOrderID ,OrderDate = soh.OrderDate ,MaxUnitPrice = (SELECT MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID) FROM AdventureWorks.Sales.SalesOrderHeader AS soh [/cc] It always seemed to me that these operations should go below the FROM clause. So to get around this, I would typically create a derived table. Which didn’t completely feel right either, but it was still just a bit cleaner: [cc lang=”sql”] SELECT soh.SalesOrderID ,soh.OrderDate ,sod.max_unit_price FROM AdventureWorks.Sales.SalesOrderHeader AS soh JOIN ( SELECT max_unit_price = MAX(sod.UnitPrice), SalesOrderID FROM Sales.SalesOrderDetail AS sod GROUP BY sod.SalesOrderID ) sod ON sod.SalesOrderID = soh.SalesOrderID [/cc] What made this ugly was the need to use the GROUP BY clause because we could not correlate. Also, even though SQL almost always generates the same execution plan as a correlated sub query, there were times when the logic […]
Continue reading ...
Auditing tables can be a complicated procedure if you require a new table for every table being audited. Fortunately the new XML datatype can help to automate this procedure of auditing multiple tables into a single table. The following trigger template can be used to audit a table into a single table. First let’s create the table needed to hold the audited values: [cc lang=”sql”] CREATE TABLE [Log].[AuditTableHistory]( [HistoryID] [int] IDENTITY(1,1) NOT NULL, [TableSchema] [varchar](10) NULL, [TableName] [varchar](100) NULL, [AuditValue] [xml] NULL, [DateCreated] [datetime] NULL, CONSTRAINT [PK_AUDITTABLEHISTORY] PRIMARY KEY CLUSTERED ( [HistoryID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] ) ON [PRIMARY] — Now let’s create the trigger CREATE TRIGGER [Audit].[Trg_Schema_Table] ON [Schema].[Table] AFTER UPDATE,DELETE AS BEGIN — SET NOCOUNT ON added to prevent extra result sets from — interfering with SELECT statements. SET NOCOUNT ON; DECLARE @AuditValue xml SET @AuditValue = ( SELECT TOP 1 * FROM DELETED FOR XML AUTO ) INSERT INTO Log.AuditTableHistory ( AuditValue, TableSchema, TableName, DateCreated ) SELECT AuditValue = @AuditValue, TableSchema = OBJECT_SCHEMA_NAME(parent_obj), TableName = OBJECT_NAME(parent_obj), DateCreated = GETDATE() FROM sysobjects so WHERE so.id = @@PROCID END GO [/cc] We can apply this trigger to any table we want just by changing the “ON” statement. It will work for any table. The only difficulty comes in when we change the schema for a particular table. When this happens, we need to know the date/time the table schema changed so […]
Continue reading ...
In order to create a view, use the “Create View” statement using the following convention: [cc lang=”sql”] CREATE VIEW dbo.vStoreAccountNum AS SELECT AccountNumber FROM Sales.Customer WHERE CustomerType = ‘S’ [/cc] Views are essentially stored SELECT Statements. They do not offer any speed advantage over regular SELECT statements. And they are not faster than a SELECT statement executed from within a stored procedure. They are mostly good for hiding complicated logic, or for providing an additional layer of abstraction from your source tables as a security measure. Along with a good indexing strategy, views can be used to simplify highly normalized tables. The more joins within a view, and the more columns it returns back, the slower it is going to be. If you use views, try to make them only perform one function and only return back a finite number of columns. You don’t want views that return back a lot of columns when the majority of your queries only use one or two columns. Here are some Do’s and Don’ts: Do use views if you want Data Analysts to access common queries. Do make sure all the join columns in a view contain indexes. Do Not call views from within views. Try not to return a lot of columns within a view if the view is referenced a lot. Do Not use SELECT * within a view. Specify the columns names explicitly. Indexed (or Materialized Views) A materialized view is a view that has been indexed and has essentially […]
Continue reading ...
The SQL Replace function replaces a value within a string with another value. The final output is the original string with the replaced value. There are three parameters to the Replace function. The first is the original string. The next parameter is the string to replace. And the last parameter is the value to replace the string with. Here is an example: [cc lang=”sql”] SELECT REPLACE ( ‘original string’, — Original String ‘string’, — String to replace ‘replaced’ — What to replace with ) [/cc] The output from this is: “original replaced”. One thing you need to watch out for is that if any of the parameters in the replace statement are NULL, then the string that is returned will also be NULL. So if passing parameters dynamically and there is a chance of NULL, make sure to use the ISNULL function and turn it into an empty string. You can also sometimes get away with using REPLACE rather than concatenating strings. I’ve done this before when you have a templated piece of text that needs values injected into it. [cc lang=”sql”] DECLARE @ResponseMessage varchar(max); SET @ResponseMessage = ‘Dear [firstname], thank you for signing up for [subscription].’; SELECT @ResponseMessage = REPLACE ( @ResponseMessage, ‘[firstname]’, pc.FirstName ) FROM Person.Contact pc WHERE ContactID = 1; SELECT @ResponseMessage; [/cc] Though it may not seem like much of a big deal, when dealing with many lines of concatenated text, it comes out much cleaner.
Continue reading ...
