SQL interview questions

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

ALTER TABLE {TABLENAME} 

ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 

CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

WITH VALUES

---------------------

How to return only the Date from a SQL Server DateTime datatype

SELECT CONVERT(date, getdate())

---------------------

How to check if a column exists in a SQL Server table?

IF EXISTS(SELECT 1 FROM sys.columns 

          WHERE Name = N'columnName'

          AND Object_ID = Object_ID(N'schemaName.tableName'))

BEGIN

    -- Column Exists

END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL

BEGIN

    -- Column Exists

END

---------------------

Insert results of a stored procedure into a temporary table stackoverflow

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

-----------------------------

Altering a column: null to not null

UPDATE [Table] SET [Column]=0 WHERE [Column] IS NULL

Then, update the table definition to disallow "NULLs":

ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL

---------------------

How can I get column names from a table in SQL Server?

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = N'Customers'

"N" if for handlling unicode string like varchar in ANSI(32bit) and nvarchar in unicode(64bit) 

------------------

How do you change the datatype of a column in SQL Server?

ALTER TABLE TableName 

ALTER COLUMN ColumnName NVARCHAR(200) [NULL | NOT NULL]