Ever tried to reseed an identity column in a table variable? No? Let's give it a go…
Let's declare a simple table variable, @country, and insert a few rows.
DECLARE @country TABLE ( CountryId INT IDENTITY(1,1), CountryName VARCHAR(50) NOT null ); INSERT INTO @country ( CountryName ) VALUES ( 'Scotland' ), ( 'England' ), ( 'Wales' ), ( 'Northern Ireland' ) ; SELECT CountryId , CountryName FROM @country ; Truncating a normal table will remove all the rows and reseed the identity value.
This month's T-SQL Tuesday is hosted by Erin Stellato, and the theme is A Day In The Life. The premise is to track what you do over the course of a day as a SQL professional. My days can differ greatly, depending on the role I'm currently working in, and for what client. My current role is mainly database development and data migration work for an upgrade to an existing application.
The Grouping Sets feature was added to the T-SQL GROUP BY clause with the release of SQL Server 2008. Despite its usefulness, I've not come across many (or any) of them in the wild at any of the clients I've worked at in the past few years. Here is a primer on how to use them, using the AdventureWorks OLTP database.
SQL Server 2005 Style In SQL Server 2005, we could use the WITH ROLLUP syntax to add an aggregated total row:
It's been a while since I've posted any SQL Server content, so in a bid to get back into the swing of things, here are a couple of very simple, but useful, T-SQL commands. I've used these recently in a large project I'm working on.
Identify all tables with no Foreign Key constraints:
select distinct<br /> t.name<br /> from sys.tables t<br /> left outer join sys.foreign_keys fk on t.object_id = fk.