Top tsql 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

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

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.

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
191
John

Scenario:

Let's say I have two tables, TableA and TableB. TableB's primary key is a single column (BId), and is a foreign key column in TableA.

In my situation, I want to remove all rows in TableA that are linked with specific rows in TableB: Can I do that through joins? Delete all rows that are pulled in from the joins?

DELETE FROM TableA 
FROM
   TableA a
   INNER JOIN TableB b
      ON b.BId = a.BId
      AND [my filter condition]

Or am I forced to do this:

DELETE FROM TableA
WHERE
   BId IN (SELECT BId FROM TableB WHERE [my filter condition])

The reason I ask is it seems to me that the first option would be much more effecient when dealing with larger tables.

Thanks!

Answered By: TheTXI ( 275)
DELETE TableA 
FROM TableA a
INNER JOIN
TableB b on b.Bid = a.Bid
and [my filter condition]

should work

186
Jeff Meatball Yang

What is the main purpose of using CROSS APPLY?

I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind)

I also know that CROSS APPLY doesn't require a UDF as the right-table.

In most INNER JOIN queries (one-to-many relationships), I could rewrite them to use CROSS APPLY, but they always give me equivalent execution plans.

Can anyone give me a good example of when CROSS APPLY makes a difference in those cases where INNER JOIN will work as well?


Edit:

Here's a trivial example, where the execution plans are exactly the same. (Show me one where they differ and where cross apply is faster/more efficient)

create table Company (
    companyId int identity(1,1)
,   companyName varchar(100)
,   zipcode varchar(10) 
,   constraint PK_Company primary key (companyId)
)
GO

create table Person (
    personId int identity(1,1)
,   personName varchar(100)
,   companyId int
,   constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId)
,   constraint PK_Person primary key (personId)
)
GO

insert Company
select 'ABC Company', '19808' union
select 'XYZ Company', '08534' union
select '123 Company', '10016'


insert Person
select 'Alan', 1 union
select 'Bobby', 1 union
select 'Chris', 1 union
select 'Xavier', 2 union
select 'Yoshi', 2 union
select 'Zambrano', 2 union
select 'Player 1', 3 union
select 'Player 2', 3 union
select 'Player 3', 3 


/* using CROSS APPLY */
select *
from Person p
cross apply (
    select *
    from Company c
    where p.companyid = c.companyId
) Czip

/* the equivalent query using INNER JOIN */
select *
from Person p
inner join Company c on p.companyid = c.companyId
Answered By: Quassnoi ( 148)

Can anyone give me a good example of when CROSS APPLY makes a difference in those cases where INNER JOIN will work as well?

See the article article in my blog for detailed performance comparison:

CROSS APPLY works better on things that have no simple JOIN condition.

This one selects 3 last records from t2 for each record from t1:

SELECT  t1.*, t2o.*
FROM    t1
CROSS APPLY
        (
        SELECT  TOP 3 *
        FROM    t2
        WHERE   t2.t1_id = t1.id
        ORDER BY
                t2.rank DESC
        ) t2o

It cannot be easily formulated with an INNER JOIN condition.

You could probably do something like that using CTE's and window function:

WITH    t2o AS
        (
        SELECT  t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn
        FROM    t2
        )
SELECT  t1.*, t2o.*
FROM    t1
INNER JOIN
        t2o
ON      t2o.t1_id = t1.id
        AND t2o.rn <= 3

, but this is less readable and probably less efficient.

Update:

Just checked.

master is a table of about 20,000,000 records with a PRIMARY KEY on id.

This query:

WITH    q AS
        (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY id) AS rn
        FROM    master
        ),
        t AS 
        (
        SELECT  1 AS id
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    t
JOIN    q
ON      q.rn <= t.id

runs for almost 30 seconds, while this one:

WITH    t AS 
        (
        SELECT  1 AS id
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    t
CROSS APPLY
        (
        SELECT  TOP (t.id) m.*
        FROM    master m
        ORDER BY
                id
        ) q

is instant.

182
Bob The Janitor

Possible Duplicate:
Testing for inequality in T-SQL

I have seen SQL that uses both != and <> for not equal. What is the prefigured syntax and why?

I like != because <> reminds me of Visual Basic.

Answered By: DBAndrew ( 134)

Technically they function the same if you’re using MS SQL aka T-SQL. If you're using it in stored procedures there is no performance reason to use one over the other. It then comes down to personal preference. I prefer to use <> as it is ANSI compliant.

You can find links to the various ANSI standards at...

http://en.wikipedia.org/wiki/SQL

170
Oded

What is the best way to get identity of inserted row?

I know about @@IDENTITY and IDENT_CURRENT and SCOPE_IDENTITY but don't understand the pros and cons attached to each.

Can someone please explain the differences and when I should be using each?

Answered By: bdukes ( 197)
  • @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.
  • SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.
  • IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). Also, as @Guy Starbuck mentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."
  • The OUTPUT clause of the INSERT statement will let you access every row that was inserted via that statement. Since it's scoped to the specific statement, it's more straightforward than the other functions above. However, it's a little more verbose (you'll need to insert into a table variable/temp table and then query that) and it gives results even in an error scenario where the statement is rolled back. That said, if your query uses a parallel execution plan, this is the only guaranteed method for getting the identity (short of turning off parallelism).