Update: This article in my blog summarizes both my answer and my comments to another answers, and shows actual execution plans: IN vs. JOIN vs. EXISTS SELECT * FROM a WHERE a.c IN (SELECT d FROM b) SELECT a.* FROM a JOIN b ON a.c = b.d These queries are not equivalent. They can yield … Read more
You don’t mention if the columns are nullable. If they are and you want the same semantics that the AVG aggregate provides you can do (2008) SELECT *, (SELECT AVG(c) FROM (VALUES(R1), (R2), (R3), (R4), (R5)) T (c)) AS [Average] FROM Request The 2005 version is a bit more tedious SELECT *, (SELECT AVG(c) FROM … Read more
use xp_cmdshell and sqlcmd EXEC xp_cmdshell ‘sqlcmd -S ‘ + @DBServerName + ‘ -d ‘ + @DBName + ‘ -i ‘ + @FilePathName
Here’s a script I used for a similiar purpose. It does not support composite foreign keys (which use more than one field.) And it would probably need some tweaking before it will work for your situation. EDIT: In particular it does not handle multi-column foreign keys correctly. select DropStmt=”ALTER TABLE [” + ForeignKeys.ForeignTableSchema + ‘].[‘ … Read more
There are many ways to store hierarchies in SQL databases. Which one to choose depends on which DBMS product you use, and how the data will be used. As you have used the MSSQL2005 tag, I think you should start considering the “Adjacency List” model; if you find that it doesn’t perform well for your … Read more
Easy: SELECT Val1, Val2, Val3, (Val1 + Val2 + Val3) as ‘Total’ FROM Emp or if you just want one row: SELECT SUM(Val1) as ‘Val1’, SUM(Val2) as ‘Val2’, SUM(Val3) as ‘Val3’, (SUM(Val1) + SUM(Val2) + SUM(Val3)) as ‘Total’ FROM Emp
You would mostly be using COUNT to summarize over a UID. Therefore COUNT([uid]) will produce the warning: Warning: Null value is eliminated by an aggregate or other SET operation. whilst being used with a left join, where the counted object does not exist. Using COUNT(*) in this case would also render incorrect results, as you … Read more
The advantages are that you can use functions like LEN and LEFT on nvarchar(max) and you cannot do that against ntext and text. It is also easier to work with nvarchar(max) than text where you had to use WRITETEXT and UPDATETEXT. Also, text, ntext, etc., are being deprecated (http://msdn.microsoft.com/en-us/library/ms187993.aspx)
Yes, SQL 11 has SEQUENCE objects, see SQL Server v.Next (Denali) : Using SEQUENCE. Creating manual sequences is possible, but not recommended. The trick to do a sequence generator is to use UPDATE WITH OUTPUT on a sequences table. Here is pseudo-code: CREATE TABLE Sequences ( Name sysname not null primary key, Sequence bigint not … Read more
You can reference the column name from the val for col in part of the unpivot. Col gets the column name Example Fiddle — Build list of cols we want to unpivot (skip PID & UID) declare @cols nvarchar(max) select @cols = coalesce(@cols+N’,’, N”) + quotename(c.name) from syscolumns c inner join sysobjects o on c.id … Read more