EzDevInfo.com

UPDATE from SELECT using SQL Server

In SQL Server, it's possible to insert into a table using a SELECT statement:

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

Is it also possible to update via a SELECT? I have a temporary table containing the values, and would like to update another table using those values. Perhaps something like this:

UPDATE Table SET col1, col2
    SELECT col1, col2 FROM other_table WHERE sql = 'cool'
    WHERE Table.id = other_table.id

Add a column, with a default value, to an existing table in SQL Server

How can a column with a default value be added to an existing table in SQL Server 2000 / SQL Server 2005?

Best way to get identity of inserted row?

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?

Should I use != or <> for not equal in TSQL?

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

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

How do I save a stream to a file in C#?

I have a StreamReader object that I initialized with a stream, now I want to save this stream to disk (the stream may be a .gif or .jpg or .pdf).

Existing Code:

StreamReader sr = new StreamReader(myOtherObject.InputStream);

1. I need to save this to disk (I have the filename).
2. In the future I may want to store this to SQL Server.

I have the encoding type also, which I will need if I store it to SQL Server, correct?

Insert results of a Stored Procedure into a Temporary Table

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.

Count(*) vs Count(1)

Just wondering if any of you guys use Count(1) over Count(*) and if there is a noticeable difference in performance or if this is just a legacy habit that has been brought forward from days gone past?

(The specific database is SQL Server 2005.)

SQL Server datetime2 vs datetime

Which one:

• datetime
• datetime2

is THE recommended way to store date and time in SQL Server 2008+?

I'm aware of differences in precision (and storage space probably), but ignoring those for now, is there a best practice document on when to use what, or maybe we should just use datetime2 only?

Altering a column: null to not null

I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to NOT NULL. Aside from changing nulls to 0, data must be preserved.

I am looking for the specific SQL syntax to alter a column (call it ColumnA) to "not null". Assume the data has been updated to not contain nulls.

Using SQL server 2000.

How can foreign key constraints be temporarily disabled using T-SQL?

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

Difference between JOIN and INNER JOIN

Both these joins will give me the same results:

SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FK

vs

SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FK

Is there any difference between the statements in performance or otherwise?

Does it differ between different SQL implementations?

Check if table exists in SQL Server

I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statement.

When you Google for the answer, you get so many different answers. Is there an official/backward & forward compatible way of doing it?

Here are two possible ways of doing it. Which one among the two is the standard/best way of doing it?

First way:

IF EXISTS (SELECT 1 
           FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_TYPE='BASE TABLE' 
           AND TABLE_NAME='mytablename') 
   SELECT 1 AS res ELSE SELECT 0 AS res;

Second way:

IF OBJECT_ID (N'mytablename', N'U') IS NOT NULL 
   SELECT 1 AS res ELSE SELECT 0 AS res;

MySQL provides the simple SHOW TABLES LIKE '%tablename%'; statement. I am looking for something similar.

What do Clustered and Non clustered index actually mean?

I have a limited exposure to DB and have only used DB as an application programmer. I want to know about Clustered and Non clustered indexes. I googled and what I found was :

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

What I found in SO was What are the differences between a clustered and a non-clustered index?.

Can someone explain this in plain English?

Update a table using JOIN in SQL Server?

I want to update a column in a table making a join on other table e.g.:

UPDATE table1 a 
INNER JOIN table2 b ON a.commonfield = b.[common field] 
SET a.CalculatedColumn= b.[Calculated Column]
WHERE 
    b.[common field]= a.commonfield
AND a.BatchNO = '110'

But it is complaining :

Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'a'.

What is wrong here?

What is the difference between varchar and nvarchar?

Is it just that nvarchar supports multibyte characters? If that is the case, is there really any point, other than storage concerns, to using varchars?