SQL Server FAQ on Random Category
87. What are the constraints for Table Constraints define rules
regarding the values allowed in columns and are the standard mechanism
for enforcing integrity. SQL Server 2000 supports five classes of
constraints.
NOT NULL
CHECK
UNIQUE
PRIMARY KEY
FOREIGN KEY
88. There are 50 columns in a table. Write a query to get first 25 columns
Ans: Need to mention each column names.
89. How to list all the tables in a particular database?
USE pubs
GO
sp_help
90. What are cursors? Explain different types of cursors. What are the
disadvantages of cursors? How can you avoid cursors?
Cursors allow row-by-row processing of the result sets.
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 roundtrip, however large the result set is. Cursors are
also costly because they require more resources and temporary storage
(results in more IO operations). Further, 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.
91. Dynamic Cursors?
Suppose, I have a dynamic cursor attached to table in a database. I
have another means by which I will modify the table. What do you
think will the values in the cursor be?
Dynamic cursors reflect all changes made to the rows in their result
set when scrolling through the cursor. The data values, order, and
membership of the rows in the result set can change on each fetch. All
UPDATE, INSERT, and DELETE statements made by all users are visible
through the cursor. Updates are visible immediately if they are made
through the cursor using either an API function such as SQLSetPos or
the Transact-SQL WHERE CURRENT OF clause. Updates made outside the
cursor are not visible until they are committed, unless the cursor
transaction isolation level is set to read uncommitted.
92. What is DATEPART?
Returns an integer representing the specified datepart of the
specified date.
93. Difference between Delete and Truncate?
TRUNCATE TABLE is functionally identical to DELETE statement with no
WHERE clause: both remove all rows in the table.
(1) But TRUNCATE TABLE is faster and uses fewer system and transaction
log resources than DELETE. The DELETE statement removes rows one at a
time and records an entry in the transaction log for each deleted row.
TRUNCATE TABLE removes the data by deallocating the data pages used to
store the table's data, and only the page deallocations are recorded
in the transaction log.
(2) Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
(3) The counter used by an identity for new rows is reset to the seed
for the column. If you want to retain the identity counter, use DELETE
instead.
Of course, TRUNCATE TABLE can be rolled back.
94. Given a scenario where two operations, Delete Stmt and Truncate
Stmt, where the Delete Statement was successful and the truncate stmt
was failed. – Can u judge why?
95. What are global variables? Tell me some of them?
Transact-SQL global variables are a form of function and are now
referred to as functions.
ABS - Returns the absolute, positive value of the given numeric
expression.
SUM
AVG
AND
96. What is DDL?
Data definition language (DDL) statements are SQL statements that
support the definition or declaration of database objects (for
example, CREATE TABLE, DROP TABLE, and ALTER TABLE).
You can use the ADO Command object to issue DDL statements. To
differentiate DDL statements from a table or stored procedure name,
set the CommandType property of the Command object to adCmdText.
Because executing DDL queries with this method does not generate any
recordsets, there is no need for a Recordset object.
97. What is DML?
Data Manipulation Language (DML), which is used to select, insert,
update, and delete data in the objects defined using DDL
98. What are keys in RDBMS? What is a primary key/ foreign key?
There are two kinds of keys.
A primary key is a set of columns from a table that are guaranteed to
have unique values for each row of that table.
Foreign keys are attributes of one table that have matching values in
a primary key in another table, allowing for relationships between
tables.
99. What is the difference between Primary Key and Unique Key?
Both primary key and unique key enforce uniqueness of the column on
which they are defined. But by default primary key creates a clustered
index on the column, where are unique creates a nonclustered index by
default. Another major difference is that, primary key doesn't allow
NULLs, but unique key allows one NULL only.
100. 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.
A key formed by combining at least two or more columns is called
composite key.
101. What is the Referential Integrity?
Referential integrity refers to the consistency that must be
maintained between primary and foreign keys, i.e. every foreign key
value must have a corresponding primary key value.
102. What are defaults? Is there a column to which a default can't be
bound?
A default is a value that will be used by a column, if no value is
supplied to that column while inserting data. IDENTITY columns and
timestamp columns can't have defaults bound to them.
103. What is Query optimization? How is tuning a performance of query
done?
104. What is the use of trace utility?
105. What is the use of shell commands? xp_cmdshell
Executes a given command string as an operating-system command shell
and returns any output as rows of text. Grants nonadministrative users
permissions to execute xp_cmdshell.
106. What is use of shrink database?
Microsoft® SQL Server 2000 allows each file within a database to be
shrunk to remove unused pages. Both data and transaction log files can
be shrunk.
107. If the performance of the query suddenly decreased where you will
check?
108. What is execution plan?
109. What is a pass-through query?
Microsoft® SQL Server 2000 sends pass-through queries as
un-interpreted query strings to an OLE DB data source. The query must
be in a syntax the OLE DB data source will accept. A Transact-SQL
statement uses the results from a pass-through query as though it is a
regular table reference.
This example uses a pass-through query to retrieve a result set from a
Microsoft Access version of the Northwind sample database.
SELECT *
FROM OpenRowset('Microsoft.Jet.OLEDB.4.0',
'c:\northwind.mdb';'admin'; '',
'SELECT CustomerID, CompanyName
FROM Customers
WHERE Region = ''WA'' ')
110. How do you differentiate Local and Global Temporary table?
You can create local and global temporary tables. Local temporary
tables are visible only in the current session; global temporary
tables are visible to all sessions. Prefix local temporary table names
with single number sign (#table_name), and prefix global temporary
table names with a double number sign (##table_name). SQL statements
reference the temporary table using the value specified for table_name
in the CREATE TABLE statement:
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)
111. How the Exists keyword works in SQL Server?
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE exists
(SELECT *
FROM publishers
WHERE
authors.city = publishers.city)
When a subquery is introduced with the keyword EXISTS, it functions as
an existence test. The WHERE clause of the outer query tests for the
existence of rows returned by the subquery. The subquery does not
actually produce any data; it returns a value of TRUE or FALSE.
112. ANY?
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE city = ANY
(SELECT city
FROM publishers)
113. to select date part only
SELECT CONVERT(char(10),GetDate(),101)
--to select time part only
SELECT right(GetDate(),7)
114. How can I send a message to user from the SQL Server?
You can use the xp_cmdshell extended stored procedure to run net send
command. This is the example to send the 'Hello' message to JOHN:
EXEC master..xp_cmdshell "net send JOHN 'Hello'"
To get net send message on the Windows 9x machines, you should run the
WinPopup utility. You can place WinPopup in the Startup group under
Program Files.
115. What is normalization? Explain different levels of normalization?
Explain Third normalization form with an example?
The process of refining tables, keys, columns, and relationships to
create an efficient database is called normalization. This should
eliminates unnecessary duplication and provides a rapid search path to
all necessary information.
Some of the benefits of normalization are:
• Data integrity (because there is no redundant, neglected data)
• Optimized queries (because normalized tables produce rapid,
efficient joins)
• Faster index creation and sorting (because the tables have fewer
columns)
• Faster UPDATE performance (because there are fewer indexes per table)
• Improved concurrency resolution (because table locks will affect
less data)
• Eliminate redundancy
There are a few rules for database normalization. Each rule is called
a "normal form." If the first rule is observed, the database is said
to be in "first normal form." If the first three rules are observed,
the database is considered to be in "third normal form." Although
other levels of normalization are possible, third normal form is
considered the highest level necessary for most applications.
First Normal Form (1NF)
• Eliminate repeating groups in individual tables
• Create a separate table for each set of related data.
• Identify each set of related data with a primary key.
Do not use multiple fields in a single table to store similar data.
For example, to track an inventory item that may come from two
possible sources, an inventory record may contain fields for Vendor
Code 1 and Vendor Code 2. But what happens when you add a third
vendor? Adding a field is not the answer; it requires program and
table modifications and does not smoothly accommodate a dynamic number
of vendors. Instead, place all vendor information in a separate table
called Vendors, then link inventory to vendors with an item number
key, or vendors to inventory with a vendor code key.
Another Example
Subordinate1 Subordinate2 Subordinate3 Subordinate4
Bob Jim Mary Beth
Mary Mike Jason Carol Mark
Jim Alan
Eliminate duplicative columns from the same table. Clearly, the
Subordinate1-Subordinate4 columns are duplicative. What happens when
we need to add or remove a subordinate?
Subordinate
Bob Jim
Bob Mary
Bob Beth
Mary Mike
Mary Jason
Mary Carol
Mary Mark
Jim Alan
Second Normal Form (2NF)
• Create separate tables for sets of values that apply to multiple
records.
• Relate these tables with a foreign key.
Records should not depend on anything other than a table's primary key
(a compound key, if necessary).
For example, consider a customer's address in an accounting system.
The address is needed by the Customers table, but also by the Orders,
Shipping, Invoices, Accounts Receivable, and Collections tables.
Instead of storing the customer's address as a separate entry in each
of these tables, store it in one place, either in the Customers table
or in a separate Addresses table.
Another Example:
CustNum FirstName LastName Address City State ZIP
1 John Doe 12 Main Street Sea Cliff NY 11579
2 Alan Johnson 82 Evergreen Tr Sea Cliff NY 11579
A brief look at this table reveals a small amount of redundant data.
We're storing the "Sea
twice each. Additionally, if the ZIP code for Sea Cliff were to
change, we'd need to make that change in many places throughout the
database. Our new table (let's call it ZIPs) might look like this:
33157
Third Normal Form (3NF)
• Eliminate fields that do not depend on the key.
Values in a record that are not part of that record's key do not
belong in the table. In general, any time the contents of a group of
fields may apply to more than a single record in the table, consider
placing those fields in a separate table.
For example, in an Employee Recruitment table, a candidate's
university name and address may be included. But you need a complete
list of universities for group mailings. If university information is
stored in the Candidates table, there is no way to list universities
with no current candidates. Create a separate Universities table and
link it to the Candidates table with a university code key.
Another Example :
Order Number Customer Number Unit Price Quantity Total
1 241 $10 2 $20
2 842 $9 20 $180
The total can be derived by multiplying the unit price by the
quantity, therefore it's not fully dependent upon the primary key. We
must remove it from the table to comply with the third normal form:
Order Number Customer Number Unit Price Quantity
1 241 $10 2
2 842 $9 20
http://databases.about.com/library/weekly/aa091601a.htm
Domain/key normal form (DKNF). A key uniquely identifies each row in a
table. A domain is the set of permissible values for an attribute. By
enforcing key and domain restrictions, the database is assured of
being freed from modification anomalies. DKNF is the normalization
level that most designers aim to achieve.
**
Remember, these normalization guidelines are cumulative. For a
database to be in 2NF, it must first fulfill all the criteria of a 1NF
database.
116. If a database is normalized by 3 NF then how many number of
tables it should contain in minimum? How many minimum if 2NF and 1 NF?
117. What is denormalization and when would you go for it?
As the name indicates, denormalization is the reverse process of
normalization. It's the controlled introduction of redundancy in to
the database design. It helps improve the query performance as the
number of joins could be reduced.
118. How can I randomly sort query results?
To randomly order rows, or to return x number of randomly chosen rows,
you can use the
will get same value. You can use an ORDER BY clause to sort the rows
by the result from the NEWID function, as the following code shows:
SELECT *
FROM Northwind..Orders
ORDER BY NEWID()
119. sp_who
Provides information about current Microsoft® SQL Server™ users and
processes. The information returned can be filtered to return only
those processes that are not idle.
120. Have you worked on Dynamic SQL? How will You handled " (Double
Quotes) in Dynamic SQL?
121. How to find dependents of a table?
Verify dependencies with sp_depends before dropping an object
122. What is the difference between a CONSTRAINT AND RULE?
Rules are a backward-compatibility feature that perform some of the
same functions as CHECK constraints. CHECK constraints are the
preferred, standard way to restrict the values in a column. CHECK
constraints are also more concise than rules; there can only be one
rule applied to a column, but multiple CHECK constraints can be
applied. CHECK constraints are specified as part of the CREATE TABLE
statement, while rules are created as separate objects and then bound
to the column.
123. How to call a COM dll from SQL Server 2000?
sp_OACreate - Creates an instance of the OLE object on an instance of
Microsoft® SQL Server
Syntax
sp_OACreate progid, clsid,
objecttoken OUTPUT
[ , context ]
context - Specifies the execution context in which the newly created
OLE object runs. If specified, this value must be one of the following:
1 = In-process (.dll) OLE server only
4 = Local (.exe) OLE server only
5 = Both in-process and local OLE server allowed
Examples
A. Use Prog ID - This example creates a SQL-DMO SQLServer object by
using its ProgID.
DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
B. Use CLSID - This example creates a SQL-DMO SQLServer object by
using its CLSID.
DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
EXEC @hr = sp_OACreate '{00026BA1-0000-0000-C000-000000000046}',
@object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
124. Difference between sysusers and syslogins?
sysusers - Contains one row for each Microsoft® Windows user, Windows
group, Microsoft SQL Server™ user, or SQL Server role in the database.
syslogins - Contains one row for each login account.
125. What is the row size in SQL Server 2000?
8060 bytes.
126. How will you find structure of table, all tables/views in one db,
all dbs?
sp_helpdb - will give list of all databases
sp_helpdb pubs - will give details about database pubs. .mdf, .ldf
file locations, size of database.
select * from information_schema.tables where table_type='base table'
OR
SELECT * FROM sysobjects WHERE type = 'U' - lists all tables under
current database
***
127. What is English query?
128. B-tree indexes or doubly-linked lists?
129. 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().
130. What are the series of steps that happen on execution of a query
in a Query Analyzer?
1) Syntax checking 2) Parsing 3) Execution plan
131. Which event (Check constraints, Foreign Key, Rule, trigger,
Primary key check) will be performed last for integrity check?
Identity Insert Check
Nullability constraint
Data type check
Instead of trigger
Primary key
Check constraint
Foreign key
DML Execution (update statements)
After Trigger
**
132. How will you show many to many relation in sql?
Create 3rd table with 2 columns which having one to many relation to
these tables.
For More SQL SERVER Frequently Asked Interview Questions
No comments:
Post a Comment