Search for notes by fellow students, in your own course and all over the country.
Browse our notes for titles which look like what you need, you can preview any of the notes via a sample of the contents. After you're happy these are the notes you're after simply pop them into your shopping cart.
Title: sql server interview question
Description: it is given sql server interview question.these questions mostly asked by interviewer.
Description: it is given sql server interview question.these questions mostly asked by interviewer.
Document Preview
Extracts from the notes are below, to see the PDF you'll receive please use the links above
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
...
They
can be executed automatically on the insert, delete and update operation
...
It is nothing but a dictionary type data
where actual data remains
...
It is a kind of index backside of the
book where you see only the reference of a kind of data
...
1
...
Delete
3
...
Instead of\
What is constraints?
SQL Server users constraints to enforce limitations on the data that can be entered into a particular column in table
...
Unique, Default, Check, Primary Key, Foreign Key, Not Null
...
But Truncate removes all rows by deallocating the
data pages assigned to the table & only these deallocation are recorded in the transaction log
...
Unique constraints allows nulls to be inserted into the field
...
How to join two tables in Sql Server?
you can write following sql statement
select category
...
categoryparent from category, categoryparent where category
...
autoid
I am assuming here that category
...
autoid (primary key)
...
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships
...
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
...
Another major difference is that, primary key doesn't allow
NULLs, but unique key allows one NULL only
...
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
...
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
...
These are the
properties of a transaction
...
What's the maximum size of a row?
8060 bytes
...
Check out SQL Server books online for the page titled:
"Maximum Capacity Specifications"
...
There is no beginning or ending of the 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
...
Unique Key Constraint:
The column values should retain uniqueness
...
It will create non-clustered index by default
...
Primary Key Constraint:
Primary key will create column data uniqueness in the table
...
By default Primary key will create clustered index
...
Multiple columns can be consolidated to form a single primary key
...
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
...
mssqlcity
...
htm
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
...
The data in this #temp table (in fact, the table itself) is visible only to the current scope
...
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
...
We should choose this
when we need to store less 100 records
...
How to return XML in SQL Server?
We can use FOR XML statement at the end of the query to return xml data from the SQL Server
...
sqljunkies
...
com/Article/296D1B56-8BDD-4236-808F-E62CC1908C4E
...
OpenXML primarily gives the ability to insert XML data to the relational database,
however we can query the data too using OpenXML
...
Syntax:
DECLARE @index int
DECLARE @xmlString varchar(8000)
SET @xmlString ='
EXEC sp_xml_preparedocument @index OUTPUT, @xmlString
SELECT *
FROM OPENXML (@index, 'Persons/Person')
WITH (id Varchar(10), Name varchar(100) 'Name' , PhoneNo Varchar(50) 'PhoneNo')
EXEC sp_xml_removedocument @index
The above code snippet will give following result
...
Lets say we have to return a non-null from more than one column, then we can use COALESCE function
...
If hourly_wage, commission are null and salary is not null then salary will be returned
...
What are joins in SQL Server?
Using joins, we can get the data from two or more tables based on logical condition between the tables
...
techbubbles
...
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
...
e
...
e
...
When-mouse-button-presed,when-mousedoubleclicked,etc
Navigational Triggers :: These Triggers are fired as a result of Navigation
...
g : Post-Text-item,Pre-text-item
...
We cannot call restricted procedures like go_to(??my_block
...
The Difference between Key-next and Post-Text is an very important question
...
Key next will not fire unless there is a key event
...
In DELETE we can rollback
Some more differences related to Truncate and Delete
1) Delete keep the lock over each row where Truncate keps the lock on table not on all the row
2) Counter of the Identity column is reset in Truncate where it is not reset in Delete
...
An automatic commit ocurrs under the following circumstances
NOTE: This is objective type question, Please click question title for correct answer
...
Values are atomic
...
Column values are of the same kind
...
Each row is unique
...
The sequence of columns is insignificant
...
The sequence of rows is insignificant
...
Each column must have a unique name
...
Such tables are referred to as being in the "first normal form" (1NF)
...
The key benefit of the one value property is that it simplifies data manipulation logic
...
For example, a Product_Price column contains only specific to product
price
...
It simplifies data access because developers and users can be certain of the type of data contained in a given column
...
Because all values are from the same domain, the domain can be defined and enforced with the Data Definition Language (DDL)
of the database software
...
Such columns are called primary keys
...
The Sequence of Columns is Insignificant
Ordering of the columns in the relational table has no meaning
...
The benefit
of this property is that it enables many users to share the same table without concern of how the table is organized
...
The Sequence of Rows is Insignificant
This property is analogous the one above but applies to rows instead of columns
...
Adding information to a relational table is simplified and does not affect existing queries
...
In general, a column name need
not be unique within an entire database but only within the table to which it belongs
...
Use as much as possible WHERE clause filters
...
Select only those fields which really require
...
Joins are expensive in terms of time
...
The join type is important as well (INNER, OUTER)
...
In SQL server 2005 we can use sp_columns, sp_tables or sp_help
...
sp_tables will show list of tables in the database
What is the Maximum number of input and output parameters in Stored procedure in SQL Server 2000 ?
1024
What are the limits of Sql Server 2000?
When you are creating or changing a stored procedure,please keep in mind that
The name of the procedure is a standard Transact-SQL identifier
...
Stored procedures may contain up to 1,024 input and output parameters
...
The maximum size of the body of the stored procedure is 128MB
...
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
...
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
...
It is not physically stored in the hard disk, it is stored in the memory
...
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 command do we use to rename a db?
sp_renamedb ‘oldname’ , ‘newname’
What do you mean by COLLATION?
Collation is basically the sort order
...
What are the OS services that the SQL Server installation adds?
MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)
What is log shipping?
Logshipping is a new feature of SQL Server 2000
...
From Enterprise Manager we can
configure the logshipping
...
If one server fails, the other server will have the same db and we can use this as the DR (disaster recovery) plan
...
What is a deadlock?
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece
...
SQL Server detects deadlocks and terminates one user’s process
what is ACID?
ACID (an acronymn for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when
evaluating databases and application architectures
...
Atomicity is an all-or-none proposition
...
Isolation keeps transactions separated from each other until they’re finished
...
Above four rules are very important for any developers dealing with databases
What Is DTS?
DTS is a set of tools you can use to import, export, and transform heterogeneous data between one or more data sources, such as Microsoft
SQL Server, Microsoft Excel, or Microsoft Access
...
ODBC (Open
Database Connectivity) data sources are supported through the OLE DB Provider for ODBC
...
Continually
backing up the transaction logs from a source database and then copying and restoring the logs to a destination database keeps the
destination database synchronized with the source database
...
What are sequence diagrams? What you will get out of this sequence diagrams?
Sequence diagrams document the interactions between classes to achieve a result, such as a use case
...
The sequence diagram lists objects horizontally, and
time vertically, and models these messages over time
...
What is the purpose of UPDATE STATISTICS?
Updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view
...
There are six RAID levels 0
through 5 offering different levels of performance, fault tolerance
What are three SQL keywords used to change or set someone’s permissions?
Grant, Deny and Revoke
What is DTC?
The Microsoft Distributed Transaction Coordinator (MS DTC) is a transaction manager that allows client applications to include several
different sources of data in one transaction
...
This will return you GUID
What is Check Constraint?
Check constraint specifies a condition that is enforced for each row of the table on which the constraint is defined
...
Can we create a Foreign Key with out Primary Key?
Yes
...
If a string is less than the maximum length, then it is stored verbatim
without any extra characters
...
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
...
g
...
)
Use char when your strings are always going to be the same length (e
...
phone numbers, zip codes, etc)
...
This causes the second connection to wait until the
first connection releases its locks
...
Blocking is not the same thing as a deadlock
...
sql-server-performance
...
aspx
What is the use of bit data type and what kind of data is stored into it?
Bit datatype is used to store boolean information like 1 or 0 where 1 is considered as true and 0 is considered as false
...
What is user-defined data type in SQL Server?
User-defined data types can be used when several tables must store the same type of data in a column and you must ensure that these
columns have exactly the same data type, length, and nullability
...
microsoft
...
80)
...
In terms of functionality it is similar to C# or VB
...
sqlteam
...
Once SPs are encrypted, the original text of the SP are not visible
...
What is Distributed Queries in SQL Server?
Distributed queries access data from multiple heterogeneous data sources
...
Microsoft SQL Server supports distributed queries by using OLE DB
...
Distributed data stored in multiple instances of SQL Server
...
Heterogeneous data stored in various relational and nonrelational data sources accessed by using an OLE DB provider
...
microsoft
...
aspx
Thanks
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
...
For example
SELECT CustName, CustOrderDate, (SELECT DateTimeOrdered FROM OrderDetails as ODetails WHERE Ord
...
SalesOrderID) AS OrderedDateTime FROM Orders AS Ord
What are the restrictions of using Subquery in SQL Server?
Subquery can be used where an expression is possible
...
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)
...
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
...
The ntext, text, and image data types cannot be used in the select list of subqueries
...
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
...
The DISTINCT keyword cannot be used with subqueries that include GROUP BY
...
The COMPUTE and INTO clauses cannot be specified
...
ORDER BY can only be specified when TOP is also specified
...
A view created by using a subquery cannot be updated
...
A fill factor of 0% and 100% means the same thing to SQL Server
NOTE: This is objective type question, Please click question title for correct answer
...
1
...
2
...
3
...
4
...
5
...
6
...
Default security model in Microsoft SQL Server ?
NOTE: This is objective type question, Please click question title for correct answer
...
What is Two-Phase Commit ?
Two-phased commits are the way to keep multiple servers synchronized
...
”
In SQL Server 6
...
In SQL Server 6
...
What is Database snapshot ?
A read-only, static view of a database at the moment of snapshot creation
...
This data can reside in either a page or a file
...
What is DRI ?
Declarative Referential Integrity :
The DRI actions enforced by FOREIGN KEY constraints can be supplemented with additional referential integrity logic defined in triggers on
a table
...
select * from mytable for xml auto
There are three mode of returning XML and they are auto, raw and explicit
For more details see http://www
...
ddj
...
scuk
What is OPENXML in SQL Server?
OPENXML can parse the xml data in SQL server very efficiently in SQL Server
...
We need to specify the path of the xml element using xpath
...
--------------------------------1 Mohan 34343
2 Sita 23432
--------------------------------What is the use of COALESCE in SQL Server?
To modify a DEFAULT definition, We must first DELETE the existing DEFAULT definition and then Re-CREATE it with the new definition
...
What is Typed vs
...
But it allows
...
Typed XML :
I create one Table with one column as XML datatype
...
This is
called 'Typed XML '
1
...
, )
CREATE XML SCHEMA COLLECTION PandianXMLSchema AS '
GO
I have created one XML SCHEMA named 'PandianXMLSchema ' ( It will say what is the name of the 'Element ' - and Type of the Element )
...
I create one Table with one column as XML datatype along with XML SCHEMA which we created above
...
CREATE TABLE TABLE2
(XMLSample
XML(PandianXMLSchema))
GO
3
...
Location: /
Because, the data 'TEST' is not an INTEGER and Its not encloused with 'Dotnetfunda ' element
...
Location: /
Because, the data '123' is an INTEGER
...
INSERT TABLE2 VALUES('
- It will also throw an Err
Msg 6926, Level 16, State 1, Line 1
XML Validation: Invalid simple type value: 'TEST'
...
INSERT TABLE2 VALUES('
(1 row(s) affected)
XMLSample
Cheers
What id the Max Length of nVarchar in SQL Server?
NOTE: This is objective type question, Please click question title for correct answer
...
How to Get SQL Server Version ?
@@VERSION
Returns the date, version, and processor type for the current installation of SQL Server
...
SELECT @@VERSION
What are the disadvantages/limitation of the cursor?
Cursor requires a network roundtrip each time it fetches a record, thus consume network resources
...
Use of Bulk Copying ?
Bulk copying is used to transfer large amount of data
...
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
...
Another major difference is that, primary key doesn’t allow
NULLs, but unique key allows one NULL only
...
How can we Rollback the Transaction in DataBase?
We can Rollback the Transaction using "ROLLBACK TRANSACTION" Command
How Can we Write the Transaction Block in SQL Server?
BEGIN TRANSACTION
Statement 1
Statement 2
...
IF(@ERROR>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
Difference Between Truncate Table and Delete Table Command in SQL Server?
In case of Delete Statement Log is maintained
...
Which is the best method to get the single value from Database?
NOTE: This is objective type question, Please click question title for correct answer
...
What will be the output of the following code? Declare @Strings VARCHAR(20) Select @Strings ='Lakhan Pal Garg' Select
SUBSTRING(@Strings,-9,16
NOTE: This is objective type question, Please click question title for correct answer
...
Which Statement describe the COALESCE ?
NOTE: This is objective type question, Please click question title for correct answer
...
NOTE: This is objective type question, Please click question title for correct answer
...
How do you determine the maximum nested-level of Stored Procedure ?
The current nested level can be determine by : @@NESTLEVEL, The maximum nested level is 32
...
Creating stored procedure :
CREATE PROC PROC_SAMPLE1
AS
BEGIN
PRINT @@NESTLEVEL
EXEC PROC_SAMPLE1
END
2
...
Result :
1
2
3
...
32
Msg 217, Level 16, State 1, Procedure PROC_SAMPLE1, Line 5
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
...
Which of the following is a fixed length DataType?
NOTE: This is objective type question, Please click question title for correct answer
...
NOTE: This is objective type question, Please click question title for correct answer
...
Primary key creates cluster index on the column
...
So is it possible to have cluster index on Unique key column?
The answer is YES
...
You cannot create an Index on a column of the following data type
NOTE: This is objective type question, Please click question title for correct answer
...
How to get a row was inserted most recently in a table?
select top 1 * from tablename order by ColumnName desc
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
What is Row_Number()?
ROW_NUMBER() returns a column as an expression that contain's the row number within the result set
...
Coalesce returns the first non-null expression among its arguments
...
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 commission is non-null and other two columns are null then commission will be returned
...
Use following article to know about all types of joins
http://www
...
com/sql-server/joins-in-sql-server/
which Caluse returns only one copy of each set of duplicate rows selected
NOTE: This is objective type question, Please click question title for correct answer
...
Key Triggers :: Key Triggers are fired as a result of Key action
...
g :: Key-next-field, Key-up,Key-Down
Mouse Triggers :: Mouse Triggers are fired as a result of the mouse navigation
...
g
...
E
...
We also have event triggers like when ?Vnew-form-instance and when-new-block-instance
...
first_item??) in the Navigational triggers
But can use them in the Key-next-item
...
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
...
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
What is the difference between DELETE and TRUNCATE?
In TRUNCATE we can not rollback
...
3) Trigger is not fired in Truncate where as trigger is fired in Delete
...
What are the properties of the Relational tables?
Relational tables have six properties
1
...
2
...
3
...
(1) OPENXML in SQL Server 2000
For SQL Server 2005:
(2) query()
(3) value()
(4) nodes()
(5) exists()
(6) Modify()
How we can Modify XMl Data in SQL Server 2005?
Using modify method we cam modify the data in XML
...
modify('replace value of (/root/item/@value)[1] with sql:variable("@value")')
select @xml
How to Insert XML Data into Existing XML Node?
declare @xml xml
set @xml = '
select @xml
declare @value varchar(10)
set @value = 'val1'
set @xml
...
How we can get the List of System Tables in DataBase?
select * from Sys
...
Objects where Type='u'
How We can Get List of Store Procedures?
select * from Sys
...
Objects where Type='fn'
Query to Get List of Table Valued Functions?
select * from Sys
...
Objects where Type='tr'
Query To Get The Column Name,DataType, And Length of columns in a Tables?
select column_name, data_type, character_maximum_length from information_schema
...
views
Query to display List of All Databases in SQL Server 2005/2008?
SELECT * FROM Sys
...
How we can add Description to the Column using Sql Command?
We can Add Description to Column using sp_addextendedproperty System Store Procedure
...
sp_addextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' ,
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName',
@level2type=N'COLUMN',@level2name=N'ColumnName'
Thanks & Regards
Lakhan Pal Garg
How To Update Description Value for a Column in Table using SQL Command?
We can Update Description to Column using sp_updateextendedproperty System Store Procedure
...
sp_updateextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' ,
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName',
@level2type=N'COLUMN',@level2name=N'ColumnName'
Thanks & Regards
Lakhan Pal Garg
How To Delete Description Value for a Column in Table using SQL Command?
We can Delete Description from Column using sp_dropextendedproperty System Store Procedure
...
sp_dropextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' ,
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName',
@level2type=N'COLUMN',@level2name=N'ColumnName'
Thanks & Regards
Lakhan Pal Garg
How To Get Description Value for all Column in Table using SQL Command?
To Get Description of Columns we need to use system function sys
...
Command To Get Description Data for all Columns:
SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA',
'dbo', 'TABLE', 'YourTable Name Here', 'COLUMN', NULL)
Thanks & Regards
Lakhan Pal Garg
How To Get Description Value for Single Column in Table using SQL Command?
To Get Description of Single Column we need to use system function sys
...
we need to pass the column Name is this
case
...
SQL File for the Store Procedure in SQL Server 2005?
Using sqlcmd utility we can create a
...
Sample Code To Create file:
Suppose the name of the procedure is: proc_GetUserList_sp
DB UserName/Password: sa/sa
Server name=MyServer
Database Name: UserDB
Command From Above data is:
sqlcmd -U sa -P sa -S MyServer -d UserDB -Q "exec sp_helptext proc_GetUserList_sp" -h-1 -k 1 >> c:\DB\proc_GetUserList_sp
...
if we don't want to return the number of records
affected then we can use Set NOCOUNT ON;
What is the system table that holds the Store Procedure Script?
Sys
...
Procedures contains the name of the store procedure
...
with this we prevent the command to lock the whole page or table
...
dm_tran_locks is the System table used to store the locking infomation
...
shared lock is used for select operation
...
g of shared lock is : WITH (HOLDLOCK)
What is the name of the System table from where we can read the Master Files (
...
ldf) Path?
From sys
...
Name of the table that contains the Configuration value of SQL Server ?
sys
...
systypes contains the Data type available in SQL Server
...
Sample Query:
BEGIN
SELECT 'Lakhan Pal'
DECLARE @time char(5)
SET @time=CONVERT(char(5),DateAdd(mi, 1, GetDate()),108)
WAITFOR TIME @time
SELECT ' Garg'
END
In the above Code SELECT 'Lakhan Pal' will be execute first and then after 1 min second select statement SELECT ' Garg' will be executed
...
Are DDL Triggers available with SQL Server 2005 ?
4
...
5
...
6
...
Values Are Atomic
Columns in a relational table are not repeating group or arrays
...
The
atomic value property of relational tables is important because it is one of the cornerstones of the relational model
...
Column Values Are of the Same Kind
All values in a column come from the same set of values or domain
...
It never contains other information such as comments, status flags, or even weekly salary
...
It also simplifies data
validation
...
Each Row is Unique
Each unique row ensures that no two rows in a relational table are identical; there is at least one column, or set of columns, the values of
which uniquely identify each row in the table
...
This property guarantees that every row in a relational table is meaningful and that a specific row can be identified by specifying the
primary key value
...
Columns can be retrieved in any order and in various sequences
...
It also permits the
physical structure of the database to change without affecting the relational tables
...
The main benefit is that the rows of a relational table can
be retrieved in different order and sequences
...
Each Column Has a Unique Name
Because the sequence of columns is insignificant, columns must be referenced by name and not by position
...
How do you optimize stored procedures in SQL Server 2005
1
...
Where Clause is the most important part for optimization
2
...
3
...
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
...
What is DESCRIBE command in SQL Server 2005? What is its purpose? How to use it?
DESCRIBE is used to see table structure
...
sp_columns
will show list of columns and its details in table
...
The maximum length of any identifier is 128 characters
...
The body of the stored procedure consists of one or more
Transact-SQL statements
...
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
Thanks & Regards
Lakhan Pal Garg
Write a Query in SQL Server to get the Parameter list of given Store Procedure
...
SELECT * FROM sys
...
Objects O ON O
...
object_id WHERE O
...
sp_lock
Which system table holds the details of all the processes running on the Microsoft sql server?
The name of the system table is sysprocesses
...
How can we find the open transactions details in sql server?
Yes, it is possible
...
What is the name of command in sql server 2005 which is used to kill any process?
The command name is kill
...
What is the command name to shrink the data file and log file size in SQL server 2005?
The command name is : DBCC SHRINKDATABASE (Database Name)
This command will shrink the data file and log file size
...
dbcc shrinkdatabase (TempDAabase,10)
This command will free only 10% space
...
With the insertion and deletion of data,
tables named “INSERTED” and “DELETED” gets created in SQL Server which contains modified/deleted data
...
What is difference between RANK and DENSE_RANK
...
For an example, if you have three records at position one
...
RANK() will rank the next record as fourth record while DENSE_RANK() will rank it as
second
...
You can create only one clustered index in table
...
Suppose, you have created table with following:
CREATE TABLE [dbo]
...
Table1
GO
CREATE UNIQUE CLUSTERED INDEX idx_viewClustered ON dbo
...
When it is set to ON, the concatenation will result null and when it is set to OFF, it will result the string
...
SET NOCOUNT specified settings are in effect at…
NOTE: This is objective type question, Please click question title for correct answer
...
To terminate SQL Server immediately, used is…
NOTE: This is objective type question, Please click question title for correct answer
...
NOTE: This is objective type question, Please click question title for correct answer
...
So, for the given SQL, output will
be NULL
...
In case of positive, negative and zero it returns +1,-1 and 0
consecutively
...
You can do this using, ISNUMERIC()
SQL_VARIEANT datatype column can be any datatype except?
NOTE: This is objective type question, Please click question title for correct answer
...
Which of the following is a Large object data type?
NOTE: This is objective type question, Please click question title for correct answer
...
select COUNT(column_name) from table_name counts only
NOTE: This is objective type question, Please click question title for correct answer
...
What will be the OutPut of the following Query? Select CELING(7
...
How you will create a UNIQUE Constraint when the table is already created?
ALTER TABLE Persons
ADD UNIQUE (Column_Name)
what is CHECK Constraint?
The CHECK constraint is used to limit the value range that can be placed in a column
...
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row
...
With below provided query you can get desired result
...
Here in below
example they are set to 0 and 200 respectively
...
[table_name] WITH(NOLOCK)
--* Optional section if you have some joins then place them here *---inner join vTanks on vTanks
...
ClientId
--and vTanks
...
Facilityid
) primarySelect
WHERE Id = 294 AND Archive <> 1 -- specify your conditions here
) As joinTable
What is the maximum size of an SQL Server 2000 Database?
The maximum size for SQL Server 2000 databases is 1,048,516 TB
...
What are the extensions of these three type of file in above question
...
mdf
...
ndf
...
ldf
how you can rename a database ?
o rename a database first get the database into Single user mode
...
PRINT @@TRANCOUNT
-- The BEGIN TRAN statement will increment the transaction count by 1
...
COMMIT
PRINT @@TRANCOUNT
--Results
--0
--1
--0
What is the maximum limit for Primary Key?
10 fields in MS Acces
900 Bytes in SQL Server
What is a Stored Procedure?
Its nothing but a set of T-SQL statements combined to perform a single task of several tasks
...
What is an Index?
Hope this helps
...
Two of the simplest ways are described below assuming your table is having
column(date1) containing value of data creation
...
delete from table1
where id1 not in
(
select top 10 id1 from table1
order by date1 desc
)
Way2:
With this query we are listing records in descending way with date as key and having index more then 10
...
delete from table1
WHERE id1 in (
SELECT id1
FROM
(
SELECT id, ROW_NUMBER() OVER(ORDER BY date1 DESC) AS rownumber
FROM table1
) AS a
WHERE rownumber > 10
)
How can you declare and initialize variables in a single line with SQL Server ?
With SQL Server 2005, we need to declare and initialize variables individually
...
Standard
2
...
Table type
4
...
View
What is XQuery?
XQuery is a language that is designed to query an XML document in SQL Server
...
After that you can use
OPEN_XML method to iterate through the xml result set
...
What is sp_XML_RemoveDocument procedure?
This procedure is used to remove the XML document from memory created using sp_XML_Preparedocument procedure
...
It has two options
1
...
2
...
See below query:
select * from syslogins
Does Views occupy memory?
No
...
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
...
There can be only one clustered index per table
...
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
...
Instead, the leaf nodes contain index rows
...
"Unique Key" is the ideal column for a nonclustered index
Non-clustered indexes are good for random searches
...
It's a physical structure containing pointers to the data
...
Index are created in an existing table to quickly and efficiently lookups
...
There are two type of index available
...
Therefore only one clustered index can be created on each table because the data rows
themselves can only be sorted in one order
...
Therefore more than one Non clustered indexes can be created on each table
...
Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command
...
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE ?
Both Clause are used to check the Condition at the time of Retrival of records in Database
...
Having Clause is basically used only with the GROUP BY function in a query
...
ROLLBACK do the same thing just terminate the currenct transaction but one another thing is that the changes made to database are
ROLLBACK to the database
...
(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
...
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
...
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
...
Then 2 tables automatically create on backend
...
These tables are called Magic Tables
...
What is the use of COALESCE function in SQL Server ?
Returns the first not null expression among its arguments
...
Let X is NULL, Y='5' and Z='9'
Here Output is : Y
What type of Authentication does Microsoft SQL Server support?
2 Type of Authentication :
1
...
SQL Server authentication
What is the difference between "dynamic SQL" and "stored procedure" ?
Dynamic sql is the bunch of statements that dynamically constructed at run time and not stored in database
...
How to determine the service pack currently installed on SQL Server?
The global variable @@Version is used to determine the service pack installed
...
00
...
06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer
Edition on Windows NT 5
...
(True /False)
False
...
Its auto Incremented Column defined by the user
...
You can use FOR clause with XML or BROWSE options
...
A pattern can include regular characters and wildcard characters
...
Wildcard characters,
however, can be matched with arbitrary fragments of the character string
...
There are 2 Wildcard characters i
...
'%' and '_'
%
Any string of zero or more characters
...
_ (underscore)
Any single character
...
What are the major new features in SQL Server 2008 ?
Transparent Data Encryption
...
When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data
retrievals are much faster when we have an index
...
There are three types of sort order Dictionary case sensitive, Dictonary - case insensitive and Binary
...
We should have two SQL Server - Enterprise Editions
...
In logshipping the transactional log file from one server is automatically updated into the backup database on the
other server
...
Where do you think the users names and passwords will be stored in sql server?
They get stored in master db in the sysxlogins table
...
Each
process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated
...
For a reliable database all this four attributes should be achieved
...
Consistency guarantees that a transaction never leaves your database in a half-finished state
...
Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal
termination
...
Connectivity is provided through OLE DB, an open-standard for data access
...
What is Log Shipping?
In Microsoft SQL Server, you can use log shipping to feed transaction logs from one database to another on a constant basis
...
This allows you to have a backup server and also provides a way to offload
query processing from the main computer (the source server) to read-only destination servers
...
Because UML is designed for object-
We can do this through SQL Job
Tell me some system defined procedures
sp_addsrvrolemember = Used to add a login as a member of fixed server role
...
sp_bindrule =
Binds the rule with database object
...
sp_rename = Renames the table,index and
views
...
sp_helpindex = Used to display info about the indexes
...
sp_helptext = Used to display definition of user-defined rule
...
sp_lock = Used to display info about locks
...
How do we list all Databases in a server using query?
select * from sysdatabases
How do we List all table names?
By the query
select * from sysobjects where xtype='U' order by name
How do we list all column names of a table?
Using the query
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA
...
The procedure should be paramerized along with data
types
...
This is stored inside of heap
...
In most of our real time scenario we are using the temp table which is physically created in tempdb
...
A table variable goes out of scope immediately after the batch end
...
Write a sample syntax of table variable?
DECLARE @TableVariableSample table (ID int IDENTITY(1,1),Name VARCHAR(150) NOT NULL)
How to execute the stored procedure?
With exec keywork or directly we can execute a stored procedure
What is the use of Dense_Rank in Sqlserver?
Dense_Rank function produces the gaps in the ranking system
How to rename a database in SQLserver?
using the procedure sp_renamedb
...
Following is the example
SELECT DATENAME(mm,GETDATE())
What is BCP?
The Bulk Copy Program (BCP) is a command-line utility that ships with SQL Server
...
What is DTS?
Data Transformation Services (DTS) in SQL Server 2000 provides a set of graphical tools and programmable objects to export and import
data
...
Which has more provisions for the export/import functionality? Whether we can customize the data through BCP?
DTS has more provisions than BCP
...
How do we get Current Months First Day?
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getDate())-1),getDate()),101)
What is compute in sql server?
Compute generates totals that appear as additional summary columns at the end of the result set
...
It calculates the summary values for subgroups, or
a summary value for the entire result set
...
What is the use of @@CPU_BUSY statement?
@@CPU_BUSY returns the time that SQL Server has spent working since it was last started
...
What is the use of @@VERSION statement?
@@VERSION statement returns version, processor architecture, build date, and operating system for the current installation of SQL Server
...
What is the purpose of @@FETCH_STATUS statement in T-SQL?
To know the status of the last cursor FETCH statement issued against any cursor currently opened by the connection
What is Modulo EQUALS statement in SQL server?
Modulo divides one number by another and return the result of the operation
...
What is the use of @@ROWCOUNT?
Returns the number of rows affected by the last statement
...
If the rows are more than 2 billion, how can I set the rowcount?
Using ROWCOUNT_BIG statement
...
How do we raise the error message?
Using RAISERROR method
...
What is the use of @@IDLE in Transact-SQL?
@@IDLE informs the time that SQL Server has been idle since it was last started
What is Replication and Database Mirroring?
Database mirroring can be used with replication to provide availability for the publication database
...
At any given time, only one copy of the database is currently
available to clients which are known as the principal database
...
Mirroring involves applying the transaction log from every insertion, update, or
deletion made on the principal database onto the mirror database
...
Using these rules complex information is broken down into its most
simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules
...
If a large amount of deletions any modification or Bulk Copy
into the tables has occurred, it has to update the indexes to take these changes into account
...
What is SQL Profiler?
SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server
...
For example, you can monitor a production environment to see
which stored procedures are hampering performances by executing too slowly
...
If traces are becoming too large, you can filter them based on the
information you want, so that only a subset of the event data is collected
...
What is Log Shipping?
Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring
them onto a standby server
...
In log shipping the transactional log file from one server is
automatically updated into the backup database on the other server
...
The sequence diagram lists objects horizontally, and
time vertically, and models these messages over time
...
What is the purpose of UPDATE STATISTICS?
Updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view
...
There are six RAID levels 0
through 5 offering different levels of performance, fault tolerance
What are three SQL keywords used to change or set someone’s permissions?
Grant, Deny and Revoke
What is DTC?
The Microsoft Distributed Transaction Coordinator (MS DTC) is a transaction manager that allows client applications to include several
different sources of data in one transaction
...
This will return you GUID
What is Check Constraint?
Check constraint specifies a condition that is enforced for each row of the table on which the constraint is defined
...
Can we create a Foreign Key with out Primary Key?
Yes
...
If a string is less than the maximum length, then it is stored verbatim
without any extra characters
...
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
...
g
...
)
Use char when your strings are always going to be the same length (e
...
phone numbers, zip codes, etc)
...
This causes the second connection to wait until the
first connection releases its locks
...
Blocking is not the same thing as a deadlock
...
sql-server-performance
...
aspx
What is the use of bit data type and what kind of data is stored into it?
The are three levels of abstraction are as follows:
Physical level: The lowest level of abstraction describes how data are stored
...
View level: The highest level of abstraction describes only part of entire database
...
5678,2) FROM DUAL;
1200
What is the error in the following command? DROP TABLE &TABLE_NAME;
Variable names should start with an alphabet
...
So it will give a compilation error
...
datediff(parameter,date1,date2)
What is the use of DATEPART function in SQL?
datepart(parameter,date) will extract the date part according to the parameter passed
...
Which function is used to find the largest integer less than or equal to a specific value?
FLOOR
Floor is used to round a number down to the last whole number
...
Examples:
SELECT FLOOR(-5
...
14);
This will be round down to 5
What is the use of DESC in SQL?
DESC is used to describe a schema as well as to retrieve rows from table in descending order
...
Which option is used to drop constraints specified on the table?
DROP option in the ALTER TABLE command is used to drop constraints specified on the table
...
Which command is used to create a table by copying the structure of another table?
CREATE TABLE
...
TRUNCATE is a DDL command whereas DELETE is a DML command
...
DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back
...
WHERE can be used with DELETE and not with TRUNCATE
...
It is True of False
True
What are the wildcards used for pattern matching?
_ is used for single character substitution
% is used for multicharacter substitution
Which command displays the SQL command in the SQL buffer, and then executes it?
RUN
It lists the commands in the SQL buffer before running and then execute (or re-execute) commands
...
Example:@Script1
...
Similarly @@ pathname will able to run a nested SQL Script
...
We can use different wildcard characters to specify the condition
...
What command is used to get back the privileges offered by the GRANT command?
REVOKE
What is the default language of SSIS Script Task?
VB
...
Is primary key Clustered or Non - Clustered Index in MS SQL
Clustered
What is fixed-length & variable-length characters? Tell me their differences??
In Sql Server there are two types of character data types
...
fixed-length (i
...
variable-length (i
...
NET based automation engine in Microsoft p/f , it can be embedded into
...
What is SqlBulkCopy in ADO
...
You can use a DataReader or DataTable as source data
store
...
Following is a sample
SqlBulkCopyColumnMapping mapColumn1 =
new SqlBulkCopyColumnMapping("EmpID", "ID");
What is a cube?
A cube is a collection of measures and dimensions for analyzing the data in detail
...
What is the full form of KPI?
Key Performance Indicators
In Analysis service what is the technology used define the expressions?
Multidimensional Expressions also abbrevated as MDX
How To know your SQL Server Version in SQL command prompt ?
SELECT @@VERSION
What is the use of xml datatype in SQL Server?
As the name indicates xnl datatype is used to store XML documents in SQL Server
...
microsoft
...
aspx
What is the maximum size of data we can store in the image type of SQL?
We can store binary data from 0 to 2^31-1 or 2,147,483,647 bytes in an image field of SQL Server
...
What is the difference between
...
rdlc report
...
rdl report is created using SQL Server Reporting service, But the
...
in rdlc 'c' stands for client side
...
What is SSIS
SSIS is know as SQL Server Integration service
...
Can we write Scripting Language like VB Script and JavaScript in SSIS Package?
Yes, We can Write VB Script and JavaScript in SSIS Packages using ActiveX Task Control
...
Adding months to a Date in SQL ??
Example below illustrates :
Select DATEADD(mm,1,getdate())
Adding minutes to a date ??
Example below illustrates :
Select DATEADD(minute,1,getdate())
Adding year to a date ??
Example below illustrates :
Select DATEADD(year,1,getdate())
How to get the month value/month part from the given Date?
There are three methods to get the Month part from given date:
DECLARE @GivenDate datetime
SET @GivenDate='2010-02-22 23:06:38
...
While querying the database we need to use 1 or 0 in the select statement to filter out the data instead of true and false
...
For more details, visit http://msdn
...
com/en-us/library/aa933121(SQL
...
aspx
Thanks
What is user-defined functions in SQL Server?
User-Defined Functions allows to define custom T-SQL functions that can accept zero or more parameters and return a single scalar data
value or a table data type
...
NET functions except the fact that code is written in T-SQL :)
For more visit http://www
...
com/article/user-defined-functions
Thanks
What is the use of Keyword WITH ENCRYPTION keyword in SQL Server?
WITH ENCRYPTION keyword is used to encrypt the text of the Stored Procedure
...
User who has created the SP will need to save the text to be used to create
SP somewhere safe to reuse it again or Users that have access to system tables or database files can retrieve the encrypted text
...
These data sources can be stored on either the same or different
computers
...
SQL Server users can use distributed queries to access the following:
#
...
#
...
For more details visit http://msdn
...
com/en-us/library/ms188721
...
Subquery is an
inner query or inner select, while the statement containing a subquery is also called an outer query or outer select
...
SalesOrderID =
ODetails
...
There are certain limitation of using Subquery, they are
A subquery is subject to the following restrictions:
#
...
#
...
#
...
To know the ID of a database DB_ID function is used
...
DB_ID(dbName)
Example:- SELECT DB_ID(Student) -->5
Which function is used to perform a full text search on a column?
FREETEXT function can perform a full text search on the specified column
...
It matches the meaning only not the exact value of the search-string
...
SELECT SYSTEM_USER
-->Abhisek
What is the use of MOD function?
MOD function is used to get the remainder of a division
...
MOD(dividend,divider)
Example:SELECT MOD(12,5)
it will output 2
...
Name the full-text predicates in SQL server
CONTAINS & FREETEXT
Name the ROW-Valued-Functions in Full Text Search
CONTAINSTABLE and FREETEXTTABLE
What are the various transaction isolation levels ?
read uncommitted
read committed
serializable and
repeat table read
What are the variuos lock types ?
SHARED
UPDATE
EXCLUSIVE
How can we check the locks defined in a DB ?
using inbuilt stored proceduce
sp_lock
Which function is used to know the database property in SQL Server
...
Example:SELECT DATABASEPROPERTYEX(Student,Version)
-->365
You can use this function to know other properties of your database
...
SELECT CHAR(90)
--->D
What is the use of APP_NAME()?
APP_NAME() returns the name of the application for the current section
...
SELECT APP_NAME()
It outputs "SQL Enterprise Manager" if you are currently working on it
...
What is the page size of Index Page ?
8 kb
Name the table where the Index information is saved
the table name is sys
...
(2) Open Properties --> Select Deployment Utility
(3) Set CreateDeploymentUtility to True
(4) Build Project
(5) Check the Deployment folder in bin
(6) Double click on
...
What the value of Identity_Insert to be set for letting the insertion in the identity column ?
It's ON and code is
set Identity_Insert tablename ON
Can we have option to use configuration file in dts package?
No, we don't have any option of configuration file in dts package
...
Name of the task that is used to execute batch file in SSIS package?
Execute Process Task is used to execute batch files
...
What is the result of "SELECT 2/2/4" in SQL ?
NOTE: This is objective type question, Please click question title for correct answer
...
Transaction logs are not recorded for the table variables
...
A procedure having temporary table is not pre-compiled, while an execution plan of procedures with table variables can be statically
compiled in advance
...
Table variables exist only in the same scope as variables
...
Also, they cannot be
used in an insert/exec statement
...
BCP utility is a command line utility
...
We can execute bcp command through xp_cmdshell only
...
We need to enable it
...
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
Can we restore the database forcefully when it is in use via SQL script?
When you try to restore the database (RESTORE DATABASE) through command and if it's in use then SQL SERVER will not allow you to do so
...
But there is a way to restore database forcefully
...
To restore database forcefully, one need to bring it in SINGLE USER mode
...
Make sure, you are always using MASTER database for this operation
...
--To Restore forcefully to an existing database which is in use
...
bak' WITH REPLACE
ALTER DATABASE virendra SET MULTI_USER
Thanks,
Virendra Dugar
Is it possible to find out
...
ldf file name from the backup(
...
It is possible to find out the name of
...
mdf file from the backup file
...
bak'
Select * from table1,table2
NOTE: This is objective type question, Please click question title for correct answer
...
synatx for this is as:
SET DATEFIRST 1
it means monday is the first day of the week
...
if it will return 1 then it means Monday is the first day but if it returns 7 then that means Sunday is the first day
...
This strategy works in the principal that either all databases are updated or none of them are updated
...
The transaction monitor waits for the acknowledgment of the pre-commit command, if received then commit command is sent to
each database, that results to make the database change permanent
...
How many Evaluation Modes are there in "SQL Server 2008 - Policy Based Management" ? Which mode is manual ?
#
...
#
...
#
...
#
...
#
...
How many database(s) can have in an instance of SQL Server 2005 ?
NOTE: This is objective type question, Please click question title for correct answer
...
Root Cause for Increasing the Transaction Log
...
Uncommitted Transactions
...
Extremely Large Transactions
...
Operations : DBCC DBREINDEX / CREATE INDEX
...
Client Applications do not process all results
...
While Restoring from Transaction Log Backups
...
Queries Time-Out before a transaction log completes the expansion and you receive false 'Log Full' error message
...
Microsoft SQL Server's default protocol ?
NOTE: This is objective type question, Please click question title for correct answer
...
(Replication also can achieve this)
Two-phased commits are considered to be “Tight Consistency
...
0, We must use the API functions and write some fairly advanced code
...
5, The DTC (Distributed Transaction Coordinator) makes the two-phase commit easy to implement
...
What is Deferred transaction ?
A transaction that is not committed when the roll forward phase of recovery finishes, and that cannot be rolled back during database startup
because the data required by rollback is offline
...
What is Dirty pages ?
Buffer pages that contain modifications that have not been written to disk
...
What is ETL ?
When it is ON - the number of affected rows will not be returned
When it is OFF - the number of affected rows will be returned
...
Note that either the SET NONCOUNT is ON or OFF, @@ROWCOUNT is always
updated with the number of rows affected
...
microsoft
...
aspx
How will you calculate maximum range of "INT" Data Type ?
The Formula Is:
2 ^ (N-1)
here, "N " is nothing but size of the Data type
...
1
...
Select (max_length * 8) 'Bit(s)' from sys
...
Now, we can apply the formula for Range
Select Power(Cast(2 as Varchar),(max_length * 8) -1) from sys
...
So, The two domains don't trust each other
...
When will SQL Server throw an Error: "MSSQLSERVER ERROR 576" ?
The record-size limit for tables that use SPARSE columns is 8018 bytes
...
When columns are converted between SPARSE and NONSPARSE types, Database Engine keeps a copy of the current record data
...
What is Instance-aware and Instance-unaware Services in SQL Server ?
Instance-aware services are associated with a specific instance of SQL Server, and have their own registry hives
...
ie:
1
...
SQL Server Agent
3
...
Reporting Services
5
...
They are not associated with a specific instance, are
installed only once, and cannot be installed side-by-side
...
Integration Services
2
...
SQL Server Active Directory Helper
4
...
SELECT 'DotnetFunda' 'SQL Server' 2
...
SELECT 'DotnetFunda' 'SQL Server'
2
...
Alias will be the column Name
...
Target will be the column Name
...
In this approach, any data is retrieved by an active user for updating,
must be locked or denied to other users until updating is not complete
...
In this case each must have to wait for the other to
unlock that resource
...
Then Y is said to be functionally dependent to X if a given value for each attribute in X
uniquely determines the value of the attributes in Y
...
ie: MSAccess to SQL Server, Oracle to SQL Server
What is Upgradation ?
Upgrading from one Edition/Version to another Edition/Version within the same Product type
...
You are in Server1
2
...
How will you validate whether the configured linked server to the Server2 is properly configured or not ?
Solution:
1
...
Execute the script given below to validate the remote server accessibility
...
If it throws an Err like : "Server 'Server2' is not configured for DATA ACCESS" then, You have to give Data Access server option
...
SQL Server connection is terminated after the query completes
...
Why ? and How to fix it ?
Solution:
Query Menu --> Query Options
...
Un-Select the check box "Disconnect after the query executes" and Click OK
...
This is referred to as "constant folding"
What is Forced Service ?
Database mirroring provides forcing service (with possible data loss) as a disaster recovery method to allow you to use a mirror server as a
warm standby server
...
Because forcing service
risks possible data loss, it should be used cautiously
...
But, we can not define FORCESEEK table hint on Table-valued function, Table variable and Openrowset objects/statements
...
A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to
reference the columns of a UNIQUE constraint in another table
...
What is Self-referencing table ?
FOREIGN KEY constraint can reference columns in the same table is called self-referencing tables
...
The following error occurred when we try to reference the different database
"The object name contains more than the maximum number of prefixes
...
"
How to identify the "Full-Text search Installed or Not" on your current instance of SQL Server ?
SELECT CASE [Full-Text Search] WHEN 1 THEN 'Full-Text Search Installed' ELSE 'Full-Text Search Not Installed' END 'Status' FROM
(
SELECT SERVERPROPERTY('IsFullTextInstalled') 'Full-Text Search'
) AS X
What are the key points to be followed when implementing Full-Text Search on View ?
When we implement a Full-Text Search on View, We have to follow the key rules
1
...
2
...
TableName"), Otherwise the following Err will
be thrown
Msg 4512, Level 16, State 3, Procedure VM_DataTypes, Line 2
Cannot schema bind view 'VM_DataTypes' because name 'TB_Category' is invalid for schema binding
...
3
...
dbo
...
Consider removing the hint
...
OUTER Join can not be used Inside the View
5
...
The Unique Index should be created on single column, Not with composite, Non-Nullable
7
...
How to Enable / Disable the Full-Text Index created on a View / Table ?
To Enable the Full-Text search Index already created on a View/Table :
EXEC dbo
...
Table or View Name', @action=N'activate'
To Disable the Full-Text search Index already created on a View/Table :
EXEC dbo
...
Table or View Name', @action=N'deactivate'
What is Delegation in SQL Server ?
SQL Server and Windows can be configured to enable a client connected to an instance of SQL Server to connect to another instance of SQL
Server by forwarding the credentials of an authenticated Windows user
...
What is "Double Hop" in SQL Server ?
One computer connects to another computer to connect to a third computer, is called a double hop
...
1
...
2
...
3
...
4
...
5
...
Maximum How many Row(s) will be there in Sys
...
Indexes view as 'HEAP' the Index_ID is '0', If we create a CLUSTERED
Index on that table then, The 'HEAP' will be replaced as 'CLUSTERED' the Index_ID is '1'
...
1005
...
But, The Index_ID in Sys
...
Indexes View for a table)
...
Finally, An Index_id will be 0 or 1 to 250 and 256 to 1005 (Maximum 1000 Entries will be there in Sys
...
sum, average) to group rows with same data together
...
The complex process of copying and cleaning data from heterogeneous sources
...
What is Fragmentation ?
A difference between the physical and logical ordering of index data that can occur when data modifications are made
...
What is Lock escalation ?
The process of converting many fine-grain locks into fewer coarse-grain locks
...
What is point-in-time recovery ?
The process of recovering only the transactions within a log backup that were committed before a specific point in time, instead of
recovering the whole backup
...
What is Roll forward ?
To apply logged changes to the data in a roll forward set to bring the data forward in time
...
How do you Copy Databases to SQL SERVER 2005 from various versions (6
...
5/7
...
0, 2000 or 2005
...
5 or earlier are cannot be restored in SQL Server 2005
...
0 or 2000 cannot be restored to SQL Server 2005
...
0 log backups that contain create-index operations cannot be restored to SQL Server 2000 or 2005
...
To restore a database created in the default location of either SQL Server
7
...
When we install SQL Server 2005, any existing databases (SQL Server 7 or 2000) are automatically upgraded
...
5 or earlier has not been upgraded to SQL Server 2005, we have to convert it to SQL Server 2005 by using one of the
following methods:
1
...
2
...
Number of PRIMARY KEY/FOREIGN KEY Constraints in a Table in SQL Server 2005/2008 ?
NOTE: This is objective type question, Please click question title for correct answer
...
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables
...
Interdependencies between these tables are defined by
the data values
...
Object Explorer only displays the Maintenance Plans node for users who are members of the sysadmin fixed server role
...
dbo
...
sp_maintplan_start @PlanID,NULL
GO
How to execute a SQL Job using script ?
DECLARE @JobID VARCHAR(36),
@retval INT
SELECT @JobID = Job_ID FROM msdb
...
sysjobs_view
WHERE [Name] = 'SQL Job Name'
EXEC @retval = msdb
...
sp_start_job @job_id = @JobID
GO
How to identify the SQL Server Start/Restart Date & Time ?
1
...
dm_os_sys_info
GO
2
...
databases WHERE [name] ='tempdb'
GO
Both the query have some difference on it's Time, Because, The Tempdb database will be Re-created after the SQL Server service started
...
)/1024
...
)/1024
...
)/1024
...
)/1024
...
)/1024
...
)/1024
...
)/1024
...
dm_os_sys_memory
Which SQL Server Profiler event enables to trace the users involved in Deadlock cycle ?
NOTE: This is objective type question, Please click question title for correct answer
...
What are the various options to move the data/databases ?
We have lots of options, we have to choose our option depending upon our requirements
...
BACKUP/RESTORE
2
...
Replication
4
...
Logshipping
6
...
DTS or DTSX
8
...
INSERT…SELECT or SELECT…INTO
10
...
What are the restrictions apply to compressed backups ?
The compressed backup was introduced in SQL Server 2008 Enterprise
...
The following are the compressed backup restrictions :
1
...
2
...
3
...
How to enable compressed backup ?
Compressed backup was introduced in SQL Server 2008 Enterprises edition and later only
...
sp_configure N'backup compression default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
Once you enable the compressed backup, When you perform the backup next time, The backup will be compressed by default
...
Device Throughput Bytes/sec counter of SQLServer:Backup Device Performance object
2
...
These models define a set of network layers
and it provides greater flexibility and easy access to data
...
Explain about the hierarchical model of database?
The hierarchical data model means a base data can have its corresponding branches i
...
the data is organized into tree structure
...
In this model you can form relationship among many tables with certain
concepts
...
Explain what is object oriented databases?
The object oriented database is a database management system which supports the creation and modeling of data as objects
...
e
...
These databases are used to store complex data, to
store information related to multimedia, Engineering databases, spatial databases etc
...
These are used in applications such as informational
portals, document exchanges, and product catalogs
...
There are two
different XML database class exists
...
This
shows that the database does the conversion to itself
...
Native XML: These types of databases depend on XML and uses XML documents as a storage unit
...
It manages the documents by grouping them into a logical collection and can manage multiple collections simultaneously
...
• It is difficult to access data in File Processing System but in Database it can be done easily
...
• In File System you will not get data integrity but the Database can have data integrity
...
• There is no security in the File System but you will get this facility in Database
...
The transaction can
be rolled back completely by specifying the transaction name in the Rollback statement or to cancel any changes to a database during
current transaction
...
Rollforward: Recovering a database by applying different transactions that recorded in the database log files
...
e
...
Define Concurrency and Concurrency control? Explain what are the different techniques?
Concurrency allows us the simultaneous access of same data by different users
...
It avoids the adverse effect
of one transaction to another transaction
...
Pessimistic concurrency control: It assumes when a conflicts happen
...
The system lock prevents users from modifying data in a way so that it will not affect other user
...
Optimistic concurrency control: This is called optimistic because the conflicts between transactions are rare and it doesn't require locking
...
It is mainly used when there is low contention for data and it checks for conflicts
before the commit
...
These are highly
important to use transactions when working with databases
...
For this successful transaction that is from begin to end transaction, it follows the
term ACID (Atomicity, Consistency, Isolation, Durability)
...
e
...
They are
responsible for reviewing the contents in the database
...
Also their responsible is to backed up data regularly and prevent from unauthorized
access
...
The different functionalities of a database administrator are maintaining database system software, developing physical database
structures and data dictionary
...
The basic responsible is how to install and configure the RDBMS applications and also they have to know the requirements of the
software application in terms of functions and assure the data integrity
...
Following are some basic
steps for a Data Administrator,
• Specification of organization data
...
• Validating the data and files
...
What is difference between SUBSTR and INSTR in the SQL?
The SUBSTR function returns a specific portion of a string
Example: SUBSTR('DotNetFunda',6)
The result will be: DotNet
The INSTR function provides character position in a pattern of string
...
‘==’ is for the comparison between string with number, number with number etc
...
Example:
// for '=' operator
if(a=b+c)
{
alert('true')
}
It will be true if it not contains any zero, false, and any empty string
...
Here we are comparing string with the
number
...
True
DEFAULT definitions cannot be created on columns defined with the following DATA TYPE ?
NOTE: This is objective type question, Please click question title for correct answer
...
Untyped XML in SQL Server ? Explain with an example
Untyped XML :
I have created one Table named : Table1
CREATE TABLE TABLE2
(XMLSample
XML)
GO
INSERT TABLE2 VALUES('TEST')
INSERT TABLE2 VALUES('123')
- The table should not allow any TYPE of data other than XML format with specific element
...
This is called 'UnTyped XML '
...
It should allow only INTEGER type of data along with some specific XML Element
...
I have to define one XML SCHEMA ( Type of the XML Element and Name of the Element, Etc
...
w3
...
2
...
It should allow only INTEGER type of
data along withDotnetfunda XML Element alone
...
Inserting data into the TABLE2
INSERT TABLE2 VALUES('TEST')
- It will throw an Err
Msg 6909, Level 16, State 1, Line 1
XML Validation: Text node is not allowed at this location, the type was defined with element only content or with simple content
...
INSERT TABLE2 VALUES('123')
- It will throw an Err
Msg 6909, Level 16, State 1, Line 1
XML Validation: Text node is not allowed at this location, the type was defined with element only content or with simple content
...
But Its not encloused with 'Dotnetfunda ' element
...
The term Backup Dump and Backup Device are the same and does the same activity
...
Then, why we are using the system stored procedure sp_addumpdevice to define a Backup device
...
The reason is SQL Server 6
...
DUMP LOG
...
So we still using the procedure(sp_addumpdevice) to define Backup Device for backward compatibility
To identify the Expiration date of database backup file
- Normally, when we take a database backup, we can specify that, How long the Database backup file(
...
- After that, The backup file(
...
- So, How to identity the Expiration date of a backup file(
...
Bak>'
- It will return a result set with 52 columns, we have a columns like ExpirationDate
...
- If the ExpirationDate column is some date then, The backup file no longer valid after the date specified
...
For example ,
Let us create a table :
CREATE TABLE ItemSales(
SalesPerson VARCHAR(50),
Item VARCHAR(50),
ItemAmount INT)
Insert values into it :
INSERT INTO ItemSales
VALUES('Person1', 'Pickles', $100
...
00)
INSERT INTO ItemSales
VALUES('Person2' ,'Oranges' ,$50
...
00)
INSERT INTO ItemSales
VALUES('Person2', 'Oranges', $300
...
00)
Now create and assigning a pivot element
SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
FROM
(SELECT SalesPerson, Item, ItemAmount
FROM ItemSales ) ps
PIVOT
(
SUM (ItemAmount)
FOR Item IN
( [Oranges], [Pickles])
) AS Pvt
After this the complete data is summarized and the report generated is in this format:
----------------------------------------------SalesPerson || Oranges || Pickles
----------------------------------------------Person1 || 500 || 200
Person2 || 350 || 25
What do you meant by an UnPivot element in sql server?
As the name implies, an UnPivot element is absolutely opposite to Pivot operation
...
That means one row of data for every column is to be unpivoted
...
Let us create a table first :
CREATE TABLE EMP(
EID INT,
ENAME VARCHAR(50),
JOB VARCHAR(50),
SAL INT
)
Insert values into it :
INSERT INTO EMP1
VALUES(1,'NARENDRA','MANAGER',10000)
INSERT INTO EMP1
VALUES(2,'SRIDHAR','ANYLYST',12000)
INSERT INTO EMP1
VALUES(3,'NAREN','CLERK',14000)
INSERT INTO EMP1
VALUES(4,'NARENDRA','MANAGER',11000)
INSERT INTO EMP1
VALUES(5,'SRIDHAR','ANYLYST',13000)
INSERT INTO EMPTABLE1
VALUES(6,'NAREN','CLERK',15000)
INSERT INTO EMP1
VALUES(7,'NARENDRA','MANAGER',12000)
INSERT INTO EMP1
VALUES(8,'SRIDHAR','ANYLYST',14000)
INSERT INTO EMP1
VALUES(9,'NAREN','CLERK',16000)
INSERT INTO EMP1
VALUES(10,'NARENDRA','MANAGER',13000)
INSERT INTO EMP1
VALUES(11,'ARAVIND','MANAGER',15000)
Now apply UnPivot property :
SELECT
FROM
EID , Property, Value
(SELECT EID,
CONVERT(sql_variant,EName) AS EName,
CONVERT(sql_variant,JOB) AS JOB,
CONVERT(sql_variant,SAL) AS SAL
FROM EMP1) EMP1
UNPIVOT (Value For Property In (EName, JOB, SAL)) as UPT
Now the final output will be in this format :
-----------------------------------------------EID || Property || Value
----------------------------------------------1 || EName || NARENDRA
1 || JOB || MANAGER
1 || SAL || 10000
...
...
In this way it goes and seperates all the records in the table
...
SQL Server uses AWE(Address Windowing Memory) to use Physical Memory over 4GB on 32 bit Operating system
...
AWE option supported in SQL Server Enterprise, Standard & Developer editions only
...
AWE option allowed only in 32 bit operating system
...
But this option is available in 64 bit operating system also, But its ignored, In futured version of service pack, The option will be removed
...
SSAS (Analysis Services) can not take advantage of AWE memory
...
FillFactor :When we create or modify an index, The percentage of free space allocated in the LEAF LEVEL of each index page during the
operation
...
It can catch the errors in business logic at the database level
...
It provides an alternative way to run scheduled tasks
...
It is very much useful when we use it to audit the change the data in a database table
...
Using SQL trigger,we don’t have to wait to run the scheduled tasks
...
...
What are the Disadvantages of using SQL Triggers ?
Disadvantages of Using SQL Triggers :
...
...
...
So it is difficult to figure out what happens at the
database layer
...
Triggers runs on every update made to the table therefore it adds more load to the database and cause the system to run slow
...
? The unique key constraints are used to enforce entity integrity as the primary key constraints
...
? The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table
...
What is a view?
? It can be thought of as a subset of a table
...
? Rows updated or deleted in the view are updated or deleted in the table the view was created
...
? The results of using a view are not permanently stored in the database
...
? Indices are created in an existing table to locate rows more quickly and efficiently
...
? The users cannot see the indexes; they are just used to speed up queries
...
? A table scan happens when there is no index available to help a query
...
? Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance
...
Location: /*:Dotnetfunda[1]
The data 'TEST' encloused with proper element, But Its not an INTEGER type
...
What is the Max length of varchar variable in SQL Server?
NOTE: This is objective type question, Please click question title for correct answer
...
The information returned by @@VERSION is similar to the product name, version, platform, and file data returned by the xp_msver stored
procedure, which provides more detailed information
...
While data processing, it issues locks on part of the table, or on the whole table
...
The maximum number of columns a table can have in SQL Server?
NOTE: This is objective type question, Please click question title for correct answer
...
But by default primary key creates a clustered
index on the column, where are unique creates a nonclustered index by default
...
Can UNIQUE KEY in SQL Server 2005 have two or more NULL?
NOTE: This is objective type question, Please click question title for correct answer
...
CHAR takes up 1 byte per character
3
...
Ex:
Declare test Char(100);
test="Test" Then "test" occupies 100 bytes first four bytes with values and rest with blank data
...
Variable length memory storage(Changeable)
2
...
varchar when the data entries in a column are expected to vary considerably in size
...
Ex:
Declare test VarChar(100);
test="Test" Then "test" occupies only 4+2=6 bytes
...
Conclusion:
1
...
When using the variable length data's in column like address use VarChar
Difference between Triggers and Stored procedures
Both are database objects containing blocks lof code that can be used for implementing business logic
The differences are:
1)Triggers fire automatically but they need events for that
...
They do not need create,alter,drop,insert,delete,update
...
2))we cannot pass parameters inside the triggers,
but we can pass parameters inside stored procedures
------------------example: if we want to display a message "error"
using a trigger: we need some DDL/DML Statement
using a procedure: NO DDL/DML is needed
Can we reset the identity column?
Yes, we can reset the identity column
use the dbcc checkident statement
example:
create table sales
(sno int identity(100,1),
sname varchar(10))
insert sales values('usb')
insert sales values('kbd')
select * from sales
sno sname
100 usb will be the output
...
dbcc checkident('sales',reseed,50)
the seed value will be reset to 50
What are derived Tables?
They are basically select statements in the from clause referred to by an
alias name
...
To calculate the maximun
salary from the salary columns of 2 tables
select max(salary) from (select salary from emp union select salary from emp1) a
a: is the derived table that will provide the input to the outer query
Which of these statements cannot be written inside the block Begin tran Commit Tran
NOTE: This is objective type question, Please click question title for correct answer
...
Identity column is auto incremented
2
...
Only one Identity column in table
4
...
Values cannot be updated
Primary Key:
1
...
2
...
3
...
4
...
Can be refer by other table as a foreign key
...
insert emptable values(100,'king',5000
...
77 500 200 5700
...
Difference between HAVING and WHERE Clause
Where Clause:
1
...
Where applies to each and single row
3
...
Where is used before GROUP BY clause
Ex:Using Condition for the data in the memory
...
Having is used only with the SELECT statement
...
Having applies to summarized rows (summarized with GROUP BY)
3
...
4
...
UNION only selects distinct values
2
...
UNION ALL selects all values (including duplicates)
...
Output is not in sorted order
EX:
SELECT Col
FROM @Table1
UNION
SELECT Col
FROM @Table2
/* Result of Union All operation */
SELECT Col
FROM @Table1
UNION ALL
SELECT Col
FROM @Table2
Output:
Union:
1
2
3
5
UnionAll:
1
2
3
2
5
What are all the SQL Server JOBs involved in LogShipping - SQL Server
There are four JOBs involved in Log Shipping
How Can we Write the Transaction Block in SQL Server?
BEGIN TRANSACTION
Statement 1
Statement 2
...
IF(@ERROR>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
Difference Between Truncate Table and Delete Table Command in SQL Server?
In case of Delete Statement Log is maintained
...
Which is the best method to get the single value from Database?
NOTE: This is objective type question, Please click question title for correct answer
...
What will be the output of the following code? Declare @Strings VARCHAR(20) Select @Strings ='Lakhan Pal Garg' Select
SUBSTRING(@Strings,-9,16
NOTE: This is objective type question, Please click question title for correct answer
...
Which Statement describe the COALESCE ?
NOTE: This is objective type question, Please click question title for correct answer
...
NOTE: This is objective type question, Please click question title for correct answer
...
How do you determine the maximum nested-level of Stored Procedure ?
The current nested level can be determine by : @@NESTLEVEL, The maximum nested level is 32
...
Creating stored procedure :
CREATE PROC PROC_SAMPLE1
AS
BEGIN
PRINT @@NESTLEVEL
EXEC PROC_SAMPLE1
END
)
BEGIN
PRINT 'Table contains one record
...
'
RETURN
END
ELSE
BEGIN
INSERT INTO dbo
...
2) valid for the current connection only
...
3)cannot be shared between multiple users
...
2)Available to all the connections once created
...
3)can be shared betwen multiple users
...
But some general issues that
you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures,
procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization,
excess usage of cursors and temporary tables
...
Can we use TOP with UPDATE and DELETE on partitioned views?
NOTE: This is objective type question, Please click question title for correct answer
...
Demonstrate or whiteboard how you would suggest using configuration files in packages
...
So
if you have 6 connection managers then you have 6 config files
...
If you have a single config file that stores all your connection managers then all your packages must have contain the connection managers
that are stored in that config file
...
2
...
When checkpoints are enabled on a package if the package fails it will save the point at which the package fails
...
The obvious benefit to this is if you load a million
record file just before the package fails you don’t have to load it again
...
Demonstrate or whiteboard using a loop in a package so each file in a directory with the
...
Before
demonstrating this tell which task/container accomplishes this and which enumerator will be used
...
Inside the Foreach Loop Editor you need to set a variable to store the
directory of the files that will be looped through
...
4
...
If transactions are enabled on your package and tasks then when the package fails it will rollback everything that occurred during the
package
...
Transactions must be enabled not only on the package level but also on each task you want included as part of the
transaction
...
How to find SQL SERVER SCRIPTING DUPLICATES ?
/* Create Table with 7 entries - 3 are duplicate entries */
CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRcordTable
SELECT 1, 1
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 2 --duplicate
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4
GO
/* It should give you 7 rows */
SELECT *
FROM DuplicateRcordTable
GO
/* Delete Duplicate records */
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO
/* It should give you Distinct 4 records */
SELECT *
FROM DuplicateRcordTable
GO
what is Schemabinding View
...
Example:
Suppose I have an table Employee(EmpID,EmpName, DOJ,Managerid,DepartID)
Now am Creating a view
Create View EmployeeDetails
with Schemabinding
as
Select EmPid, EmpName,DOj,ManagerId,DepartID from Employee
after it just try to execute delete table and alter table and delete column of employee table
...
Because you are having Schema dependency
...
If you are using Normal View
...
How many Foreign key can i have in my MS sql table ?
A Maximum of 253 Foreign Keys we can have in for a single table
...
* From '
While (@I <=299)
Begin
if (@I >1)
Select @Script = @Script + ' Join Table' + CAST(@I+1 as varchar) + ' A' + CAST(@I+1 as varchar) + ' On (' + 'A' + CAST(@I+1
as varchar) + '
...
Id)'
else
Select @Script = @Script + 'Table' + CAST(@I as varchar) + ' A' + CAST(@I as varchar) + ' Join Table' + CAST(@I +1 as
varchar) + ' A' + CAST(@I +1 as varchar) + ' On (' + 'A' + CAST(@I as varchar) + '
...
Id)'
Select @I = @I + 1
End
EXEC(@Script)
Go
The script will confirm the limitation of using tables in a SELECT statement
...
This
function also provides a way to replace a value with the null if the result is true
...
If it is a null, it replaces it with '' string
...
sql statement which returns all columns of table with no row(assuming that table has more than 3000 row)
...
What does this return ? declare @adress varchar = 'India' select @adress
NOTE: This is objective type question, Please click question title for correct answer
...
Non deterministic functions may return different results each time they are called with a specific set of input values even if the
database state that they access remains the same
...
USER_NAME, IDENTITY
Can you set firing order in triggers and in instead of triggers?
sp_settriggerorder [triggername] first / last
example :
2
...
Result :
1
2
3
...
32
Msg 217, Level 16, State 1, Procedure PROC_SAMPLE1, Line 5
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
...
Which of the following is a fixed length DataType?
NOTE: This is objective type question, Please click question title for correct answer
...
NOTE: This is objective type question, Please click question title for correct answer
...
Primary key creates cluster index on the column
...
So is it possible to have cluster index on Unique key column?
The answer is YES
...
You cannot create an Index on a column of the following data type
NOTE: This is objective type question, Please click question title for correct answer
...
How to get a row was inserted most recently in a table?
select top 1 * from tablename order by ColumnName desc
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
What is Row_Number()?
ROW_NUMBER() returns a column as an expression that contain's the row number within the result set
...
From @t
UNION ALL
Select ID
, RevStr + RIGHT( RemStr, 1 )
, Left( RemStr, LEN(RemStr)-1 )
From cteReverseRecur
Where RemStr > '')
SELECT ID, RevStr as data
From cteReverseRecur
Where RemStr = '';
What is the purpose of "sp_resetstatus" system stored procedure ?
This system stored procedure is used to reset the database status from SUSPECT to normal
...
You should be under 'sysadmin' server role
...
Should not be under Transaction
...
e: The procedure 'sp_resetstatus' cannot be executed within a transaction
...
Database name should be valid and available
i
...
Supply a valid database name
...
databases
4
...
It should be a source database
...
e: Cannot run sp_resetstatus against a database snapshot
...
The database should be already in SUSPECT mode
...
e: The suspect flag on the database "
...
1
...
e: SQLFunda\SQLBuddy
(or)
2
...
e: (Local)\SQLBuddy
(or)
3
...
e: Localhost\SQLBuddy
(or)
4
...
e:
...
If the Server Name : SQLFunda
If the Instance Name : SQLBuddy
But, we can force the connection either Named Pipe or TCP/IP
Forcing TCP/IP:
i
...
e: np:SQLFunda\SQLBuddy
Once connected
...
session_id [Session ID],
e
...
dm_exec_sessions s join sys
...
endpoint_id = e
...
Thanks and Regards
Akiii
Once we switched from SIMPLE Recovery model to FULL or BULK_LOGGED recovery model, what are all the recommendations ?
1
...
2
...
till reaches the physical free space
...
Exp : EmpID, EmailID, SSN in Employee table and project ID in project table
...
Candidate Key:
All keys in a table that become unique called as candidate key
...
Alternate Key:
Among of candidate keys if any single key or combination of keys made as primary key then rest candidate key called as alternate key
...
Delete duplicate rows on table with/without primary key in Sql Server 2005
Here below query that have multiple repeated records, my aim to delete duplicate records and keep one record for each unique row
...
Empname = ee
...
Empname = ee
...
When we are transferring records from one database to another database so now if we use SqlBulkCopy class to transfer the records then
which scenario we need to handle ?
NOTE: This is objective type question, Please click question title for correct answer
...
Each letter in the acronym corresponds to each database operations
...
dotnetspider
...
aspx
Do stored procedures perform better than SQL queries (or embedded SQL)?
Its a myth that the stored procedure perform better than SQL queries
...
0 where in stored
procedures were a way to partially precompile an execution plan in SQL Server version 6
...
Such partially compiled execution
plan created at the the time the stored procedure was created, were stored in a system table
...
http://msdn
...
com/en-us/library/aa174792(v=sql
...
aspx
Important anecdote from this article is as follows
"SQL Server 2000 and SQL Server version 7
...
SQL Server 2000 and SQL Server 7
...
"
How many types of indexes are available in SQL Server?
There are four type's of index's available in Sql Server
...
When we create primary key then cluster index create automatically
...
when create
unique key then non cluster index create automatically
...
Composite Key: when we create index with two columns together then composite key index create
...
In SQL Server Database, what is the basic difference between a table scan and an index scan ?
Table Scan --- Here, row by row scanning is done to get the data
...
Index Scan --- Here in the first, index is created in the table
...
It increases the
performance
...
BCP does not
copy the structure same as source to destination
...
In previous version we had to write
separate statement to INSERT,DELETE and UPDATE data based on certain conditions, but now using MERGE statement we can include the
logic of such data modification in one statement that even checks when the data matched then just update it and when unmatched then
insert it
...
Write a sql query which will give result of the city name of the employee whose avg salary > 2000
select cityname, avg(salary) as avgsal from Employee group by cityname having avg(salary) >2000
Find and Delete duplicate records in a table
Many times you can face problem of duplicate records in table
...
SELECT [FirstName] FROM tblTest GROUP BY [FirstName] HAVING COUNT(*) > 1
Then Delete duplicate records
...
Are all the above statements
correct ?
NOTE: This is objective type question, Please click question title for correct answer
...
What is the command that is used to set a set of privileges that can be granted to users or different roles?
NOTE: This is objective type question, Please click question title for correct answer
...
Global temporary tables (created with a double “##”) are visible to all sessions
...
Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it
...
And
if @@Recordcount is checked before the error-checking statement then @@Error would get reset
...
SELECT @RC = @@ROWCOUNT, @ER = @@ERROR
What is a table called, if it has no Cluster Index?
Unindexed table or Heap
...
Name’, ‘NameChange’ , ‘COLUMN’
Can we use Truncate command on a table which is referenced by FOREIGN KEY?
No
...
Can we use NEWID(), or RAND() in function?
No
Can we use "Print" statement in function?
No
Which of the following queries generates an error when executed? DECLARE @x VARCHAR(10), @y VARCHAR(10) SET @y = '' SELECT @x/0 -query 1 SELECT @x/2 -- query 2 SELECT @y/0 -- query 3 SELECT @y/2 -- query 4
Answer: 3
Explanation: Queries 1 and 2 return NULL since @x is undefined
...
Query 3
returns a divide by 0 error
...
The SELECT statement is a Data Manipulation Language commend, not a Data Definition Language command
...
Name the method used in SQL Server to Read XML Data?
TRUNCATE TABLE employee;
Unlike using DELETE, we can restore the data, as the physical data will not get deleted
...
It is similar to a derived table
...
It can be recursive and non-recursive
...
CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views
...
Usually used with the DML triggers
...
Primarily used to perform certain action like
1) Extend referential integrity between tables
2) Test for errors and take action based on the error
...
What is the use of UPDATE_STATISTICS command ?
UPDATE_STATISTICS updates the indexes on the tables accordingly
...
What is SQL Profiler ?
SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server
...
You can use SQL Profiler to monitor only the events in which you are interested
...
What is SQL Server Agent ?
It plays an important role in the tasks of Database Administrator(DBA)
...
Using its full- function scheduling engine, you can schedule your own jobs and scripts
...
What is join? How many types of joins?
Joins are used to extract data from multiples tables on specified some conditions
...
INNER JOIN: extract only matched data from two tables
...
RIGHT OUTER JOIN: extract all data from right table and matched data from left table, having null against unmatched records
...
Even there are more joins like cross join, non-equi joins but they are in less use
...
w3schools
...
asp
Difference between procedure and user defined function
...
2) procedure may or may not return a value where as function must return a value
...
4) you can use function in select, where or in case statement but procedures can not be used
...
What is linked server?
One sql server is added to another sql server, this concept is called linked server
...
Example:
select tab1
...
db1
...
table1 tab1,
dbserver1
...
dbo
...
id=tab2
...
It does not exist in physical memory
...
View can be applied more than one table
also
...
Types of 'tables' SQL Server
...
Temporary tables:
Temporary tables are divided into two types,
-Local (Its visible only to their creators)
-Global (visible to any user and any connection)
System tables:
The configuration of the server and all its tables in a special set of tables known as system tables
...
-Wide tables can define up to 30,000 columns
...
-When data is added and removed from a wide table, performance can be affected
...
, of columns in Wide Table
...
This increases the cost to maintain indexes on the table
...
Which SQL statement is used to extract data from a database?
NOTE: This is objective type question, Please click question title for correct answer
...
????
NO Null is NOT Treated as Zero
...
So,A NULL value is treated as a blank
Columns per foreign key in SQL Server 2000
NOTE: This is objective type question, Please click question title for correct answer
...
Maximun Bytes per GROUP BY numbers SQL Server
NOTE: This is objective type question, Please click question title for correct answer
...
What is the Database size of SQL Server Database Engine object?
NOTE: This is objective type question, Please click question title for correct answer
...
What is the syntax to create a table?
create table tabName
(column1 number(5) primary key,
column2 varchar2,
column3 varchar2 default = 'Value');
What is syntax to execute a stored procedure?
using following syntax
...
Execute sp_name 'param1', 'param2';
Syantax to create a stored procedure
...
Create or Replace Procedure sp_proc_name
(param1 varchar2(20), param2 varchar2(20))
as
begin
select * from tableName
end;
What is Coalesce?
Coalesce is a function tat returns first non-null value within the given list of values
...
What is the difference between primary key and unique key?
Difference between Primary Key and Unique Key
1) Primary key creates a clustered index on column whereas unique key creates a non-clustered index on column
...
Unique key can have two null values?
No, unique key can have only one null value because unique key enforces uniqueness of the column
...
What is check constraint?
It is used to enforce domain integrity
...
(1) OPENXML in SQL Server 2000
For SQL Server 2005:
(2) query()
(3) value()
(4) nodes()
(5) exists()
(6) Modify()
How we can Modify XMl Data in SQL Server 2005?
Using modify method we cam modify the data in XML
...
modify('replace value of (/root/item/@value)[1] with sql:variable("@value")')
select @xml
How to Insert XML Data into Existing XML Node?
declare @xml xml
set @xml = '
select @xml
declare @value varchar(10)
set @value = 'val1'
set @xml
...
How we can get the List of System Tables in DataBase?
select * from Sys
...
Objects where Type='u'
How We can Get List of Store Procedures?
select * from Sys
...
Objects where Type='fn'
Query to Get List of Table Valued Functions?
select * from Sys
...
Objects where Type='tr'
Query To Get The Column Name,DataType, And Length of columns in a Tables?
select column_name, data_type, character_maximum_length from information_schema
...
Create table #
(
ID int,
NAME varchar(50)
)
You will get the following error
...
The maximum length is 116 characters
...
It is not advisable to encrypt a stored procedure because there is no way to decrypt your procedure, it is one way call only
...
How Many Parameters per stored procedure in Sql Server?
NOTE: This is objective type question, Please click question title for correct answer
...
What is sp_config command?
sp_config is a system stored procedure which is used to displays or changes global configuration settings for the current server
...
[Employee](
[Id] [int] NOT NULL PRIMARY KEY,
[Name] [varchar](50) NULL,
[Age] [int] NOT NULL,
[Photo] [image] NULL,
[Salary] [numeric](10, 2) NULL,
)
INSERT INTO Employee values (101,'James Clerk',29,NULL,1000
...
00);
INSERT INTO Employee values (103,'Matt Mcnair',35,NULL,5000
...
00);
INSERT INTO Employee values (105,'Jeff Yeary',32,NULL,1000
...
How to get the nth row value of a table
...
For the above question I have found the below solution
...
CREATE TABLE [dbo]
...
00);
INSERT INTO Employee values (102,'Steve Proell',40,NULL,60000
...
00);
INSERT INTO Employee values (104,'Amit Kr',29,NULL,1000
...
00);
# Solution 1
select * from employee where id in (
select MAX(Id) from Employee
where Id in (select top(3) ID from Employee ))
# Solution 2
select top 1 *
from employee
where Id in (select top 3 Id from employee order by Id asc)
order by Id desc
# Solution 3
SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RowNum, * FROM Employee) sub
WHERE RowNum = 3
Please suggest if any other solution are there for the above problem
Is there a way to decrypt stored procedure?
No, there is no way to decrypt a stored procedure, once you have encrypted your stored procedure, you can not get your code
...
What is Collation ?
Collation refers to a set of rules that determine how data is sorted and compared
...
What is Identity?
Identity is column that automatically generates numeric values, it is increamented by 1 by default but it can be set also
...
Structured Query Language (SQL), pronounced "sequel", is a language that provides an interface to relational database systems
...
What is diffrence between Co-related sub query and nested sub query ?
Correlated subquery runs once for each row selected by the outer query
...
Example:
select e1
...
basicsal, e1
...
basicsal = (select max(basicsal) from emp e2 where e2
...
deptno)
Nested subquery runs only once for the entire nesting (outer) query
...
Example:
select empname, basicsal, deptno from emp
where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)
What Operator performs Pattern Matching ?
Pattern matching operator is LIKE and it is used with two attributes:
1
...
_ ( underscore ) - means mathing exactly one character
How can i hide a particular table name of our schema ?
You can hide the table name of your schema by creating synonyms
...
Normalization means to refining the redundant and maintain the stablization
...
What are Data Marts ?
Data Warehousing is a process in which the data is stored and accessed from central location
...
For example your company has lot of branches which are spanned across the globe
...
So to achieve this IT department can setup data mart in all branch offices and a central data
warehouse where all data will finally reside
...
Star schema is good when you do not have big tables in data warehousing
...
When you denormalize star schema it is nothing but snow flake design
...
views
Query to display List of All Databases in SQL Server 2005/2008?
SELECT * FROM Sys
...
How we can add Description to the Column using Sql Command?
We can Add Description to Column using sp_addextendedproperty System Store Procedure
...
sp_addextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' ,
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName',
@level2type=N'COLUMN',@level2name=N'ColumnName'
Thanks & Regards
Lakhan Pal Garg
How To Update Description Value for a Column in Table using SQL Command?
We can Update Description to Column using sp_updateextendedproperty System Store Procedure
...
sp_updateextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' ,
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName',
@level2type=N'COLUMN',@level2name=N'ColumnName'
Thanks & Regards
Lakhan Pal Garg
How To Delete Description Value for a Column in Table using SQL Command?
We can Delete Description from Column using sp_dropextendedproperty System Store Procedure
...
sp_dropextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' ,
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName',
@level2type=N'COLUMN',@level2name=N'ColumnName'
After add SET ROWCOUNT
SET ROWCOUNT 5
SELECT * FROM EMPLOYEES
- It will return 5 rows of records instead of 10 row
...
Example:
SELECT LastName, SUBSTRING(FirstName, 1, 1) AS Initial
FROM Person
...
What port do you need to open on your server firewall to enable named pipes connections ?
To enable named pipes connections, you need to open Port 445 on your server firewall
...
Define Candidate Key ?
A candidate key is a key which can identify each row of a table uniquely
...
If
the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys
...
Any of
those combinations of columns could be used to uniquely identify a row in the table, and all of them are composite keys
...
Every lock is a memory structure
...
To prevent this from happening, SQL Server escalates many fine-grain locks to fewer coarse-grain locks
...
5, but from SQL Server 7
...
What is RAID ?
RAID stands for Redundant Array of Inexpensive Disks
...
What is meant by Blocking ?
Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type
...
How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?
SQL Server can be started from command line, using the SQLSERVR
...
This EXE has important parameters with which a DBA should be familiar with
...
Explain about reporting services of MYSQL ?
This forms the report generating environment whereby a report is generated by the data generated by the client and server
...
Reports are created in RDL format
...
What is the difference between weak entity set & strong entity set ?
Weak Entity Set:
An entity set which does not possess sufficient attributes to form a primary key is known as a weak entity set
...
Example:
Set of all Persons,Companies,Trees,Holidays
What is Normalization?
Normalization
Normalization is the process of simplifying the relationship between data elements in a record
...
F is achieved when all repeating groups are removed, and P
...
big table is broken into many
small tables, such that each table has a primary key
...
I
...
The columns in a table which is not completely
dependent on the primary key are taken to a separate table
...
K’s
...
e
...
Ie If A is the primary
key in a table
...
Suppose C depends only on B and B depends on A
...
So remove C from the table to a look up table
...
declare @start_date DATETIME
SET @start_date=dateadd(week, datediff(week, 0, getdate()), 0) ;
-- Get the last date of the last week
declare @end_date DATETIME
SET @end_date=dateadd(week, datediff(week, 0, getdate()), 6) ;
select
CONVERT(VARCHAR(10),@start_date,105) 'start day date of the week',
CONVERT(VARCHAR(10),@end_date,105) 'end day date of the week'
CONVERT(VARCHAR(10),@start_date,105) : This is used for removing the time part from the DateTime data type
...
It can used along select,delete and update
statement
...
It can only used with select
statement
...
Example:
Let us say you want to query database with LIKE for all employees whose name starts with La
...
What is @@rowcount ?
@@rowcount gives the number of rows given as a result of previous query ran
...
2) OUTER JOIN(LEFT OUTER JOIN,RIGHT OUTER JOIN)
Left outer join: Taking Left side of the values checking in to Right side Taking Right side values checking with left side call right outer join
...
4) CROSS JOIN
Cross join is an Cartesian Product
...
5) SELF JOIN
join within the table is called self join
...
TRIGGER IS AN FIRED WHEN AN INSERT,UPDATE ,DELETE STATEMENTS
...
VIEW IT WONT OCCUPY ANY MEMORY,VIEW IS AN IMAGINARY TABLE
...
The inner query will display the salary which is less than the largest salary
...
If we can execute multiple sql statements inside a stored procedure
...
fn_listextendedproperty
...
fn_listextendedproperty
...
Command To Get Description Data for Single Columns:
SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA',
'dbo', 'TABLE', 'Table Name Here', 'COLUMN', 'Column Name Here')
Thanks & Regards
Lakhan Pal Garg
How we can Create a
...
sql file for the StoreProcedure
...
sql
Thanks & Regards
Lakhan Pal Garg
How We can get the DB name using SQL Command?
Following is the Command to get the DB name using Command analyzer
SELECT DB_NAME()
Thanks & Regards
Lakhan Pal Garg
What is the use of Set NOCOUNT ON;?
By Default When we execute any command it return us the number of record affected
...
There are two types of Primary Keys:
1) Simple Primary Key ==> Defining primary key on a single column is called Simple Primary Key
...
What is Unique Key in SQL Server?
A Unique Key in a table uniquely identifies each and every row and allowing Nulls per column Combination
...
2) Composite Unique Key ==> Defining Unique Key on more than one column is called Composite Unique Key
...
2)By definition you can have only one primary key defined on a table where as you can have multiple unique keys defined on a table
3)Also by default Primary key is created as clustered index and unique key is created as non clustered index
...
Once constraint is defined,
insert or update to the data within the tables is checked against the defined constraint
...
Check constraint is associated with columns in a Table
...
Rules are defined with in a database and can be applied to any number of columns
...
TABLE_CONSTRAINTS, where CONSTRAINT_TYPE column stores the information of Constraint Type
...
Unique Key ==> Is also used to uniquely identify records but does allow NULL Values per column combination
...
This is not a constraint
...
How to disable and Enable the constraints?
You need to use ALTER TABLE statement to disable constraint
...
First you need to drop all the Foreign Key relationships and then only you can drop Parent Table
...
Etc
...
VARBINARY data type is the recommended one to hold an Object
Create Table LoadImage
(
ImageContent
VARBINARY(MAX)
)
Go
Loading Image into a Table
INSERT LoadImage
SELECT * FROM OPENROWSET(BULK N'D:\Pandian
...
JPG', SINGLE_BLOB) AS [Image]
Go
Define Stored Procedure with Example?
Stored Procedure is an collection SQL Statements
...
Stored Procedure is an Precompiled Execution
...
Stored Procedure Can reduce the Client /Server Network Traffic
...
In This example am using Adventure works DataBase
SELECT * FROM AdventureWorks
...
Address
CREATE PROCEDURE GetAddress
AS
SELECT * FROM AdventureWorks
...
Address
GO
EXEC GetAddress
--or just simply
uspGetAddress
To Create a Stored Procedure u can use Create Procedure or Create Proc
Just like you have the ability to use parameters with your SQL code you can also setup your stored procedures to except one or more
parameter values
...
Person
...
Person
...
Person
...
How will you copy the structure of a table without copying the data ?
By using UNIVERSAL FASLE condition like 1=2,A=B
...
This is the only method for copying the structure of the table without copying the data
...
The Profiler uses the location specified for the TEMP system variable
...
It states about the management features of SQL server
...
Scripts using this command tool are stored as
...
What is DESCRIBE command in SQL Server ?
DESCRIBE command is used in Oracle
...
In SQL Server, we use sp_help fpr the same purpose
...
Read Committed is the default SQL Server
isolation level
...
Example:
CREATE INDEX myIndex ON myTable (myColumn)
What is Lock Escalation ?
Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks)
...
Too many locks would mean, more memory being occupied by locks
...
Lock escalation threshold was definable in SQL Server 6
...
0 onwards it is dynamically managed by SQL Server
...
Each
process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated
...
SQL_Modules table contains the script of the Store Procedure and Sys
...
Thanks & Regards
Lakhan Pal Garg
How we can implement Row lock explicitly in SQL Server?
Using "With (ROWLOCK)" we can implement Row Lock Explicitly
...
Thamks & Regards
Lakhan Pal Garg
What is the name of the table from which we can get the Locking information in SQL Server?
sys
...
Can we use shared lock with Update Statement?
No We can't use the shared lock to Update or modifty data
...
e
...
mdf,
...
master_files table we can find the master file information for mdf file file_id is 1 and for ldf its value is 2
...
configurations
What is the Name of table that contains available data type in SQL Server?
sys
...
What is the use of WAITFOR in SQL SERVER ?
Suppose we want to execute one query now one one after some time then we need to use WAITFOR TIME for this
...
What is the name of the Store procedure to get the listing of current users?
sp_who
Maximum number of parameters you can pass in a Store procedure in SQL Server 2005?
2100
...
You can increase the delay according to the size of your transactions
...
Now Execute the Transaction A and Transaction B at the same time
...
What is a live lock ?
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering
...
A livelock also occurs when read transactions
monopolize a table or page, forcing a write transaction to wait indefinitely
...
Theoretically, 3 and 4 could execute (there's only a shared lock),
but queuing makes 3 and 4 to wait
...
Which is default sorting option in SQL ORDER BY clause?
NOTE: This is objective type question, Please click question title for correct answer
...
Which of the following SQL statements is correct?
NOTE: This is objective type question, Please click question title for correct answer
...
Explain Composite Index in SQL Server
...
Such index that consists of more than one columns is referred as composite
index
...
The order of the columns are very important here
...
col16);
For example
...
It can do a wide range of activities, including running T-SQL scripts and ActiveX scripts, Integration Services packages, Analysis Services
commands and queries, or Replication tasks
...
A job can be edited only by its owner or members of the sysadmin role
Difference between Database Mail and SQLMail ?
Database mail :
Based on SMTP (Simple Mail Transfer Protocol)
...
No need to install Outlook
...
More secure than Sqlmail
...
Used prior versions of Sql server 2005
...
Leass secure than Database mail
...
CASE
Evaluates a list of conditions and returns one result
...
Simple CASE
The simple CASE expression compares an expression to a set of simple expressions to determine the result
...
Searched CASE
The searched CASE expression evaluates a set of Boolean expressions to determine the result
...
--Simple CASE expression:
CASE input_expression
WHEN when_expression THEN result_expression [
...
n ]
[ ELSE else_result_expression ]
END
Examples:
--Simple CASE expression:
SELECT empID,empname,CASE gender
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
ELSE 'NA'
END
FROM employee
--Searched CASE expression:
SELECT empID,empname,
CASE
WHEN gender= 'M' THEN 'Male'
WHEN gender= 'F' THEN 'Female'
END
FROM employee
What is SQL Server Express LocalDB ?
It is a new feature introduce in sql server 2012
...
NET Framework integration,
spatial types and others that are not available in SQL Server Compact
...
Write A Query : We have a table EmpMaster Which have two column Name varchar(50), Gender char(1)
...
Means Where Gender Is 'M' Update With 'F' and 'F' Update with 'M'
--Create Table
CREATE TABLE EMPMaster(
[EMP_Name] [varchar](50) ,
Gender Char(1)
)
--Insert Record
Insert INTO EMPMaster Values('AA','M')
Insert INTO EMPMaster Values('BB','M')
Insert INTO EMPMaster Values('CC','F')
Insert INTO EMPMaster Values('DD','M')
Insert INTO EMPMaster Values('EE','F')
Insert INTO EMPMaster Values('FF','F')
--Update Query
Update EMPMaster SET Gender=Case When Gender='M' Then 'F'
Else 'M' END
What is Co-Related subquery?
The Subquery is one which Produces output based on the inner query values
...
E-g
SELECT e
...
Employee e
WHERE e
...
ContactID
FROM Employee_Person
...
ModifiedDate) = MONTH(e
...
They can be defined with create, alter, drop and other DDL statements
...
Building and maintaining logical
relationships between tables is essential part to work with relational databases and when such relationships exist, we say that the data has
referential integrity
...
We can enforce such referential integrity through foreign key constraints
...
id
);
GO
What maximum size of row is allowed in SQL Server ?
NOTE: This is objective type question, Please click question title for correct answer
...
system_sql_modules
What is Concat function in Denali? Explain with example
...
In earlier versions of Sql Server we have the option of performing concatenation using the '+'
symbol
...
However, the new Concat() function takes care of this explicit conversion
...
g
...
This function is use to format the value
...
g
...
2012/07/14
*/
What is EOMonth function in Denali? Explain with example
...
e
...
Select LastDayOfcurrentMonth = CONVERT(varchar(10),EOMonth(GETDATE()),110)
/*
LastDayOfcurrentMonth
--------------------------------07-31-2012
*/
What are the advantages of Sparse Column?
- Eliminate the limit of maximum allowed column in SQL Server 2008 per table which is 1024
...
So we can have 1024 + 100,000 columns
...
- Sparse columns works well with filtered indexes because we create index for dealing with the non-empty attributes in the column
...
What are the limitations of Sparse Column?
a) Microsoft recommends to use sparse columkn if there is a need to save space by at least 20 to 40%
...
c) Sparse Columns cannot include Default value
...
e) I cannot be part of a clustered index
...
What is the difference between Inline Table Valued Function and Views?
A few key differences is listed here
a) View can be materialized (indexed view) and hence performs better
...
b) Views can have triggers since they can be used to change underlying tables (INSTEAD OF triggers) but not Inline Table Valued functions
...
d) Views don't accept parameter but Inline Table Valued function does so
...
This function is used to
return a single string part of a date/time
...
Now, given any month number, we will first construct the first day of the month as under
CAST('10' + '/1/1900' AS DATETIME)
where 10 is month number
So, since we have now constructed the date, now we can easily apply the DATEPART function to obtain the month number
DECLARE @MonthNumber INT = 10
SELECT [Month Name] = DATENAME(MONTH,CAST('10' + '/1/1900' AS DATETIME))
/* Result */
Month Name
---------October
How will you get month number if month name is given?
We can use the DATEPART function for accomplishing the task
...
The general syntax is :
DATEPART(datepart,date)
So if we specify the datepart as month, we will get the month component from this function
...
g
...
Now, given any month, we will first construct the first day of the month as under
Declare @monthname Varchar(20) = 'October'
Select CompleteDate = CAST(@monthname + ' 1, 1900' AS DATETIME)
/* Output */
CompleteDate
---------------------1900-10-01 00:00:00
...
A simple example is to find-out the manger of an employee from the employee table that contains both employee id an manger id
...
Let us see the code
1
...
Inserting the value
Thanks & Regards
Lakhan Pal Garg
Write a Query in SQL Server to get the Parameter list of given Store Procedure
...
SELECT * FROM sys
...
Objects O ON O
...
object_id WHERE O
...
sp_lock
Which system table holds the details of all the processes running on the Microsoft sql server?
The name of the system table is sysprocesses
...
How can we find the open transactions details in sql server?
Yes, it is possible
...
What is the name of command in sql server 2005 which is used to kill any process?
The command name is kill
...
What is the command name to shrink the data file and log file size in SQL server 2005?
The command name is : DBCC SHRINKDATABASE (Database Name)
This command will shrink the data file and log file size
...
dbcc shrinkdatabase (TempDAabase,10)
This command will free only 10% space
...
You can use them to manipulate a tablevalued expression into another table
...
UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into
column values
...
[Old_Column_Name]' , '[New_Column_Name]', 'COLUMN'
Renaming any object (table, sp etc) :
sp_RENAME '[Old_Table_Name]' , '[New_Table_Name]'
Example
sp_rename 'employee
...
Example for finding an Orphan:
An orphan record exists when there is a Contact2 record but NOT a Contact1 record
SELECT * FROM Contact2 WHERE AccountNo NOT IN (SELECT AccountNo from Contact1)
Explain the differences between CAST and CONVERT ?
Both are used for the same purpose
...
The major differences are:
a) CAST cannot allows you to specify the format of the result which you wants to convert, whereas CONVERT allows it
...
c) CONVERT can be used to format dates as strings, whereas CAST cannot
...
Product
WHERE CAST(ListPrice AS int) LIKE '3%';
GO
Usage of CONVERT:
USE Sample
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production
...
While the user is in session, he can use any of the SQL commands
...
Each SQL session is associated with a user identifier and role name
...
By deleting a specified number of characters from 1st string expression and replacing them with 2nd string expression, a string will be
formed
...
Important note is that, for deletion purpose, you have to specify the starting position and the length of the string
...
If any modifications are done to the row of the table, this BINARY CHECKSUM function identifies them which takes case-sensitivity into
account
...
This CHECKSUM_AGG function will returns a value to evaluate whether the changes are happened
...
The datatype for this function will be either an integer datatype or BINARY_CHECKSUM function result
...
The main advantage of this operator is that, it takes a normalized table into consideration and will convert it into a new table in which the
values of the columns are derived from the original table values
...
So let’s fill it with some data
...
With the insertion and deletion of data,
tables named “INSERTED” and “DELETED” gets created in SQL Server which contains modified/deleted data
...
What is difference between RANK and DENSE_RANK
...
For an example, if you have three records at position one
...
RANK() will rank the next record as fourth record while DENSE_RANK() will rank it as
second
...
You can create only one clustered index in table
...
Suppose, you have created table with following:
CREATE TABLE [dbo]
...
Table1
GO
CREATE UNIQUE CLUSTERED INDEX idx_viewClustered ON dbo
...
When it is set to ON, the concatenation will result null and when it is set to OFF, it will result the string
...
This SCOPE_IDENTITY function is used to return a value which is the last generated identity in the current scope
...
This scope can be a stored procedure or a module or a function or a batch
...
[dbo]
...
Unlike SCOPE_IDENTITY function, this @@IDENTITY function will returns the value which is the last generated identity in the current
session
...
An important point to note is that, if at a time, two users using two different connections connected to SQL server inserts two rows with
identity column in a table, then each of them will get the value they have just inserted
...
CREATE TABLE [dbo]
...
[DimUser1]
(
[userId1] int IDENTITY(1,1) ,
[userName1] varchar(100) NULL
)
GO
Let’s create a SP named “sp_InsertData” as:
CREATE PROCEDURE sp_InsertData
AS
BEGIN
INSERT INTO [dbo]
...
[DimUser1] is another table created similar to [dbo]
...
Now, let’s execute our sample query:
INSERT INTO [dbo]
...
This COALESCE function is used to return a value which is the first non-null expression among all its arguments
...
It is also used to display any other value instead of NULL value in the result
...
This function will let the user know whether he has the effective permission on a securable(Ex:Table)
...
If the user wants to know whether he has the permission for SELECT on the customer's table, he can use the below query:
select Has_perms_by_name('Customers', 'Objects', 'SELECT')
It will return either 1(true) or 0(false)
...
' + QUOTENAME(name),
'OBJECT', 'SELECT') As have_select, name FROM sys
...
The authorization process can be scoped to the database
...
What is the purpose of sys
...
It also provides
information like
a) CPU Count: Number of logical CPUs in the server
b) Hyperthread-ratio: Ratio of logical and physical CPUs
c) Physical_memory_in_bytes: Amount of physical memory available
d) Virtual_memory_in_bytes: Amount of virtual memory available
e) Bpool_commited: Committed physical memory in buffer pool
f) OS_Priority_class: Priority class for SQL Server process
g) Max_workers_thread: Maximum number of workers which can be created
What is the purpose of sys
...
It also provides information like
a)Name: Name of the host registered
b)Type: Type of hosted component [SQL Native Interface/OLE DB/MSDART]
c)Active_tasks_count: Number active tasks host placed
d)Active_ios_count: I/O requests from host waiting
What is the purpose of sys
...
The number of runnable tasks is generally a nonzero
value that
indicates that tasks have to wait for their time slice to run
...
Write a query that will list all the available schedulers in the SQL Server machine and the number of runnable tasks for each scheduler
...
dm_os_schedulers
Result
scheduler_id
current_tasks_count
0
8
0
1
12
0
1048578 1
0
1048576 2
0
1048579 1
0
1048580 1
0
1048581 1
0
1048582 1
0
1048583 1
runnable_tasks_count
0
What is the purpose of sys
...
It provides the below information
a)Io_type: Type of pending I/O request
b)Io_pending: Indicates whether the I/O request is pending or has been completed by Windows
c)Scheduler_address: Scheduler on which this I/O request was issued
What is the purpose of sys
...
Internally, SQL Server has a framework that monitors different memory pressures
...
This notification is used internally by the components to adjust their memory usage according to the
memory state
...
When an index is created or rebuilt, the Fill Factor value determines the percentage of space on each leaf-level page to be filled with data,
reserving the remainder on each page as free space for future growth
...
Fill Factor is always an integer valued from 1 to 100
...
Specifying a Fill Factor value of 70 would implies that 30
percent of each page will be left empty, providing space for index expansion as data is added to the underlying table
...
The Fill Factor option is designed for improving index performance and data storage
...
FROM
print 'testing' + NULL; -- results testing and prints the same
This is used to terminate a query when an overflow or divide-by-zero error occurs during query execution…
NOTE: This is objective type question, Please click question title for correct answer
...
SET OFFSETS specified settings are in effect at…
NOTE: This is objective type question, Please click question title for correct answer
...
Return type of RAND() is
...
Write and justify output of - select nullif('test','test')
The output will be – NULL
Justificatio n : nullif returns NULl if given two arguments are same else returns the first specified argument
...
How to get date of 234 days back in SQL?
SQL :
SELECT DATEADD(dd,-234,getdate())
What is SIGN() ?
SIGN() is used to determine whether the specified is positive, negative or zero
...
print sign(0) -- prints 0
print sign(10) -- prints 1
print sign(-10) -- prints -1
Sometimes before using SIGN(), you need to confirm you are passing numeric value as argument
...
Which of the following is a Large object Binary Data Type?
NOTE: This is objective type question, Please click question title for correct answer
...
INNER JOIN cannot be used without the
It is the central part of DQS client application
...
As they capture the organizational knowledge, so they are term as knowledge base
...
The KB contains Domains which is a component of Data Quality
...
Now we can have this domain in our knowledge base say
CountryKB
...
Domains can be either single valued or compound (consists of multiple fields)
...
csv file
A Data quality project can be created either as a cleansing project or a matching project to perform respective activities and can perform the
operation on the same KB
...
Matching is that KB application that performs matching activity based on matching policy in a knowledge base to prevent data duplication
by identifying exact and approximate matches, and thereby helps to remove duplicate data
...
Helps us to perform data cleansing operation on the KB
II
...
III
...
IV
...
csv file
...
Helpful for a data steward/non-data base user/IT professional
What does DQS Activity Monitoring do?
It covers usage and activities against Knowledge Bases, tells the status of the KB or Data Quality projects, the type of activities performed,
the start and end time of the activities etc
...
How to Create Database and Tables insert,edit,Delete using Procedure?
I have post the interview QA for SQl
Answer :
Create Proc Sp_GenrateDMLinSQL
as
USE master
Create Database Temp_2012
--USE Temp_2012
Create Table Tbl_Test
(
Id int primary key identity(1,1),
Ename varchar(200)
)
USE Temp_2012
--insert Query
Insert into Tbl_Test values('Jehovah Jireh');
Insert into Tbl_Test values('Jehovah Ruffa');
Insert into Tbl_Test values('Jehovah Raffa');
Insert into Tbl_Test values('Jehovah Nissi');
Insert into Tbl_Test values('Jayakumar');
Insert into Tbl_Test values('Test');
USE Temp_2012
--Update Query
Update Tbl_Test set Ename='Jesus Never Fail' where Id=5;
USE Temp_2012
--Delete Query
Delete from Tbl_Test where Id=6;
We have two tables one is employee and another one is City
...
Write a query such that all cities should come and count of employees in each city and result should be in descending order of number
of employees in each city
...
The table is as follows: City Table --------------- cityID
cityName 1 Chennai 2 Mumbai 3 New Delhi 4 Kolkatta EmployeeTable --------------------- empID empName empCity 1 Naga 1 2 Siva 1 3 Shankar
2 4 Sundar 3 5 Kevin 1 6 Rajesh 1 7 Karthick 2 8 John 2 9 Shah 3 10 Lal 3 11 Paul 3 12 Zinda 3
select COUNT(e
...
cityName from
dbo
...
Employees e
on e
...
cityID
group by c
...
cityName
order by COUNT(e
...
So we should go for outer join
...
In our example the City Kokatta does not have any employees
...
The second point is, we total number of employees for each city
...
The keyword count gives the total number of employees
...
from dbo
...
Employees e
on e
...
cityID
I have used alias name for City and Employees table to refer or get the columns of each table
...
cityID,c
...
Here the important point to be notes is I am grouping with cityId column of City table not
with employee table
...
In the following line I am soring by number of count using Orderby clause
order by COUNT(e
...
select COUNT(e
...
cityName
The output will be
TotalNoofEmp
cityName
5
New Delhi
4
Chennai
3
Mumbai
0
Kolkatta
I have the following EmployeeMaster table in Test database
...
If I run the following query, how many rows will be returned by SQL Server? select * from EmployeeMaster where empStatus <> 'A'
NOTE: This is objective type question, Please click question title for correct answer
...
The following is a Student's Marks table
...
select * from tbl_StudentMarks where
Stu_Marks =
(select MIN(Stu_Marks) from (
select top 3 Stu_Marks from tbl_StudentMarks
order by Stu_Marks desc) AA)
At first we need to find the 3rd maximum value
...
Two students have scored 96 Marks
...
I used two subqueries to obtain the result
...
select top 3 Stu_Marks from tbl_StudentMarks
order by Stu_Marks desc
The above code pritns 98,97,96
...
(select MIN(Stu_Marks) from (
select top 3 Stu_Marks from tbl_StudentMarks
order by Stu_Marks desc) AA
In the above code I used an alias name called "AA" for intermediate data operation purpose
...
select * from tbl_StudentMarks where
Stu_Marks = 96
At the end of the query we will be geting two rows that is of Vinod and Kellis who scored 96 Marks
...
Marks >= 80 -- Merit ; Marks >=60 and < 80 --- firstclass ;Marks >=50 and <60 ---Second class ;Marks < 50 failure
...
Since I need to update all the column in table I didn't use where
condition
...
When MarksGot >= 80 then I am setting the text "Merit" to
sGradeObt column
...
In the following table there are two student IDs (1and 2)
...
SlNo StuID SubID StuMark 1 1 1 75 2 1 2 88 3 1 3 96 4 1 4 84 5 1 5 80 6 2 1 NULL 7 2 2 NULL 8 2 3 NULL 9 2 4 NULL 10 2 5
NULL
The code is
update b set b
...
StuMark from tblSTUDENTMARKS a
inner join tblSTUDENTMARKS b on a
...
SubID
where a
...
StuID = 2
Here I am using join based on SubjectID not with SlNo
...
I am using alias name 'a' and 'b' for
tblStudentMarks table
...
SubID = b
...
StuId = 1 and b
...
Please note that I am checking table a
...
StuID = 2 in "where" condition
...
So I am updating marks of the "b" table which of student id 2 with marks of "a" table which is of student id- 1
...
Which type of join will you use to map all the records of Shape table with all the records of
Color table
...
Shape table: iShapeID sShapeName iShapeArea 1 Circle 12 2 Square 8 3
Rectangle 40 4 Triangle 15 Color Table: iColorID sColorName 1 Yellow 2 Green 3 Orange 4 Blue 5 Rose
NOTE: This is objective type question, Please click question title for correct answer
...
Which of the following is a Aggreate Function?
NOTE: This is objective type question, Please click question title for correct answer
...
69) as number
NOTE: This is objective type question, Please click question title for correct answer
...
If you define a CHECK constraint on a single column it allows only certain values for this column
...
Write query with which you can achieve paging from SQL side
...
You need to pass parameter of startrowindex and maximum rows
...
declare @StartRowIndex as int
set @StartRowIndex = 0
declare @MaximumRows as int
set @MaximumRows = 200
SELECT * FROM
(
SELECT *, ROW_NUMBER() OVER (Order By Sort_Column) AS RowRank -- Add your desired column name which you want ordered here in
place of "Sort_Column"
FROM
(
SELECT distinct Sort_Column , column1 , column2 -- Desired list of the column names you want to retrieve
FROM [dbo]
...
ClientId = Facilities
...
FacilityId = Facilities
...
As per the above code if the employeeid is matched no rows will be
returned
(II) Multistatement table :
CREATE FUNCTION FNC_MULTIGETEMPDET(@EMPID VARCHAR(10))
RETURNS @tblEMPMaster TABLE
(
EMPID VARCHAR(10),
FirstName VARCHAR(50)
)
AS
BEGIN
IF EXISTS(SELECT EmpID from tbl_EmployeePrim WHERE EmpId = @EMPID)
BEGIN
INSERT INTO @tblEMPMaster
SELECT EmpId, SFirstName
FROM tbl_EmployeePrim
WHERE EmpId = @EMPID
END
ELSE
BEGIN
INSERT INTO @tblEMPMaster
SELECT @EMPID, 'No Records found for the EmpID ' + ISNULL(@EMPID,'NULL')
END
RETURN
END
In the above code I am creating a function with return type TABLE but I created table variable called @tblEMPMaster with two columns such
as EMPID,FirstName
...
System Functions
SQL Server provides many system functions that can be used to perform a variety of operations
...
Some example are :
COALESCE, ISNULL,CONVERT, ISNUMERIC
etc
...
Consider the following Employee table EmpID Salary 1
1000 2 2000 3 5200 4 7855 5 4000 6 6500 7 7000
Here the main constraint is we should not use the TOP keyword
...
Now 7855 will come
...
So now the query will be
select MAX(Salary) from tbl_Employee where Salary <> 7855
The above query gives 7000 which is the second maximum salary
What is the difference between GetDate() and SysDateTime() in SQL Server 2008?
GetDate() and SysDateTime are used to fetch the current datetime of server
...
GetDate() used to get the time upto MilliSeconds where as SysDateTime used to get the time upto NanoSeconds
...
500
2012-12-03 11:28:43
...
Currently the identity is 100
...
Now write a synatx to reset the identity to 51
...
But caution to be taken that the identity column should not be the primary key
...
DBCC execution completed
...
You have a Studets table as follows
...
You need to create a reoprt with SlNo
...
The student Master table is : StuID SName Marks S001 Naga 98 S102 Sundar 92 S203 Ganesh 95 S158 John 85 S211
Kumar 90
We need to use Row_Number() function to obtain the result
...
The query is
SELECT ROW_NUMBER() OVER (ORDER BY StuID ASC) AS 'SlNo' ,* from StudentMaster
I used order by with StudentID
...
The output will be
SlNo StuID SName Marks
1
S001
Naga
98
2
S102
Sundar
92
3
S158
John
4
S203
Ganesh
95
5
S211
Kumar
90
85
You have a Employe table with EmpId, EmpName, EmpAge
...
EmpId is a primary key with identity column
...
You need to start the identity value from 10 instead of 6
...
For that we can use the following query:
Set identity_insert EmployeMaster on
Insert into EmployeMaster(EmpId, EmpName, EmpAge)
values (10,'Shankar',19)
In the above query I mentioned "Identity_Insert on" for table name EmployeeMaster
...
Now we will be having identity value as 10
...
For SQL Server 2008:
1 Clustered Index and 999 Nonclustered Index, Total 1000 Index per table
...
INSERT INTO tbl_Student(Name,Class)
SELECT 'Ali','IV Standard'
UNION ALL
SELECT 'John','V Standard'
UNION ALL
SELECT 'Abraham','X Standard'
Can you split the one column in to two columns For Ex:FullName:Aswini Aluri It must split into firstname: Aswini
...
The beginning of such a modification starts with a transactionand ends when
a transaction finishes (either by a commit or arollback)
...
Consisyency:
Modification on the data in the database either fail or succeed
...
A software crash entails an implicit rollback
...
The 'executor' of a transaction has the feeling that he has the entire database for himeself
...
Difference between IDENT_CURRENT ,@@IDENTITY and SCOPE_IDENTITY ?
1
...
2
...
3
...
write a query to select second highest salary of the employee from the employee table?
select * from employee
select top 1 salary from
(select top 2 salary from employee order by(salary) desc )a
order by(salary)
It first select top 2 salary from table and arrange them in descending order now we have the second highest salary in second number then
select top 1 by ascending it that is the second highest salary
...
Duplicate values are ignored and only displayed once
...
This is only applied at the time an index is created or rebuilt
...
The PAD_INDEX option is used when data manipulation
language operations that lead to excessive nonleaf level page splitting need
to be mitigated
...
When enabled, rows violating the key constraint will fail
...
What is the use of sys
...
indexes catalog view provides information on each index in a database
...
This provides a full accounting of all indexes in a database
...
indexes is useful in a few ways
...
Along with that is the type of the index, identifying whether the index is clustered,
nonclustered, and so forth
...
This includes the fill factor, filter definition, the uniqueness
flag, and other items that were used to define the index
...
index_columns catalog view ?
WHERE (RowRank > @StartRowIndex AND RowRank <= (@StartRowIndex + @MaximumRows))
ORDER BY table_name
How can you insert multiple rows together in a single insert statement for a table?
It is not possible to insert multiple rows with a single insert statement
...
But SQL Server 2008 allows to insert multiple rows with a single insert statement
...
CREATE TABLE [State] (
[StateID]
int,
[StateName]
VARCHAR(20)
)
GO
INSERT INTO State
VALUES (1, 'Gujarat')
INSERT INTO State
VALUES (2, 'Dehli')
INSERT INTO State
VALUES (3, 'Bihar')
But with SQL Server 2008 we can combine all the three insert statement in single insert statement
...
e
...
45)
Result: 95
...
e
...
Select CEILING(-105
...
g Select FLOOR(-34
...
This will return name and description of
various collations
...
g
...
Stored procedure may or not return values
...
Stored Procedures can have select statements as well as DML statements such as insert, update, delete etc
3)Functions will allow only input parameters, doesn’t support output parameters
...
4) Transactions are not allowed within functions
...
5) Stored procedures can’t be called from function
...
6) UDF can be used in join clause as a result set
...
Varchar(Max) can store maximum of 2 147 483 647 Non-Unicode characters
i
...
maximum storage capacity is: 2GB
...
Index can’t be created on a Varchar(Max) data type columns
...
e
...
In case of VARCHAR(MAX), Sql server will try to store
the value ‘in a row’ but if it could not then it will store the value ‘out of row’
...
e
...
When overflow happens, data is stored as old TEXT Data Type and a pointer is replacing the old content
...
NVarchar takes 2 bytes per Unicode/Non-Unicode character
...
NVarchar can store maximum 4000 Unicode/Non-Unicode characters
...
of bytes equal to the no
...
NVarchar takes no
...
of Characters entered plus two bytes extra for defining offset
...
This is the most straightforward way to determine what a table looks like in ad hoc and troubleshooting scenarios
...
Using SELECT * in this case can cause wasteful scans or lookups in order to
return all of the columns, when the query may have been satisfied by a covering index at a much lower resource cost
...
Why declaring VARCHAR without length is not a good practice?
Server has some inconsistent rules about how long a string can be, depending on how the value is defined
...
This is because
when a CHAR-based variable is
declared without a length, the length becomes 1 (and this follows the ANSI standard)
...
This behavior can also come into play when we create tables
...
x_insert
@y VARCHAR
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo
...
x_insert @y = 'foo';
SELECT Result = y FROM dbo
...
This problem goes away if we always declare a length for the CHAR-based columns
...
The values generated by the sequence can be ascending or descending,
starting from any defined value
...
It has been introduced since SQL Server 2012 (code name DENALI)
...
In which situation we will go ahead with SEQUENCE object instead of IDENTITY Column?
A SEQUENCE object can be used instead of the IDENTITY column in the following scenarios:
1) The application requires the value before an INSERT statement is executed
...
3) The application has to restart the sequence after a certain value has been
reached
...
2) There aren’t any unique constraints on a sequence value
...
What is the purpose of "EXECUTE…WITH RESULT SETS" introduce with SQL Server 2012?
The WITH RESULT SETS clause is related to the EXECUTE statement
...
We can specify the new option with the EXECUTE statement when executing a stored procedure or a dynamic
batch, like so:
EXECUTE WITH ;
E
...
EXEC('SELECT 43112609 AS val;')
WITH RESULT SETS
(
(
val VARCHAR(10)
)
);
Henceforth, we can make out that, irrespective of the column name(s) returned in the result set, we can change the column names and it’s
data Types as long as the data Type conversion is compatible with the original result set(i
...
the data types defined in the table schema)
...
Where can we use "EXECUTE…WITH RESULT SETS"?
a
...
b
...
Suppose a dotnet application is expecting a Boolean and the underlying schema was
designed as of type int for that column
...
Instead of that, we can
directly change the data type to bit
...
Another example can be say the dotnet application is expecting a int but the column type is float
...
Another usage may be say the schema has been changed and the DAL layer is not aware of this
...
In such a scenario, we can just change the column names at runtime in the With Result Set so that the table schema as
well as the DAL logic will be un touched
...
The number of columns has to be same as that of the result set
...
The Usp_FetchRecords is as under
CREATE PROCEDURE [dbo]
...
tbl_Test;
Select Id,Name From dbo
...
THROW outside of a TRY…CATCH block acts similar to the RAISERROR() function, with a
few notable exceptions:
- The message_id parameter doesn’t need to be defined in sys
...
- The message_id must be INT (not BIGINT) and greater than or equal to 50000
...
- The severity level is always 16
...
The next example shows a division by 0 into a TRY block
...
Then, the THROW command displays the error:
BEGIN TRY
select 1/0
END TRY
BEGIN CATCH
PRINT N'Message from inside CATCH
...
2)As @@ Error values changes with every execution of statement in the code we need to use a local variable to store the @@error value and
use it whenever needed
...
What the statement implies is that, suppose we
have 100 records and we want to skip the first 10 records
...
In this case if we issue something as
Select *
Hope this helps
...
Two of the simplest ways are described below assuming your table is having
column(date1) containing value of data creation
...
delete from table1
where id1 not in
(
select top 10 id1 from table1
order by date1 desc
)
Way2:
With this query we are listing records in descending way with date as key and having index more then 10
...
delete from table1
WHERE id1 in (
SELECT id1
FROM
(
SELECT id, ROW_NUMBER() OVER(ORDER BY date1 DESC) AS rownumber
FROM table1
) AS a
WHERE rownumber > 10
)
How can you declare and initialize variables in a single line with SQL Server ?
With SQL Server 2005, we need to declare and initialize variables individually
...
For addressing the same let us fire the below query
SELECT
[User Name] = CASE WHEN GROUPING([User Name]) = 1 THEN 'Total:'
ELSE [User Name] END
,[Count] = COUNT ([User Name])
FROM @t
GROUP BY [User Name] WITH ROLLUP
As can be figure out that, we have count for the users at every individual levels as well as the total count
...
What is a Super Key ?
Super Key is a key which is used to define uniqueness of a row with the collection of more than one column
Eg:- There is Customer Table
The Primary Key will be CustomerID field but this cannot be termed as a Super key
...
If we see the above eg over here we have club 2 columns and uniquely identified a ROW
Database normalization is process of
NOTE: This is objective type question, Please click question title for correct answer
...
* To any external application the column will behave the same
* Sparse columns work really well with filtered indexes as you will only
want to create an index to deal with the non-empty attributes in the column
...
The
column set behaves like a column itself
...
* Change Data Capture and Transactional replication both work, but not the
column sets feature
...
For example,
If you want to give the 1st preference to Mango, 2nd for banana, 3rd for Apple and so on
CREATE TABLE UserPreferences(FruitId int identity, FruitName varchar(40))
GO
INSERT INTO UserPreferences VALUES( 'Apple'), ('Cherry'), ('Mango'), ('Banana')
SELECT * FROM UserPreferences
/*Sample Data:
FruitId
FruitName
1
Apple
2
Cherry
3
Mango
4
Banana
*/
Answer should be:
SELECT * FROM UserPreferences ORDER BY CASE WHEN FruitName = 'Mango' THEN 1 WHEN FruitName = 'Banana' THEN 2 WHEN
FruitName = 'Apple' THEN 3 ELSE 4 END
/*RESULT in custome order:
FruitId
FruitName
3
Mango
4
Banana
1
Apple
2
Cherry*/
What are the Different ways of importing data into SQL Server?
Many approaches are available to import data into SQL Server
...
1
...
2
...
1
...
2
...
e
...
Query is expected to return 30,
40 department's employee details
...
WHY?
Reason1: NOT IN operation is equivalent to "AND operation of multiple conditions"
...
As a result of above two reasons query doesn't return any record
Title: sql server interview question
Description: it is given sql server interview question.these questions mostly asked by interviewer.
Description: it is given sql server interview question.these questions mostly asked by interviewer.