Inserting Specific Identity Values

SET
  #a (i,j)
INSERT
SELECT 2,2
 SET
  * FROM #a
SELECT IDENTITY_INSERT #a OFF IDENTITY_INSERT #a ON

How to identify Indentiy column in a table and also max value for that Identity column

DBCC checkident (tablename)Eg:

create table b (i int identity ,n int)

 insert into b
select 122
union
select 135

select * from b 

 dbcc checkident(b)

How to change the replication working directory on the distributor?

By default every publisher stores it snapshot data in the MSSQL7\Repldate folder on the distributor. If you are running out of disk space on that particular you need to change the working directory to another drive. You need to change this path for each publisher, as it is not a global setting on the distributor.

There are two ways to do this:

Method #1:
- Open Enterprise Manager
- Connect to the Distribution server
- Go to Tools -> Replication -> Configure Publishing, Subscribers, and Distribution...
- Go to Publishers tab
- Click on the properties button (...) against the publisher's name
- Enter the new path for the Snapshot folder

Method #2:
Run sp_changedistpublisher from query analyzer. For more information on sp_changedistpublisher see Books Online

How to maintain IDENTITY property of a column on both publishing and subscribing databases in transactional replication?

Follow these steps to maintain IDENTITY property of a column on both publishing and subscribing databases:

1) Before subscribing, you must create the table at the subscriber (by default, replication will not transfer the IDENTITY property to the subscribers) and don't forget to create the IDENTITY property with NOT FOR REPLICATION option.

2) After creating the publication, go to publication properties window and choose the Articles tab. Click on the properties button (...) against the table that has IDENTITY property. Go to 'Snapshot' tab. Choose 'Keep the existing table unchanged'.

3) By default SQL Server 7.0 applies the transactions to the subscriber using stored procedures (these procedures will get created during the application of initial snapshot). Before you subscribe, you can choose to continue using stored procedures to apply the transactions on the subscriber OR you can choose to apply the transactions to the subscriber using SQL statements.
If you choose to use SQL statements, jump to step 6. If you choose to go by the default behavior (stored procedures) follow steps 4 and 5.

4) Go to the publication properties, choose 'Articles' tab, click on the properties button (...) against the table that has IDENTITY property, click on the 'Commands' tab and uncheck 'Create the stored procedures specified above during the initial synchronization of the subscribers'. Using sp_scriptinsproc, sp_scriptmappedupdproc, sp_scriptdelproc (See SQL Server 7.0 Books Online for more information) generate the scripts for these procedures.
Add a column list to the INSERT statement in the INSERT stored procedure script. In the UPDATE stored procedure script, remove the line in the UPDATE statement which tries to update the IDENTITY column. Apply these scripts on the subscriber.

5) Push or pull the subscription.

6) If you choose to use SQL statements to apply transactions to the subscriber, go to the publication properties window, choose articles tab, click on the properties button (...) against the table that has IDENTITY property, choose 'Commands' tab. Uncheck the check marks against the procedure calls and check the box against 'Use column names in the SQL statements'.

7) Push or pull the subscription.

Microsoft SQL Home page link

http://www.microsoft.com/sqlserver/en/us/default.aspx

Find the column level collation using t-sql

select object_name(object_id)as Tablename,name as Column_Name, collation_name from sys.columns

What is the difference between SET and SELECT when assigning values to variables, in T-SQL?

I will highlight all the major differences between SET and SELECT, and things you should be aware of, when using either SET or SELECT.
DECLARE
SELECT @Variable1 AS int, @Variable2 AS int @Variable1 = 1 SET @Variable2 = 2 SET is the ANSI standard way of assigning values to variables, and SELECT is not

U can use SELECT to assign values to more than one variable at a time. SET allows you to assign data to only one variable at a time.
Here's how:
DECLARE @Variable1 AS int, @Variable2 AS int @Variable1 = 1, @Variable2 = 2SET @Variable1 = 1SET

So far so good
DECLARE
SELECT. But if you ever wrote error handling code in T-SQL, you most probably are aware that, the system variables @@ERROR and @@ROWCOUNT must be captured in one statement, immediately after a data manipulation (DML) statement like INSERT, UPDATE, DELETE, or else, these system variables get reset to 0. So, if you want to stick to the standards and use SET in this scenario, you are out of luck. The following example demonstrates the problem: @Error int, @RowCount int price/0 FROM dbo.titlesSET @RowCount = @@ROWCOUNTSET @Error = @@ERRORSELECT @Error AS ErrorGO
If
DECLARE
SELECT you run the above piece of code in pubs database, the value of @@ERROR system variable will be displayed as 0, even though the 'division by zero' resulted in error 8134. So, in this particular scenario, forget about standards and use SELECT, as shown below: @Error int, @RowCount int price/0 FROM dbo.titlesSELECT @RowCount = @@ROWCOUNT, @Error = @@ERRORSELECT
But
DECLARE
SELECT @Error AS Errorif you insist on using SET even in this scenario, there's always a way out. Here's one example, though not readable and recommended: @ErrorAndRowcount AS varchar(25), @Error int, @RowCount int price/0 FROM dbo.titlesSET @ErrorAndRowcount = CAST(@@ERROR AS varchar(12)) + '.' + CAST(@@ROWCOUNT AS varchar(12))SET @Error = CAST(PARSENAME(@ErrorAndRowcount, 2) AS int)SET @RowCount = CAST(PARSENAME(@ErrorAndRowcount, 1) AS int)SELECT @Error AS Error, @RowCount AS Row_CountSET @Error = CAST(LEFT(@ErrorAndRowcount, CHARINDEX('.', @ErrorAndRowcount)-1) AS int)SET @RowCount = CAST(RIGHT(@ErrorAndRowcount, CHARINDEX('.', REVERSE(@ErrorAndRowcount))-1) AS int)SELECT @Error AS Error, @RowCount AS Row_CountGOMoving
SET
CREATEon to other differences between SET and SELECT: When using a query to populate a variable, SET will fail with an error, if the query returns more than one value. But SELECT will assign one of the returned rows and mask the fact that the query returned more than one row. As a result, bugs in your code could go unnoticed with SELECT, and this type of bugs are hard to track down too. Here is an example: NOCOUNT ON TABLE #Test (i int, j varchar(10))INSERT INTO #Test (i, j) VALUES (1, 'First Row')INSERT INTO #Test (i, j) VALUES (1, 'Second Row')GO
DECLARE @j varchar(10)SELECT @j = j FROM #Test WHERE i = 1SELECT @jGO
DECLARE @j varchar(10)SET @j = (SELECT j FROM #Test WHERE i = 1)SELECT @jServer:
Subquery returned more than 1 value Msg 512, Level 16, State 1, Line -1074284106. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Based
DECLAREon the above results, when using a query to populate variables, I suggest you always use SET, if you want to be sure that only one row is returned. If you hate SET for some reason, you could get the same behavior of SET, using SELECT, as shown below: @j varchar(10)SELECT @j = (SELECT j FROM #Test WHERE i = 1)SELECT
Here
/* Returns NULL */
DECLARE @Title varchar(80)
SET @Title = '
SET @Title =
(
SELECT title
FROM dbo.titles
WHERE title_id = '
)
SELECT @Title
GO
/* Returns the string literal '
DECLARE @Title varchar(80)
SET @Title = '
SELECT @Title = title
FROM dbo.titles
WHERE title_id = '
SELECT @Title
GO @jis another difference with respect to assigning values based on a query, especially when the query doesn't return any rows. Run the following example in the pubs database, and you will see what I mean:Not Found'InvalitTitleID'Not Found' */Not Found'InvalitTitleID' @Variable2 = 2
SELECT

To concatenate the values of a column in all rows into one row

Consider we have table like this
id  name
1  Yesy
2  Yesy

expected result to concatenate the values of a name column in all rows into one row

Solution:

select
 DECLARE
SET @title_ids varchar(max), @delimiter char @delimiter = ','SELECT @title_ids = COALESCE(@title_ids + @delimiter, '') + name FROM test4SELECT @title_ids AS [List of Title IDs] * from test4

IFNULL

The IFNULL command is not used in Oracle or SQL Server. It performs the same function of the ISNULL implementation in MS SQL Server but is used in mySQL.
This is where the differences between the various SQL database vendors becomes apparent. Rather than sticking to the standard, they all have their own variations.
In short, for mySQL servers, ISNULL will replace NULL values with a user specified value.
Say we have a table called orders like this:

---------------------
|order_id |quantity |
---------------------
|1        |10       |
---------------------
|2        |10       |
---------------------
|3        |10       |
---------------------
|4        |NULL     |
---------------------

Now, we will look at how to change NULL in mySQL.

SELECT SUM(IFNULL(quantity,5)) AS result FROM orders;
--------
|result|
--------
|35    |
--------

As you can see, we have replaced the NULL values with 5.

What is the difference between SYNONYM and ALIAS?

SYNONYM: is dropped when the table or tablespace is dropped. Synonym is available only to the creator.

ALIAS: is retained even if table or tablespace is dropped. ALIAS can be created even if the table does not exist. It is used mainly in distributed environment to hide the location info from programs. Alias is a global object & is available to all.

Synonym

As a synonym is an abstraction/alternative name for an already existing database object,
in the case of a table, index behaviour is identical to that of the underlying object i.e. when execution plans are generated, the same plan is generated irrespective of using the table name or corresponsing synonym

What is the system function to get the current user's user id?

USER_ID(). Also check out other system functions like USER_NAME(),
SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?

Cursors allow row-by-row prcessing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven.

Disadvantages of cursors: Each time you fetch a row from the cursor,
it results in a network roundtrip, where as a normal SELECT query
makes only one rowundtrip, however large the resultset is. Cursors are
also costly because they require more resources and temporary storage
(results in more IO operations). Furthere, there are restrictions on
the SELECT statements that can be used with some types of cursors.

 

Most of the times, set based operations can be used instead of
cursors. Here is an example:

 

If you have to give a flat hike to your employees using the following
criteria:

 

Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike

 

In this situation many developers tend to use a cursor, determine each
employee's salary and update his salary according to the above
formula. But the same can be achieved by multiple update statements or
can be combined in a single UPDATE statement as shown below:

 

UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END

 

Another situation in which developers tend to use cursors: You need to
call a stored procedure when a column in a particular row meets
certain condition. You don't have to use cursors for this. This can be
achieved using WHILE loop, as long as there is a unique key to
identify each row. For examples of using WHILE loop for row by row
processing, check out the 'My code library' section of my site or
search for WHILE.

 

Write down the general syntax for a SELECT statements covering all the
options.

 

Here's the basic syntax: (Also checkout SELECT in books online for
advanced syntax).

 

SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by__expression]
[HAVING search_condition]
[ORDER BY order__expression [ASC | DESC] ]

What is database replicaion? What are the different types of replication you can set up in SQL Server?

Replication is the process of copying/moving data between databases on
the same or different servers. SQL Server supports the following types
of replication scenarios:

    * Snapshot replication
    * Transactional replication (with immediate updating subscribers,
with queued updating subscribers)
    * Merge replication

what are the DBCC commands commonly use for database maintenance?

DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC,
DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE ,DBCC CHECKIDENT etc.,

What are constraints?

Constraints enable the RDBMS enforce the integrity of the database
automatically, without needing you to create triggers, rule or defaults.

Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

What's the difference between DELETE TABLE and TRUNCATE TABLE commands?

DELETE TABLE is a logged operation, so the deletion of each row gets
logged in the transaction log, which makes it slow. TRUNCATE TABLE
also deletes all the rows in a table, but it won't log the deletion of
each row, instead it logs the deallocation of the data pages of the
table, which makes it faster. Of course, TRUNCATE TABLE can be rolled
back.

What is a transaction and what are ACID properties?

A transaction is a logical unit of work in which, all the steps must
be performed or none. ACID stands for Atomicity, Consistency,
Isolation, Durability. These are the properties of a transaction. For
more information and explanation of these properties, see SQL Server
books online or any RDBMS fundamentals text book

Define candidate key, alternate key, composite key

A candidate key is one that can identify each row of a table uniquely.
Generally a candidate key becomes the primary key of the table. If the
table has more than one candidate key, one of them will become the
primary key, and the rest are called alternate keys.

 

Dependent tables used in stored procs.

CREATE PROCEDURE USEDTABLESAS
BEGIN
DECLARE @qry NVARCHAR(MAX),@dbname VARCHAR(100),@dbid VARCHAR(3)
DECLARE @table TABLE (SLno INT IDENTITY(1, 1),Tablename VARCHAR(500),Tableid varchar(100),Dbname VARCHAR(100),Dbid INT)
DECLARE @table2 TABLE (Slno INT, Tablename VARCHAR(500))
DECLARE @table3 TABLE (Spname VARCHAR(500), spcode VARCHAR(MAX))
DECLARE @table4 TABLE (Spname VARCHAR(500), Tablename VARCHAR(500))
DECLARE DB CURSOR FOR
SELECT [Name],database_id FROM sys.DatabasesWHERE database_id in (10)
OPEN DBFETCH NEXT FROM DB INTO @dbname,@dbid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @qry = 'SELECT [Name],object_id,''' + @dbname + ''',''' + @dbid + ''' FROM ' + @dbname + '.sys.columns '
INSERT INTO @table
EXEC (@qry)
FETCH NEXT FROM DB INTO @dbname,@dbid
END
CLOSE DBDEALLOCATE DBINSERT INTO @table2
SELECT Slno,Dbname + '.dbo.' + Tablename AS TablenameFROM @table
INSERT INTO @table3  SELECT [Name] AS Spname,OBJECT_DEFINITION(OBJECT_ID) AS spcode FROM sys.procedures
DECLARE @value VARCHAR(500)
DECLARE SP CURSOR FOR
SELECT Tablename FROM @table2 OPEN SP FETCH NEXT FROM SP INTO @value WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @table4 SELECT spname,@valueFROM (SELECT Spname,spcode, PATINDEX('%' + @value + '%', spcode) AS idx FROM @table3) AS a WHERE  idx > 0
FETCH NEXT FROM SP INTO @valueEND
CLOSE Sp
DEALLOCATE Sp

SELECT DISTINCT t.Spname,t.Tablename,b.Dbname,b.Tablename as t into##ttFROM @table4 t,@table2 a,@table b WHERE t.Tablename = a.Tablename and a.Slno = b.SLno
END