Newbe Dev Stack

Answer : Add an identity to your table variable, and do an easy loop from 1 to the @@ROWCOUNT of the INSERT-SELECT. Try this: DECLARE @RowsToProcess int DECLARE @CurrentRow int DECLARE @SelectCol1 int DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 int ) INSERT into @table1 (col1) SELECT col1 FROM table2 SET @RowsToProcess=@@ROWCOUNT SET @CurrentRow=0 WHILE @CurrentRow<@RowsToProcess BEGIN SET @CurrentRow=@CurrentRow+1 SELECT @SelectCol1=col1 FROM @table1 WHERE RowID=@CurrentRow --do your thing here-- END DECLARE @table1 TABLE ( idx int identity(1,1), col1 int ) DECLARE @counter int SET @counter = 1 WHILE(@counter < SELECT MAX(idx) FROM @table1) BEGIN DECLARE @colVar INT SELECT @colVar = col1 FROM @table1 WHERE idx = @counter -- Do your work here SET @counter = @counter + 1 END Believe it or not, this is actually more efficient and performant than using a cursor. ...

Answer : Strictly, method a is the least resource intensive: a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) Proven less CPU intensive for the same total duration a million rows by someone with way too much time on their hands: Most efficient way in SQL Server to get a date from date+time? I saw a similar test elsewhere with similar results too. I prefer the DATEADD/DATEDIFF because: varchar is subject to language/dateformat issues Example: Why is my CASE expression non-deterministic? float relies on internal storage it extends to work out first day of month, tomorrow, etc by changing "0" base Edit, Oct 2011 For SQL Server 2008+, you can CAST to date i.e. CAST(getdate() AS date) . Or just use date datatype so no time to remove. Edit, Jan 2012 A worked example of how flexible this is: Need to calculate by rounded time or date figure in sql server Edit, May 2012 Do not use this in WHERE clauses and the like without thinking: adding a function or CAST...

Answer : SQL Server does not currently support subqueries for CHECK CONSTRAINTs. As you have discovered, there can be trouble with CHECK constraints involving UDFs when attempting to circumvent the subquery limitation. The alternative constraint implementation strategies are triggered procedural and embedded procedural . The former is preferred because, in common with declarative constraints, they cannot be circumvented. Implementing a triggered procedural strategy that is well optimized and handles concurrency issues is non-trivial but still doable. I highly recommend the book Applied Mathematics for Database Professionals By Lex de Haan, Toon Koppelaars, chapter 11 (the code examples are Oracle but can be easily ported to SQL Server). As others have mentioned already, this type of Check constraints is not yet implemented in SQL-Server. Besides triggers, you could also examine the possibility of changing the table's design. A possible alternative includes storing the...