Welcome to Shivshanker's Blog

SELECT *
FROM employee AS e
WHERE 9=(select count(*)
from employee x
where x.sal > e.sal);

select top 10 * employee

select top 1 empsal from
(
Select top 5 empsal from employee
group by empsal
order by empsal desc
) as t
order by empsal asc

select top 1 * from
(
SELECT TOP 10 * FROM EMPLOYEE
ORDER BY SAL desc
) as abc
order by sal

Finding record by perticular no of rows
getting the row number of a record

Please tell me how to update column X1 of one table according to column X2 of another table if one column A is identical in both of them?

For getting 10th highest paid employee you can even use this

SELECT *
FROM employees AS e
WHERE 10=(select count(distinct salary)
from employees x
where x.salary >= e.salary)

so in generic to get any rank of highest paid employee use this stored procedure.

CREATE PROCEDURE highest_paid_employee(@rnk int)
AS
SELECT *
FROM employees AS e
WHERE @rnk=(select count(distinct salary)
from employees x
where x.salary >= e.salary)
GO

I havent handled the exception try to customize it for error handling and then use it.

Update c set X1=d.X2
from
table1 c,
table2 d
where
c.A=d.A

How do you get rid of the time part from the date returned by GETDATE function?

select convert(varchar,getdate(),105)

What is the T-SQL equivalent of IIF (immediate if/ternary operator) function of other programming languages?

case expr then expr_if_true else expr_if_false end

v can make use of the powerful rank construct for finding the 10th highest paid employee:

select empid from
(select empid, rank() over(orderby sal desc nulls last) as sal_rank
from employee)
where sal_rank = 10

For getting 10th highest paid employee sal from this querry

select distinct top 1 sal from (select distinct top 10 sal from employeee order by sal desc) a order by sal

Please Send me All Answer of Questions of SQL Server 2000

To find the 10th highest paid employee in an employee table

select rank,sal from(select rownum as rank,sal from(select distinct sal from employee order by sal desc))where rank=10;

How do you read transaction logs?
DBCC LOG (Your_Database_Name, 2)
Using 3rd party tool Log Explorer we can read transaction logs

How do you reset or reseed the IDENTITY column?
DBCC CHECKIDENT (jobs, RESEED, 100)

How do you persist objects, permissions in tempdb?
since tempdb is recreated every time SQLServer starts.
Create a stored procedure that creates the required objects in tempdb. Mark this stored procedure as a startup stored

procedure, so that it runs everytime SQL Server service starts. See sp_procoption in SQL Server Books Online.

Add the required objects to the model database. Since the model database is used as a template for creating new databases,

all new databases will inherit the objects from model database.

How do you simulate a deadlock for testing purposes?

In Query Analyzer, run the following statements first:

CREATE TABLE t1 (i int)
CREATE TABLE t2 (i int)

INSERT t1 SELECT 1
INSERT t2 SELECT 9

Open a new window (say Window1) in Query Analyzer, paste the following SQL statements:

BEGIN TRAN
UPDATE t1 SET i = 11 WHERE i = 1
WAITFOR DELAY 00:00:202
UPDATE t2 SET i = 99 WHERE i = 9
COMMIT

Open another window (say Window2) in Query Analyzer and paste the following code:

BEGIN TRAN
UPDATE t2 SET i = 99 WHERE i = 9
WAITFOR DELAY 00:00:202
UPDATE t1 SET i = 11 WHERE i = 1
COMMIT

Now run the code from Window1, followed by Window2 simultaneously. Briefly after 20 seconds, one of the windows will
experience a dead lock!

How do you rename an SQL Server computer?

If you are running SQL Server 7.0, after renaming the SQL Server machine, the SQL Server service will fail to start, with an

error message Your installation was corrupted or had been tampered with. To get around this problem, you have to rerun the

SQL Server setup. Setup will prompt you to upgrade. After doing so, the necessary SQL Server registry entries will be reset

with the new computer name. Now you will be able to start SQL Server. After restarting, use Query Analyzer to run the

following commands:

EXEC sp_dropserver Your_OLD_Computer_Name
GO

EXEC sp_addserver Your_NEW_Computer_Name, local
GO

Restart your SQL Server service. Connect using Query Analyzer and run the following command (It should output the new server

name):

SELECT @@SERVERNAME
GO

If you are running SQL Server 2000, the new name is recognized, the next time SQL Server service starts. You dont have to

rerun the setup. However, you have to run the sp_dropserver and sp_addserver stored procedure as shown above.

How do you run jobs from T-SQL?
Use the procedure msdb..sp_start_job to start jobs programmatically.

How do you restore single tables from backup in SQL Server 7.0/2000? In SQL Server 6.5?
Support for restoring individual tables from backup is discontinued in SQL Server 7.0/2000. If you need this functionality,

here are some roundabout ways:

Restore the complete database onto a new database with a different name. Copy the required tables (using T-SQL or DTS) into

the actual database and drop the new database that you just created

Where to get the latest MDAC from?
MDAC (Microsoft Data Access Components) can be downloaded from the Microsoft Universal Data Access site.

I forgot/lost the sa password. What do I do?
Login to the SQL Server computer as the Administrator of that computer. Open Query Analyzer and connect to SQL Server using

Windows NT authentication. Run sp_password as show below to reset the sa password:

sp_password @new = will_never_forget_again, @loginame = sa

I have only the .mdf file backup and no SQL Server database backups. Can I get my database back into SQL Server?
Yes. The system stored procedures sp_attach_db and sp_attach_single_file_db allow you to attach .mdf files to SQL Server. In

the absence of the log file (.ldf), SQL Server creates a new log file.

How do you add a new column at a specific position (say at the beginning of the table or after the second column) using ALTER

TABLE command?
ALTER TABLE always adds new columns at the end of the table and will not let you add new columns at a specific position. If

you must add a column at a specific position, use Enterprise Manager. In Enterprise Manager, right click on the table, select

Design Table. Right click on the desired location and select Insert Column. Mind you, Enterprise Manager drops and

recreates the table to add a column at a specific location. So it might take a long time if your table is huge.

How do you change or alter a user defined data type?
there is no easy way to alter or modify a user defined data type. To modify a user defined data type, follow these steps:

Alter all the tables, that are referencing this user defined data type (UDT), using ALTER TABLE&ALTER COLUMN command and

change the data type of the referencing column to an equivalent (or the intended) base data type.

Drop the user defined data type using sp_droptype.

Recreate the user defined datatype with the required changes using sp_addtype.

Again, use the ALTER TABLE&ALTER COLUMN syntax to change the columns datatype to the user defined data type.

How do you rename an SQL Server 2000 instance?
You cannot rename an instance of an SQL Server 2000. If you must rename an instance, follow these steps:

Install a new SQL Server 2000 instance with the desired name.

Move your databases from the old instance to the newly created instance.

Uninstall the old instance of SQL Server 2000.

How do you capture/redirect detailed deadlock information into the error logs?
To capture detailed deadlock information into the error logs, enable the trace flags 1204 and 3605 at the session level using

the DBCC TRACEON command. When you enable these trace flags at the session level, only those deadlocks are captured into the

error log, in which this session has participated.

To enable these trace flags at the server level, start your SQL Server from command prompt (sqlservr.exe) with -T1204 and

-T3605 parameters. You could also set these trace flags from Enterprise Manager. (Right click on the server, select

Properties. Click on Startup parameters&. Add the parameters -T1204 and -T3605 one after another by clicking the Add

button.). After setting these trace flags in Enterprise Manager, you must restart your SQL Server service for these trace

flags to take effect.

How do you remotely administer SQL Server?
You can install Enterprise Manager utility and then administer remote server just as you administer a local server. If you

need to administer SQL Server over the internet, you should specify the TCP/IP address instead of the remote SQL Servers

name under the New SQL Server Registation window.

What are the effects of switching SQL Server from Mixed mode to Windows only authentication mode? What are the steps

required, to not break existing applications
Change the connect strings in all your applications to connect using windows authentication.
If your applications are using DSNs, you will have to alter the same, to connect using Windows authentication. ?

Is there a command to list all the tables and their associated filegroups?
SELECT OBJECT_NAME(id) [Table Name], FILEGROUP_NAME(groupid) AS [Filegroup Name]
FROM sysindexes
WHERE indid IN (0, 1) AND
OBJECTPROPERTY(id, IsMSShipped) = 0

How do you ship the stored procedures, user defined functions (UDFs), triggers, views of my application, in an encrypted form

to my clients/customers? How do you protect intellectual property?
SQL Server 7.0 Enterprise Manager allowed us to script encrypted objects in encrypted form. But that feature is not available

anymore in SQL Server 2000. This means that there is no direct way out, for shipping your database objects in encrypted form.

Here are a couple of workarounds, that may or may not help depending on the situation:

Method 1: Create a template database, with encrypted objects. Backup this database. Add this database backup to your setup

application, and restore it at your cleints place, from the setup program.

Method 2: Create a template database, with encrypted objects. Detach this database. Add this detached database to your setup

application, and attach it to your clients SQL Server from your setup program.

Method 3: What if your application is already live at your clients place and you just want to ship some updates to stored

procedures? The above methods will not work, because, restore or attaching a database will overwrite the existing database,

resulting in losing all the data. Heres how you can handle this situation: Design your application to have two databases.

One database will contain all the tables and the other database will contain all the stored procedures, UDFs, triggers and

views and these objects must be coded to access tables from the other database. Now if you want to ship updated code, just

modify the stored procedures and ship the backup of the second database only. This way you are not overwriting the existing

data at your cleints end.

How do you archive data from my tables? Is there a built-in command or tool for this?
Archiving is the process of getting rid of old data (or data that is not valid anymore) from the main production databases.

There is no built-in command or tool for archiving databases. In this article Im going to show you how to implement this

process.

Why archive in the first place? By archiving old data, you can keep your databases smaller and more manageable. With smaller

tables, your index rebuild times and backup/restore times will be under control. You will also see performance gains on

certain queries that either scan a larger portion of the table or clustered index.

Depending on the business needs, one might choose to either delete the unwanted data, or copy the data to a history table and

delete that data from the production database.

Implementing an archival process is not that complicated. The three logical steps involved in this process are:

Identifying the data to be archived.

Optionally copying the identified data to a history table.

Deleting the identified data from the production database.

How do you troubleshoot ODBC timeout expired errors experienced by applications accessing SQL Server databases?
By default, ADO connections time out after 30 seconds. So, your query or stored procedure is obviously taking more than 30

seconds to complete. You can verify this by running your query/stored procedure in Query Analyzer and noting the execution

time. You can also use Profiler to see the execution time of queries (Start time, End time and Duration columns in Profiler).

Once you confirm that the query indeed is taking more than 30 seconds, find out why it is taking more than 30 seconds and see

if you can tune it to make it quicker. The most obvious step is to look at the Execution plan in Query Analyzer. Any table

scans are bad. Make sure you have right indexes on your tables, and that the query is written in an efficient way and is

using those indexes (Index seeks are preferred). If you are not sure, how to tune indexes, try the Index Tuning Wizard. Btw,

if you are using cursors, see if the query can be rewritten without using a cursor. Cursors make things slow.

Consider that indexing is fine, but still the query is slow. Few things to look at: Is your query doing clustered index scan,

and the index is fragmented? If so, you need to rebuild the index using DBCC DBREINDEX or DBCC INDEXDEFRAG (in SQL Server

2000 only). I rebuild indexes on some of my tables evrey weekend using a scheduled job.

Assume that there is no fragmentation in the index. Next thing to verify would be to see if there is any blocking going on.

Use the system stored procedures sp_who or sp_who2 (undocumented, but provides more info, compared to sp_who) to verify

blocking. If you do find that there is some blocking, you need to troubleshoot the blocking first. The following topics from

SQL Server Books Online will help you troubelshoot blocking:

- Understanding and Avoiding Blocking

- Troubleshooting Locking

Another approach for troubleshooting timeouts is to use Performance Monitor to identify the bottlenecks. A bottleneck could

be a slow disk subsystem, or slow/insufficient processing power (CPU), not enough RAM, or network. In this case, a hardware

upgrade should help.

If none of the above help, just increase the timeout value (The CommandTimeout property of Connection and/or Command object)

in your ADO application!

How do you restart SQL Server service automatically at regular intervals?
@ECHO OFF
NET STOP SQLSERVERAGENT
NET STOP MSSQLSERVER
NET START MSSQLSERVER
NET START SQLSERVERAGENT

If your requirement is to restart SQL Server every Sunday mid-night, run the following command at the command prompt of the

SQL Server computer:

AT 00:00 /EVERY:Su C:RestartSQL.BAT

This will schedule the batch file RestartSQL.BAT to run every Sunday night at 12:00 AM.

What is the T-SQL equivalent of IIF (immediate if/ternary operator) function of other programming languages?
IIf(�Logical Expression�, �Numeric Expression1�, �Numeric Expression2�)
CASE is the equivalent of IIF function

How do you programmatically find out when the SQL Server service started?
Everytime SQL Server starts, it recreates the tempdb database. So, the creation date and time of the tempdb database tells us

the date and time at which SQL Server service started. This information is stored in the crdate column of the sysdatabases

table in master database. Heres the query to find that out:

SELECT crdate AS SQL Server service started approximately at:
FROM master.dbo.sysdatabases
WHERE name = tempdb

How do you get rid of the time part from the date returned by GETDATE function?
SELECT CONVERT(char,GETDATE(),101)
SELECT CONVERT(char,GETDATE(),102)
SELECT CONVERT(char,GETDATE(),103)
SELECT CONVERT(char,GETDATE(),1)

How do you upload images or binary files into SQL Server tables?
SQL Server 7.0 and 2000 come with a utility called textcopy.exe.

How do you run an SQL script file that is located on the disk, using T-SQL?
EXEC master..xp_cmdshell osql -Svaio -Usa -Pzaassds1 -ic:MySQl.sql -n
EXEC master..xp_cmdshell isql -Svaio -Usa -Pzaassds1 -ic:MySQl.sql -n

See xp_cmdshell in SQL Server Books Online if you are having permissions problems in getting this technique to work.

How do you get the complete error message from T-SQL while error handling?
the error handling capabilities of SQL Server are limited. When an error occurs, all you can get is the error number, using

the @@ERROR global variable. There is no @@ERROR_MESSAGE global variable to get the error description.

For a complete error message, you can always query the master..sysmessages table using the error number

How do you get the first day of the week, last day of the week and last day of the month using T-SQL date functions?
SELECT DATEADD(dd,-(DATEPART(dw, getdate()) - 1),getdate()) AS First day of the week
SELECT DATEADD(dd,-(DATEPART(dw, getdate()) - 7),getdate()) AS Last day of the week
SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,getdate())),DATEADD(m,1,getdate()))) AS Last day of the month

How do you pass a table name, column name etc. to the stored procedure so that I can dynamically select from a table?
CREATE PROC DropTable
@Table sysname
AS
EXEC (DROP TABLE + @Table)
GO

Error inside a stored procedure is not being raised to my front-end applications using ADO. But I get the error when I run

the procedure from Query Analyzer.
This typically happens when your stored procedure is returning multiple resultsets and the offending SQL statement is

executed after returning one or more resultsets. ADO will not return an error untill it processes all the recordsets returned

before the offending SQL statement got executed. So, to get to the error message returned by your procedure. You have to loop

through all the recordsets returned. ADO Recordset object has a method called NextRecordset, which lets you loop through the

recordsets.

Having SET NOCOUNT ON at the beginning of the procedure also helps avoid this problem. SET NOCOUNT ON also helps in improving

the stored procedure performance. Heres a sample procedure to simulate the problem:

CREATE PROC TestProc
AS
SELECT MAX(Col1) FROM TestTable
SELECT MIN(Col1) FROM TestTable
INSERT INTO TestTable (Col1, Col2) VALUES (1,Oracle and SQL Server comparison)
INSERT INTO TestTable (Col1, Col2) VALUES (1,How to configure SQL Server?) Dupplicate key error occurs
GO

How do you suppress error messages in stored procedures/triggers etc. using T-SQL?
Its not possible to suppress error messages from within T-SQL. Error messages are always returned to the client. If you

dont want your users to see these raw error messages, you should handle them in your front-end applications. For example,

if you are using ADO from ASP to connect to SQL Server, you would do something like the following:

On Error Resume Next
Set Rs = Conn.Execute (INSERT INTO MyTable (1,How to migrate from Oracle to SQL Server,'Book)
If Err.Number 0 Then Response.Write (Error occurred while inserting new data)
On Error GoTo 0

How do you save the output of a query/stored procedure to a text file?
T-SQL by itself has no support for saving the output of queries/stored procedures to text files. But you could achieve this

using the command line utilities like isql.exe and osql.exe. You could either invoke these exe files directly from command

prompt/batch files or from T-SQL using the xp_cmdshell command. Here are the examples:

From command prompt:
osql.exe -S YourServerName -U sa -P secretcode -Q EXEC sp_who23 -o E:output.txt

From T-SQL:
EXEC master..xp_cmdshell osql.exe -S YourServerName -U sa -P secretcode -Q EXEC sp_who23 -o E:output.txt

Query Analyzer lets you save the query output to text files manually. The output of stored procedures that are run as a part

of a scheduled job, can also be saved to a text file.

BCP and Data Transformation Services (DTS) let you export table data to text files.

How do you join tables from different databases?
SELECT a.i, a.j
FROM pubs.dbo.Table1 a
INNER JOIN
northwind.dbo.Table2 b
ON a.i = b.i
GO

How do you join tables from different servers?
To be able to join tables between two SQL Servers, first you have to link them. After the linked servers are setup, you just

have to prefix your tables names with server name, database name, table owner name in your SELECT queries. The following

example links SERVER_01 to SERVER_02. Execute the following commands in SERVER_02:

EXEC sp_addlinkedserver SERVER_01
GO

/* The following command links sa login on SERVER_02 with the sa login of SERVER_01 */
EXEC sp_addlinkedsrvlogin @rmtsrvname = SERVER_012, @useself = false, @locallogin = sa, @rmtuser = sa, @rmtpassword =

sa password of SERVER_012
GO

SELECT a.title_id
FROM SERVER_01.pubs.dbo.titles a
INNER JOIN SERVER_02.pubs.dbo.titles b
ON a.title_id = b.title_id
GO

How to convert timestamp data to date data (datetime datatype)?

The name timestamp is a little misleading. Timestamp data has nothing to do with dates and times and can not be converted to

date data. A timestamp is a unique number within the database and is equivalent to a binary(8)/varbinary(8) datatype. A table

can have only one timestamp column. Timestamp value of a row changes with every update of the row. To avoid the confusion,

SQL Server 2000 introduced a synonym to timestamp, called rowversion

Can I invoke/instantiate COM objects from within stored procedures or triggers using T-SQL?
Yes. SQL Server provides system stored procedures that let you instantiate COM objects using T-SQL from stored procedures,

triggers and SQL batches. Search SQL Server Books Online for sp_OACreate and sp_OA* for documentation and examples.

Oracle has a rownum to access rows of a table using row number or row id. Is there any equivalent for that in SQL Server? Or

How do you generate output with row number in SQL Server?
There is no direct equivalent to Oracles rownum or row id in SQL Server. Strictly speaking, in a relational database, rows

within a table are not ordered and a row id wont really make sense. But if you need that functionality, consider the

following three alternatives:

Add an IDENTITY column to your table. See Books Online for more information

Use the following query to generate a row number for each row. The following query generates a row number for each row in the

authors table of pubs database. For this query to work, the table must have a unique key.

SELECT (SELECT COUNT(i.au_id)
FROM pubs..authors i
WHERE i.au_id >= o.au_id ) AS RowID,
au_fname + + au_lname AS Author name
FROM pubs..authors o
ORDER BY RowID

How do you specify a network library like TCP/IP using ADO connect string?
To specify TCP/IP net library, append the following to your ADO connect string:

Network=dbmssocn

Is there a way to find out when a stored procedure was last updated?

How do you find out all the IDENTITY columns of all the tables in a given database?
SELECT [ + TABLE_SCHEMA + ].[ + TABLE_NAME + ] AS [Table Name], COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + . + QUOTENAME(TABLE_NAME)), COLUMN_NAME, IsIdentity) = 1
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + . + QUOTENAME(TABLE_NAME)), IsMSShipped) = 0

How do you search the code of stored procedures?
Create a stored procedure that searches the text column of syscomments table. Syscomments table stores the text of stored procedures, triggers, views, user defined functions etc. I created a stored procedure that does just this. Create this stored procedure in the master database and call it from the database of your choice, just as would call a local stored procedure. You will find this procedure a little slow if you have a lot of stored procedures with bulky code, as it performs a table scan on syscomments table. You will not be able to search on keywords that are split across rows in syscomments table.

How do you retrieve the generated GUID value of a newly inserted row? Is there an @@GUID, just like @@IDENTITY?
As you know, T-SQL offers the NEWID() function to generate GUID values. To be able to retrieve GUID value after inserting a row, do not assign NEWID() as a default to your key column. Instead, use a stored procedure to insert new rows. Inside this stored procedure, generate a GUID value before hand, save it in a variable for later use, and then insert the new row with the generated GUID. Here is an example table schema and a sample stored procedure along with VB code that uses ADO to call this stored procedure:

How do you update table A from table B,by taking all the updated records from table B which are 1 hour older ,i think we need to use time stamp for this,but dont exactly know how to do it

Que: How i can find total tables and no of count records for each table in a single database i am using SQLserver plz help me

Ans: select o.name, i.rows,i.indid from sysindexes i, sysobjects o
where
i.indid

Select top 10 from emp order by sal

Select Top 1 * From
(SELECT TOP 10 * FROM employee ORDER BY job_lvl desc) tmp
Order by job_lvl asc

Select * from Emp A where 10=(select count(distinct salary) from Emp B where B.salary=A.Salary)

To get Retreive the Column Name as well as the Value Names in the Query
Ans: select columfild as colunName from Table
ex;
select EmployeeID as sona from Employees

Question : -

How do you join tables from different databases?

Answer :-

Here is the generalised SQL command, you can change the dbname and tablename to whichever you want

select * from
[dbname].dbo.[tablename] t1,
[dbname].dbo.[tablename] t2
where

Question:-

How do you programmatically find out when the SQL Server service started?

Answer:-

There is one simple but awkward method is there to find out when the SQL server service is started.

The following query will give you the time when the SQL server service is started;

select crdate from [master].dbo.sysdatabases
where name=tempdb

This is because, the tempdb will be created whenever SQL server service is started or restarted.