smallseo.info

sql-server interview questions

Top sql-server frequently asked interview questions

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?


Source: (StackOverflow)

What is the difference between char, nchar, varchar, and nvarchar in SQL Server?

What is meant by nvarchar?

What is the difference between char, nchar, varchar, and nvarchar in SQL Server?


Source: (StackOverflow)

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?


Source: (StackOverflow)

How can I remove duplicate rows?

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

Source: (StackOverflow)

How to return the date part only from a SQL Server datetime datatype

SELECT GETDATE()

Returns: 2008-09-22 15:24:13.790

I want that date part without the time part: 2008-09-22 00:00:00.000


Source: (StackOverflow)

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?


Source: (StackOverflow)

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?


Source: (StackOverflow)

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.


Source: (StackOverflow)

Function vs. Stored Procedure in SQL Server

I've been learning Functions and Stored Procedure for quite a while but I don't know why and when I should use a function or a stored procedure. They look same to me, maybe because I am kinda newbie about that.

Can some one tell me why?


Source: (StackOverflow)

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?


Source: (StackOverflow)

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.)


Source: (StackOverflow)

SQL Server - when should you use "with (nolock)"

Can someone explain the implications of using with (nolock) on queries, when you should/shouldn't use it?

For example, if you have a banking application with high transaction rates and a lot of data in certain tables, in what types of queries would nolock be okay? Are there cases when you should always use it/never use it?


Source: (StackOverflow)

How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008.

But I get this error,

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

My code,

DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo
Where Company = '1' AND Date = '2013-05-06'

Source: (StackOverflow)

How to check if column exists in SQL Server table

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 in a table of SQL Server database?


Source: (StackOverflow)

When should I use Cross Apply over Inner Join?

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

Source: (StackOverflow)