Top sql-server Questions

List of Tags
367
sparkyfied

In SQL Server you can insert into a table using a select statement:

INSERT INTO table(col,col2,col3)
SELECT col,col2,col3 FROM other_table WHERE sql = 'cool'

How can I update via a select as well in a similar manner? I have a temporary table that has the values, and I want to update another table using those values.

Something like this:

UPDATE Table SET col1,col2
SELECT col1,col2 FROM other_table WHERE sql = 'cool'
WHERE Table.id = other_table.id
Answered By: Robin Day ( 592)
UPDATE
    Table
SET
    Table.col1 = other_table.col1,
    Table.col2 = other_table.col2
FROM
    Table
INNER JOIN
    other_table
ON
    Table.id = other_table.id
351
Mathias

How do you add a column, with a default value, to an existing table in SQL Server 2000/2005?

Answered By: James Boother ( 482)
ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]?

Select all data from BusinessLine into tmpBusLine works fine.

select * into tmpBusLine
from BusinessLine

Trying the same, but using a stored procedure that returns data, is not quite the same.

select * into tmpBusLine from
exec getBusinessLineHistory '16 Mar 2009'

Output message:

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'exec'.

I have read several examples of creating a temporary table with the same structure as the output stored procedure, which works fine, but it would be nice to not supply any columns.

I am using SQL Server 2005.

Answered By: Aaron Alton ( 168)

You can use OPENROWSET for this. Have a look. I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable
307
Maciej

I need to add a specific column if it does not exist. I have something like this, but it always returns false.:

IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = 'myTableName' 
           AND  COLUMN_NAME = 'myColumnName')

How can I check if a column exists on a table in SQL Server?

Answered By: Mitch Wheat ( 384)

SQL Server 2005 onwards:

if exists(select * from sys.columns 
            where Name = N'columnName' and Object_ID = Object_ID(N'tableName'))    
begin
    -- Column Exists
end
301
Jeff Atwood

How do I parameterize a query containing an IN clause with a variable number of arguments, like this one?

select * from Tags 
where Name in ('ruby','rails','scruffy','rubyonrails')
order by Count desc

In this query, the number of arguments could be anywhere from 1 to 5.

I would prefer not to use a dedicated stored procedure for this (or XML), but if there is some fancy SQL Server 2008 specific way of doing it elegantly, I am open to that.

Answered By: Mark Brackett ( 301)

You can parameterize each value, so something like:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
string cmdText = "SELECT * FROM Tags WHERE Name IN ({0})";

string[] paramNames = tags.Select(
    (s, i) => "@tag" + i.ToString()
).ToArray();

string inClause = string.Join(",", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
    for(int i = 0; i < paramNames.Length; i++) {
       cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
    }
}

Which will give you:

cmd.CommandText = "SELECT * FROM Tags WHERE Name IN (@tag0,@tag1,@tag2,@tag3)"
cmd.Parameters["@tag0"] = "ruby"
cmd.Parameters["@tag1"] = "rails"
cmd.Parameters["@tag2"] = "scruffy"
cmd.Parameters["@tag3"] = "rubyonrails"

No, this is not open to SQL injection. The only injected text into CommandText is not based on user input. It's solely based on the hardcoded "@tag" prefix, and the index of an array. The index will always be an integer, is not user generated, and is safe.

The user inputted values are still stuffed into parameters, so there is no vulnerability there.

Edit:

Injection concerns aside, take care to note that constructing the command text to accomodate a variable number of parameters (as above) impede's SQL server's ability to take advantage of cached queries. The net result is that you almost certainly lose the value of using parameters in the first place (as opposed to merely inserting the predicate strings into the SQL itself).

Not that cached query plans aren't valuable, but IMO this query isn't nearly complicated enough to see much benefit from it. While the compilation costs may approach (or even exceed) the execution costs, you're still talking milliseconds.

If you have enough RAM, I'd expect SQL Server would probably cache a plan for the common counts of parameters as well. I suppose you could always add five parameters, and let the unspecified tags be NULL - the query plan should be the same, but it seems pretty ugly to me and I'm not sure that it'd worth the micro-optimization (although, on Stack Overflow - it may very well be worth it).

Also, SQL Server 7 and later will auto-parameterize queries, so using parameters isn't really necessary from a performance standpoint - it is, however, critical from a security standpoint - especially with user inputted data like this.

Are disabling and enabling foreign key constraints supported in SQL Server? Or is my only option to 'drop and then re-'create' the constraints?

Answered By: kristof ( 350)

If you want to disable all constraints in the database just run this code:

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

To switch them back on, run: (the print is optional of course and it is just listing the tables)

-- enable all constraints
exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

I find it useful when populating data from one database to another. It is much better approach than dropping constraints. As you mentioned it comes handy when dropping all the data in the database and repopulating it (say in test environment).

If you are deleting all the data you may find this solution to be helpful.

Also sometimes it is handy to disable all triggers as well, you can see the complete solution here.

Convention has it that table names should be the singular of the entity that they store attributes of.

I dislike any T-SQL that requires square brackets around names, but I have renamed a Users table to the singular, forever sentencing those using the table to sometimes have to use brackets.

My gut feel is that it is more correct to stay with the singular, but my gut feel is also that brackets indicate undesirables like column names with spaces in them etc.

Should I stay, or should I go?

Answered By: Brian Boatright ( 148)

If you use Object Relational Mapping tools or will in the future I suggest Singular.

Some tools like LLBLGen can automatically correct plural names like Users to User without changing the table name itself. Why does this matter? Because when it's mapped you want it to look like User.Name instead of Users.Name or worse from some of my old databases tables naming tblUsers.strName which is just confusing in code.

My new rule of thumb is to judge how it will look once it's been converted into an object.

one table I've found that does not fit the new naming I use is UsersInRoles. But there will always be those few exceptions and even in this case it looks fine as UsersInRoles.Username.

What is the best way to remove duplicate rows from a fairly large table (i.e. 300,000+ rows)?

The rows of course will not be perfect duplicates because of the existence of the RowID identity field.

MyTable
-----------
RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null
Answered By: Mark Brackett ( 258)

Assuming no nulls, you GROUP BY the unique columns, and SELECT the MIN (or MAX) RowId as the row to keep. Then, just delete everything that didn't have a row id:

DELETE MyTable 
FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, Col1, Col2, Col3 
   FROM MyTable 
   GROUP BY Col1, Col2, Col3
) as KeepRows ON
   MyTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

Use

CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn))) 

instead of MIN(RowId) if you have a GUID instead of an integer

215
Sklivvz

What are some hidden features of SQL Server?

For example, undocumented system stored procedures, tricks to do things which are very useful but not documented enough?


Answers

Thanks to everybody for all the great answers!

Stored Procedures

  • sp_msforeachtable: Runs a command with '?' replaced with each table name (v6.5 and up)
  • sp_msforeachdb: Runs a command with '?' replaced with each database name (v7 and up)
  • sp_who2: just like sp_who, but with a lot more info for troubleshooting blocks (v7 and up)
  • sp_helptext: If you want the code of a stored procedure, view & UDF
  • sp_tables: return a list of all tables and views of database in scope.
  • sp_stored_procedures: return a list of all stored procedures
  • xp_sscanf: Reads data from the string into the argument locations specified by each format argument.
  • xp_fixeddrives:: Find the fixed drive with largest free space
  • sp_help: If you want to know the table structure, indexes and constraints of a table. Also views and UDFs. Shortcut is Alt+F1

Snippets

  • Returning rows in random order
  • All database User Objects by Last Modified Date
  • Return Date Only
  • Find records which date falls somewhere inside the current week.
  • Find records which date occurred last week.
  • Returns the date for the beginning of the current week.
  • Returns the date for the beginning of last week.
  • See the text of a procedure that has been deployed to a server
  • Drop all connections to the database
  • Table Checksum
  • Row Checksum
  • Drop all the procedures in a database
  • Re-map the login Ids correctly after restore
  • Call Stored Procedures from an INSERT statement
  • Find Procedures By Keyword
  • Drop all the procedures in a database
  • Query the transaction log for a database programmatically.

Functions

  • HashBytes()
  • EncryptByKey
  • PIVOT command

Misc

  • Connection String extras
  • TableDiff.exe
  • Triggers for Logon Events (New in Service Pack 2)
  • Boosting performance with persisted-computed-columns (pcc).
  • DEFAULT_SCHEMA setting in sys.database_principles
  • Forced Parameterization
  • Vardecimal Storage Format
  • Figuring out the most popular queries in seconds
  • Scalable Shared Databases
  • Table/Stored Procedure Filter feature in SQL Management Studio
  • Trace flags
  • Number after a GO repeats the batch
  • Security using schemas
  • Encryption using built in encryption functions, views and base tables with triggers
Answered By: GilM ( 92)

In Management Studio, you can put a number after a GO end-of-batch marker to cause the batch to be repeated that number of times:

PRINT 'X'
GO 10

Will print 'X' 10 times. This can save you from tedious copy/pasting when doing repetitive stuff.

206
Eric Labashosky

How do I perform an IF...THEN in an SQL SELECT statement?

For example;

SELECT IF(Obsolete = 'N' or InStock = 'Y';1;0) as Salable, * FROM Product
Answered By: Darrel Miller ( 242)

The CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server

SELECT CAST(
             CASE 
                  WHEN Obsolete = 'N' or InStock = 'Y' 
                     THEN 1 
                  ELSE 0 
             END AS bit) as Salable, * 
FROM Product

You only need to do the CAST if you want the result as a boolean value, if you are happy with an int, this works:

SELECT CASE 
            WHEN Obsolete = 'N' or InStock = 'Y' 
               THEN 1 
               ELSE 0 
       END as Salable, * 
FROM Product

CASE statements can be embedded in other CASE statements and even included in aggregates.

SQL Server Denali adds the IIF statement which is also available in access: (pointed out by Martin Smith)

SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Selable, * from Product