What is trigger?
Triggers allows us to execute a batch of SQL code when
either an insert, update or delete command is executed against a specific
table.
Triggers are special types of stored procedures that are
defined to execute automatically in place of or after data modifications. They
can be executed automatically on the insert, delete and update operation.
There are four types of triggers.
1. Insert
2. Delete
3. Update
4. Instead of
What is Clustered
& Non-Clustered Index?
Clustered Index :
A Clustered index determines the physical order of data in a table and is particularly
efficient on columns that are often searched for ranges of values.
The leaf nodes of a clustered index contain the data pages.
There can be only one clustered index per table.
"primary key" is the ideal column for a clustered index
Clustered indexes are good for range searches.
Nonclustered Index :
Nonclustered index is a special type of index in which the logical order of the
index does not match the physical stored order of the rows on disk.
The leaf node of a nonclustered index does not consist of the data pages.
Instead, the leaf nodes contain index rows.
There can be multiple non-clustered indexes per table.
"Unique Key" is the ideal column for a nonclustered index
Non-clustered indexes are good for random searches.
What is constraints?
SQL Server users constraints to enforce limitations on the
data that can be entered into a particular column in table. There are following
types of constraints.
Unique, Default, Check, Primary Key, Foreign Key, Not Null.
What is the
difference between Truncate and Delete?
Delete statement removes rows of a table one by one &
delete triggers on that table fires. But Truncate removes all rows by
deallocating the data pages assigned to the table & only these deallocation
are recorded in the transaction log.
What Primary key and
Unique key?
Primary key are used with Foreign key to enforce referential
integrity. Unique constraints allows nulls to be inserted into the field. But
there can't be null in Primary key.
How to join two
tables in Sql Server?
you can write following sql statement
select category.*, categoryparent.categoryparent from
category, categoryparent where category.categoryparentid =
categoryparent.autoid
I am assuming here that category.categoryparentid (foreign
key) is the value of categoryparent.autoid (primary key).
How do you implement
one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-One relationship can be implemented as a single table
and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the
data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction
table with the keys from both the tables forming the composite primary key of
the junction table.
What's the difference
between a primary key and a unique key?
Both primary key and unique 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.
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.
In TRUNCATE we can not rollback.In DELETE we can rollback. Trigger is
not fired in Truncate where as trigger is fired in Delete
What's the maximum
size of a row?
8060 bytes. Don't be surprised with questions like 'what is
the maximum number of columns per table'. Check out SQL Server books online for
the page titled: "Maximum Capacity Specifications".
Difference Between
Implict Transaction And Explict Transaction
Implicit Transaction is the auto commit. There is no
beginning or ending of the transaction.
Explicit Transaction has the beginning, ending and rollback
of transactions with the command
Begin Transaction
Commit Transaction and
Rollback Transation
In the explicit transaction, if an error occurs in between
we can rollback to the begining of the transaction which cannot be done in
implicit transaction.
what is the diff
between a HAVING CLAUSE and a WHERE CLAUSE?
You can use Having Clause with the GROUP BY function in
query and WHERE Clause is applied to each row before they are part of the GROUP
BY function in a query.
How to change
Database name in SQL Server?
Use following code
Supported in SQL Server 2000 and 2005
exec sp_renamedb "test", "test1"
Supported in SQL Server 2005 and later version
ALTER Database "test1" Modify
Name="test"
How to Get nth Record
in a Table?
First Get the n records fron the table using
Select Top n FROM UserTable
Now Reverse the Order using identity Column like:
Select Top n FROM UserTable Order By 1 DESC
Now we need nth record that can be get as
SELECT TOP 1 * FROM (Select Top n FROM UserTable Order By 1
DESC)AC
For Example i need to get 5th record From userTable then
quey will be like this:
SELECT TOP 1 * FROM (SELECT TOP 5 * FROM UserTable Order By
1 DESC)AC
Difference between
Primary key Constraint and Unique key Constraint in SQL Server.
Unique Key Constraint:
The column values should retain uniqueness.
It allows null values in the column.
It will create non-clustered index by default.
Any number of unique constraints can be added to a table.
Primary Key Constraint:
Primary key will create column data uniqueness in the table.
It Wont allow Null values.
By default Primary key will create clustered index.
Only one Primary key can be created for a table.
Multiple columns can be consolidated to form a single primary key.
What is cursor in SQL
Server?
A cursor is a set of rows together with a pointer that
identifies a current row.
In other word, Cursor is a database object used by applications to manipulate
data in a set on a row-by-row basis, its like recordset in the ASP and visual
basic.
Typical syntax of cursor is
DECLARE @fName varchar(50), @lName varchar(50)
DECLARE cursorName CURSOR -- Declare cursor
LOCAL SCROLL STATIC
FOR
Select firstName, lastName FROM myTable
OPEN cursorName -- open the cursor
FETCH NEXT FROM cursorName
INTO @fName, @lName
PRINT @fName + ' ' + @lName -- print
the name
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM cursorName
INTO @fName, @lName
PRINT @fName + ' ' + @lName -- print
the name
END
CLOSE cursorName -- close the cursor
DEALLOCATE cursorName -- Deallocate the cursor
What is #temp table
and @table variable in SQL Server?
#temp Table (Temporary Table)
temp table is a temporary table that is generally created to store session
specific data. Its kind of normal table but it is created and populated on
disk, in the system database tempdb — with a session-specific identifier packed
onto the name, to differentiate between similarly-named #temp tables created
from other sessions.
The data in this #temp table (in fact, the table itself) is visible only to the
current scope. Generally, the table gets cleared up automatically when the
current procedure goes out of scope, however, we should manually clean up the
data when we are done with it.
Syntax:
-- create temporary table
CREATE TABLE #myTempTable (
AutoID int,
MyName char(50) )
-- populate temporary table
INSERT INTO #myTempTable (AutoID, MyName )
SELECT AutoID, MyName FROM myOriginalTable
WHERE AutoID <= 50000
-- Drop temporary table
drop table #myTempTable
@table variable
table variable is similar to temporary table except with more flexibility. It
is not physically stored in the hard disk, it is stored in the memory. We
should choose this when we need to store less 100 records.
Syntax:
DECLARE @myTable TABLE (
AutoID int,
myName char(50) )
INSERT INTO @myTable (AutoID, myName )
SELECT YakID, YakName FROM myTable WHERE AutoID <= 50
We don't need to drop the @temp variable as this is created inside the memory
and automatically disposed when scope finishes.
What is the use of
COALESCE in SQL Server?
Coalesce returns the first non-null expression among its arguments.
Lets say we have to return a non-null from more than one column, then we can
use COALESCE function.
SELECT COALESCE(hourly_wage, salary, commission) AS 'Total Salary' FROM wages
In this case,
If hourly_wage is not null and other two columns are null then hourly_wage will
be returned.
If hourly_wage, commission are null and
salary is not null then salary will be returned.
If commission is non-null and other two columns are null then commission will
be returned.
What are the types of
triggers and how the sequence of firing in text item?
Triggers can be classified as Key Triggers, Mouse Triggers
,Navigational Triggers.
Key Triggers :: Key Triggers are fired as a result of Key
action.e.g :: Key-next-field, Key-up,Key-Down
Mouse Triggers :: Mouse Triggers are fired as a result of the mouse
navigation.e.g. When-mouse-button-presed,when-mouse-doubleclicked,etc
Navigational Triggers :: These Triggers are fired as a result of Navigation.
E.g : Post-Text-item,Pre-text-item.
We also have event triggers like when ?Vnew-form-instance and
when-new-block-instance.
We cannot call restricted procedures like go_to(??my_block.first_item??) in the
Navigational triggers
But can use them in the Key-next-item.
The Difference between Key-next and Post-Text is an very important question.
The key-next is fired as a result of the key action while the post text is
fired as a result of the mouse movement. Key next will not fire unless there is
a key event.
The sequence of firing in a text item are as follows ::
a) pre – text
b) when new item
c) key-next
d) when validate
e) post text
How do you optimize
stored procedures in SQL Server 2005
1. Use as much as possible WHERE clause filters. Where Clause is the most
important part for optimization
2. Select only those fields which really require.
3. Joins are expensive in terms of time. Make sure that use all the keys that
relate the two tables together and don't join to unused tables, always try to
join on indexed fields. The join type is important as well (INNER, OUTER).
How you can get the
last identity value inserted in any table ?
SQL Server 2000 has a System Variable @@IDENTITY which gives the last
identity element value inserted in any table
What is a Stored
Procedure?
Its nothing but a set of T-SQL statements combined to perform a single task
of several tasks. Its basically like a Macro so when you invoke the Stored
procedure, you actually run a set of statements.
what is ACID?
A transaction is a logical unit of work in which, all the steps must be
performed or none.
ACID (an acronymn for Atomicity Consistency Isolation Durability) is a
concept that Database Professionals generally look for when evaluating
databases and application architectures. For a reliable database all this four
attributes should be achieved.
Atomicity is an all-or-none proposition.
Consistency guarantees that a transaction never leaves your database in a
half-finished state.
Isolation keeps transactions separated from each other until they’re finished.
Durability guarantees that the database will keep track of pending changes in
such a way that the server can recover from an abnormal termination.
Above four rules are very important for any developers dealing with databases
Can we create a Foreign Key with out
Primary Key?
Yes. If the table has Unique Key then it is posible to create a Foreign key
constraint
Difference between
varchar and char?
varchar are variable length strings with a maximum length specified. If a
string is less than the maximum length, then it is stored verbatim without any
extra characters.
char are fixed length strings with a set length specified. If a string is less
than the set length, then it is padded with extra characters so that it's
length is the set length.
Use varchar when your strings do not have a fixed length (e.g. names, cities,
etc.)
Use char when your strings are always going to be the same length (e.g. phone
numbers, zip codes, etc).
What Kind of User-Defined Functions can I Create?
There are three types of User-Defined functions in SQL Server 2000 and they
are Scalar, Inline Table-Valued and Multi-statement Table-valued.
How do I create and use a Scalar User-Defined Function?
A Scalar user-defined function returns one of the scalar data types. Text,
ntext, image and timestamp data types are not supported. These are the type of
user-defined functions that most developers are used to in other programming
languages. You pass in 0 to many parameters and you get a return value. Below
is an example that is based in the data found in the NorthWind Customers Table.
CREATE FUNCTION whichContinent
(@Country nvarchar(15))
RETURNS varchar(30)
AS
BEGIN
declare @Return varchar(30)
select @return = case @Country
when 'Argentina' then 'South America'
when 'Belgium' then 'Europe'
when 'Brazil' then 'South America'
when 'Canada' then 'North America'
when 'Denmark' then 'Europe'
when 'Finland' then 'Europe'
when 'France' then 'Europe'
else 'Unknown'
end
return @return
end
Because this function returns a scalar value of a varchar(30) this function
could be used anywhere a varchar(30) expression is allowed such as a computed
column in a table, view, a T-SQL select list item. Below are some of the
examples that I was able to use after creating the above function definition.
Note that I had to reference the dbo in the function name.
print dbo.WhichContinent('USA')
select dbo.WhichContinent(Customers.Country), customers.* from customers
create table test
(Country varchar(15),
Continent as (dbo.WhichContinent(Country)))
insert into test (country)
values ('USA')
select * from test
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Country Continent
---------------
------------------------------
USA North America
Stored procedures have long given us the ability to pass
parameters and get a value back, but the ability to use it in such a variety of
different places where you cannot use a stored procedure make this a very
powerful database object. Also notice the logic of my function is not exactly
brain surgery. But it does encapsulate the business rules for the different continents
in one location in my application. If you were to build this logic into T-SQL
statements scattered throughout your application and you suddenly noticed that
you forgot a country (like I missed Austria!) you would have to make the change
in every T-SQL statement where you had used that logic. Now, with the SQL
Server User-Defined Function, you can quickly maintain this logic in just one
place.
How do I create and use an Inline Table-Value
User-Defined Function?
An Inline Table-Value user-defined function returns a table data type and
is an exceptional alternative to a view as the user-defined function can pass
parameters into a T-SQL select command and in essence provide us with a
parameterized, non-updateable view of the underlying tables.
CREATE FUNCTION CustomersByContinent
(@Continent varchar(30))
RETURNS TABLE
AS
RETURN
SELECT
dbo.WhichContinent(Customers.Country) as continent,
customers.* FROM customers
WHERE
dbo.WhichContinent(Customers.Country) = @Continent
GO
SELECT * from CustomersbyContinent('North America')
SELECT * from CustomersByContinent('South America')
SELECT * from customersbyContinent('Unknown')
Note that the example uses another function (WhichContinent)
to select out the customers specified by the parameter of this function. After
creating the user-defined function, I can use it in the FROM clause of a T-SQL
command unlike the behavior found when using a stored procedure which can also
return record sets. Also note that I do not have to reference the dbo in my reference
to this function. However, when using SQL Server built-in functions that return
a table, you must now add the prefix :: to the name of the function.
Example from Books Online: Select * from ::fn_helpcollations()
How do I create and use a Multi-statement Table-Value
User-Defined Function?
A Multi-Statement Table-Value user-defined function returns
a table and is also an exceptional alternative to a view as the function can
support multiple T-SQL statements to build the final result where the view is limited
to a single SELECT statement. Also, the ability to pass parameters into a T-SQL
select command or a group of them gives us the capability to in essence create
a parameterized, non-updateable view of the data in the underlying tables.
Within the create function command you must define the table structure that is
being returned. After creating this type of user-defined function, I can use it
in the FROM clause of a T-SQL command unlike the behavior found when using a
stored procedure which can also return record sets.
CREATE FUNCTION dbo.customersbycountry ( @Country
varchar(15) )
RETURNS
@CustomersbyCountryTab
table (
[CustomerID]
[nchar] (5), [CompanyName] [nvarchar] (40),
[ContactName]
[nvarchar] (30), [ContactTitle] [nvarchar] (30),
[Address]
[nvarchar] (60), [City] [nvarchar] (15),
[PostalCode]
[nvarchar] (10), [Country] [nvarchar] (15),
[Phone]
[nvarchar] (24), [Fax] [nvarchar] (24)
)
AS
BEGIN
INSERT INTO
@CustomersByCountryTab
SELECT [CustomerID], [CompanyName],
[ContactName],
[ContactTitle],
[Address],
[City],
[PostalCode],
[Country],
[Phone],
[Fax]
FROM
[Northwind].[dbo].[Customers] WHERE country = @Country
DECLARE @cnt INT
SELECT @cnt = COUNT(*)
FROM @customersbyCountryTab
IF @cnt = 0
INSERT
INTO @CustomersByCountryTab (
[CustomerID],[CompanyName],[ContactName],
[ContactTitle],[Address],[City],[PostalCode],[Country],
[Phone],[Fax] )
VALUES
('','No Companies Found','','','','','','','','')
RETURN
END
GO
SELECT * FROM dbo.customersbycountry('USA')
SELECT * FROM dbo.customersbycountry('CANADA')
SELECT * FROM dbo.customersbycountry('ADF')
What are the benefits of User-Defined Functions?
The benefits to SQL Server User-Defined functions are
numerous. First, we can use these functions in so many different places when
compared to the SQL Server stored procedure. The ability for a function to act
like a table (for Inline table and Multi-statement table functions) gives
developers the ability to break out complex logic into shorter and shorter code
blocks. This will generally give the additional benefit of making the code less
complex and easier to write and maintain. In the case of a Scalar User-Defined
Function, the ability to use this function anywhere you can use a scalar of the
same data type is also a very powerful thing. Combining these advantages with
the ability to pass parameters into these database objects makes the SQL Server
User-Defined function a very powerful tool.
What is Subquery in SQL Server?
A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE
statement, or inside another subquery. Subquery is an inner query or inner
select, while the statement containing a subquery is also called an outer query
or outer select.
For example
SELECT CustName, CustOrderDate, (SELECT DateTimeOrdered FROM OrderDetails as
ODetails WHERE Ord.SalesOrderID = ODetails.SalesOrderID) AS OrderedDateTime
FROM Orders AS Ord
A subquery is subject to the following restrictions:
1. The select list of a subquery introduced with a comparison operator can
include only one expression or column name (except that EXISTS and IN operate
on SELECT * or a list, respectively).
2. If the WHERE clause of an outer query includes a column name, it must be
join-compatible with the column in the subquery select list.
3. The ntext, text, and image data types cannot be used in the select list of
subqueries.
4. Because they must return a single value, subqueries introduced by an
unmodified comparison operator (one not followed by the keyword ANY or ALL)
cannot include GROUP BY and HAVING clauses.
5. The DISTINCT keyword cannot be used with subqueries that include GROUP BY.
6. The COMPUTE and INTO clauses cannot be specified.
7. ORDER BY can only be specified when TOP is also specified.
8. A view created by using a subquery cannot be updated.
What are the different
types of Locks
Mainly There are three types of locks in SQL Server :
(1)Shared locks are used for operations that does not allow to change or update
data, such as a SELECT statement.
(2)Update locks are used when SQL Server intends to modify a page, and later
promotes the update page lock to an exclusive page lock before actually making
the changes.
(3)Exclusive locks are used for the data modification operations, such as
UPDATE, INSERT, or DELETE.
What is the
difference between UNION ALL Statement and UNION
The main difference between UNION ALL statement and UNION is UNION All
statement is much faster than UNION.
Reason : Because UNION ALL statement does not look for duplicate rows, but on
the other hand UNION statement does look for duplicate rows, whether or not
they exist.
How to get number of
Maximum connection can be establish to SQL
select @@MAX_Connections
What is a Join in SQL Server?
Join puts data from two or more tables into a single result set.
What is the Magic
Tables in Sqlserver2000?
In Database for any table or view When a trigger is fired for any DML
command.
Then 2 tables automatically create on backend.
One table is for Insert and other one is for Delete.
These tables are called Magic Tables.
Number of records in both tables should be same.
How do we find the duplicate records of
a table?
SELECT empname, COUNT(*) AS n FROM
employee GROUP BY empname HAVING
COUNT(*)>1
How do we find the
last date of current month?
Select DateAdd(d,-day(GetDate()),GETDATE()-1) as LASTDATE
How do we get Current Months First Day?
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getDate())-1),getDate()),101)
What are DMVs?
Dynamic management views (DMVs) and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance; that is, they let you see what is going on inside SQL Server. They were introduced in SQL Server 2005 as an alternative to system tables. One example is viewing operating system wait statistics via this query:
SELECT * FROM sys.dm_os_wait_stats;
Another example is examining current sessions, much like the sp_who2 command:
SELECT * FROM sys.dm_exec_sessions;
What are temp tables? What is the difference between global and local temp tables?
Temporary tables are temporary storage structures. You may use temporary tables as buckets to store data that you will manipulate before arriving at a final format. The hash (#) character is used to declare a temporary table as it is prepended to the table name. A single hash (#) specifies a local temporary table.
CREATE TABLE #tempLocal ( nameid int, fname varchar(50), lname varchar(50) )
Local temporary tables are available to the current connection for the user, so they disappear when the user disconnects.
Global temporary tables may be created with double hashes (##). These are available to all users via all connections, and they are deleted only when all connections are closed.
CREATE TABLE ##tempGlobal ( nameid int, fname varchar(50), lname varchar(50) )
Once created, these tables are used just like permanent tables; they should be deleted when you are finished with them. Within SQL Server, temporary tables are stored in the Temporary Tables folder of the tempdb database.
How are transactions used?
Transactions allow you to group SQL commands into a single unit. The transaction begins with a certain task and ends when all tasks within it are complete. The transaction completes successfully only if all commands within it complete successfully. The whole thing fails if one command fails. The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions. A group of tasks starts with the begin statement. If any problems occur, the rollback command is executed to abort. If everything goes well, all commands are permanently executed via the commit statement.
What is the difference between a clustered and a nonclustered index?
A clustered index affects the way the rows of data in a table are stored on disk. When a clustered index is used, rows are stored in sequential order according to the index column value; for this reason, a table can contain only one clustered index, which is usually used on the primary index value.
A nonclustered index does not affect the way data is physically stored; it creates a new object for the index and stores the column(s) designated for indexing with a pointer back to the row containing the indexed values.
You can think of a clustered index as a dictionary in alphabetical order, and a nonclustered index as a book's index.
What are DBCC commands?
Basically, the Database Consistency Checker (DBCC) provides a set of commands (many of which are undocumented) to maintain databases -- maintenance, validation, and status checks. The syntax is DBCC followed by the command name. Here are three examples:
DBCC CHECKALLOC -- Check disk allocation consistency.
DBCC OPENTRAN -- Display information about recent transactions.
DBCC HELP -- Display Help for DBCC commands.
What is the difference between truncate and delete?
Truncate is a quick way to empty a table. It removes everything without logging each row. Truncate will fail if there are foreign key relationships on the table. Conversely, the delete command removes rows from a table, while logging each deletion and triggering any delete triggers that may be present.
What does the NOLOCK query hint do?
Table hints allow you to override the default behavior of the query optimizer for statements. They are specified in the FROM clause of the statement. While overriding the query optimizer is not always suggested, it can be useful when many users or processes are touching data. The NOLOCK query hint is a good example because it allows you to read data regardless of who else is working with the data; that is, it allows a dirty read of data -- you read data no matter if other users are manipulating it. A hint like NOLOCK increases concurrency with large data stores.
SELECT * FROM table_name (NOLOCK)
What is a CTE?
A common table expression (CTE) is a temporary named result set that can be used within other statements like SELECT, INSERT, UPDATE, and DELETE. It is not stored as an object and its lifetime is limited to the query. It is defined using the WITH statement as the following example shows:
WITH ExampleCTE (id, fname, lname)
AS
(
SELECT id, firstname, lastname FROM table
)
SELECT * FROM ExampleCTE
A CTE can be used in place of a view in some instances.
What is a view? What is the WITH CHECK OPTION clause for a view?
A view is a virtual table that consists of fields from one or more real tables. Views are often used to join multiple tables or to control access to the underlying tables.
The WITH CHECK OPTION for a view prevents data modifications (to the data) that do not confirm to the WHERE clause of the view definition. This allows data to be updated via the view, but only if it belongs in the view.
What is a query execution plan?
SQL Server has an optimizer that usually does a great job of optimizing code for the most effective execution. A query execution plan is the breakdown of how the optimizer will run (or ran) a query. There are several ways to view a query execution plan. This includes using the Show Execution Plan option within Query Analyzer; Display Estimated Execution Plan on the query dropdown menu; or use the SET SHOWPLAN_TEXT ON command before running a query and capturing the execution plan event in a SQL Server Profiler trace.
What does the SQL Server Agent Windows service do?
SQL Server Agent is a Windows service that handles scheduled tasks within the SQL Server environment (aka jobs). The jobs are stored/defined within SQL Server, and they contain one or more steps that define what happens when the job runs. These jobs may run on demand, as well as via a trigger or predefined schedule. This service is very important when determining why a certain job did not run as planned -- often it is as simple as the SQL Server Agent service not running.
What is the default port number for SQL Server?
If enabled, the default instance of Microsoft SQL Server listens on TCP port 1433. Named instances are configured for dynamic ports, so an available port is chosen when SQL Server starts. When connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall.
The list of possible questions is endless. I am sure these questions will spawn debate and discussion.
