What is Compound Operators in sql server
2008?
Compound operators are a combination
of operator with another operator.
arithmetic operators - +=,-=,*=, /=,
%=,
bitwise operators -&=, ^=,|=
What is filtered indexes in sql server 2008?
Filtered index in SQL
Server 2008 is an index WHERE clause. A filtered index is an optimized
non-clustered index. It allows for defining the filter predicate with WHERE
clause at the time of creating index.
Ex: CREATE NONCLUSTERED INDEX FI_Employee_DOJ ON Employee(DOJ)
WHERE DOJ IS NOT NULL
WHERE DOJ IS NOT NULL
In the above example the NOT NULL is the filtered criteria for the
index. Employee is the table and DOJ is the column name.
Explain GO Command
GO command indicates
the end of the SQL statements. It is used when there are multiple statements to
be executed but sent as a batch.
A
top-to-bottom relationship among the items in a database is established by a
Hierarchical schema
Report generators are used to
Retrieve information
from files and Answer queries
An audit
trail is
The recorded history
of operations performed on a file
Which of
the following commands display schema of a table?
DESCRIBE
The
highest level in the hierarchy of data organization is called
Data base
ER model
is used in which phase?
Conceptual schema
Which Lock
ensures that multiple updates cannot be made to the same resource at the same
time.
Exclusive
Ranking
Functions
Ranking functions
return a ranking value for each row in a partition. SQL Server has total of 4
ranking function.
ROW NUMBER
ROW NUMBER
RANK
DENSE RANK
NTILE
ROW_NUMBER () OVER
([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
RANK () OVER
([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set.
Returns the rank of each row within the partition of a result set.
DENSE_RANK () OVER
([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.
NTILE (integer_expression) OVER ([<partition_by_clause>]
<order_by_clause>)
Distributes the rows in an ordered partition into a specified number of groups.
Distributes the rows in an ordered partition into a specified number of groups.
Each group contains the same number
of rows, or, if the number of rows in a partition is not divisible by "integer_expression",
lower-numbered groups (starting from 1, 2, ...) will each contain one more row.
For example,
NTILE(2): 1, 1, 1, 1, 1, 2, 2, 2,
2, 2
NTILE(3): 1, 1, 1, 1, 2, 2, 2, 3, 3, 3
NTILE(4): 1, 1, 1, 2, 2, 2, 3, 3, 4, 4
NTILE(5): 1, 1, 2, 2, 3, 3, 4, 4, 5, 5
NTILE(6): 1, 1, 2, 2, 3, 3, 4, 4, 5, 6
NTILE(3): 1, 1, 1, 1, 2, 2, 2, 3, 3, 3
NTILE(4): 1, 1, 1, 2, 2, 2, 3, 3, 4, 4
NTILE(5): 1, 1, 2, 2, 3, 3, 4, 4, 5, 5
NTILE(6): 1, 1, 2, 2, 3, 3, 4, 4, 5, 6
What is
Normalization of database?
Normalization is set
of rules that are to be applied while designing the database tables which are
to be connected with each other by relationships. This set of rules is called
Normalization.
Is it possible to create two identity column in a single table?
No
Which functions ignore NULL values by default?
aggregate
functions
What is the significance of NVL() function in SQL?
to set default value for NULL value
NVL(col_name,value):if col_value is null then put value to
that field.
example:
select ename,(sal*12+nvl(comm,0)*12) as annual_sal
from emp
example:
select ename,(sal*12+nvl(comm,0)*12) as annual_sal
from emp
Does user defined functions supports DML commands?
No
User defined functions does not support insert, update,
delete statements in it.
I want to insert a value in identity column. Which of the following SQL statement I have to execute before insertion of values?
SET
IDENTITY_INSERT Account ON
An Identity column is an auto increment
column in SQL Server. By default any attempt to insert any value into the
Identity column results in error.
But we can insert a value into identity column but before that we have to execute following statement
SET IDENTITY_INSERT Account ON
But we can insert a value into identity column but before that we have to execute following statement
SET IDENTITY_INSERT Account ON
What is use of SPACE function ?
convert the
specified integer value into spaces
Difference
between primary key and unique key
One table may have more than one
Unique Key Constraint columns, but should have only one Primary Key Constraint column.
unique
key creates by default non clustered index, but primary key creates by default
clustered index.
unique
key allows null values in the column, but primary key Wont allow Null values.
Find any
text in Sql server Database
Declare @text varchar(100)
set @text='%Ticket Ref%'
SELECT sys.sysobjects.*, sys.syscomments.text
FROM sys.sysobjects INNER JOIN
sys.syscomments ON sys.sysobjects.id = sys.syscomments.id
WHERE sys.syscomments.text like @text
This will find “text” in all type of objects Stored
procedure, functions, Views etc.
if you want to find the “text” in particular type of objects than you have
to use [xtype='P' i.e. for Stored Procedure] in where clause like this
WHERE sys.syscomments.text like @text and xtype='P'
These are the type of objects codes which is used in sysobjects:
U – User table
D – Default constraint
P – Stored procedure
V – View
TR – Trigger
TF – Table Function (which returns as table) FN – Function
D – Default constraint
P – Stored procedure
V – View
TR – Trigger
TF – Table Function (which returns as table) FN – Function
How to determine total number of open/active connections in sql server
SELECT
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame
How to fetch unmatching records from two SQL tables ?
Table1
Id Name
1 Prashant
2 Ravi
3 Gaurav
5 Naween
7 Sachin
1 Prashant
2 Ravi
3 Gaurav
5 Naween
7 Sachin
Table2
Id Name
1 Prashant
2 Ravi
4 Alok
6 Raja
1 Prashant
2 Ravi
4 Alok
6 Raja
The
output I want is
Id Name
3 Gaurav
4 Alok
5 Naween
6 Raja
7 Sachin
3 Gaurav
4 Alok
5 Naween
6 Raja
7 Sachin
select a.Id, a.Name from Table1 a left outer join Table2 b on a.Name = b.Name where b.Id is null
UNION ALL
select a.Id, a.Name from Table2 a left outer join Table1 b on a.Name = b.Name where b.Id is null
UNION ALL
select a.Id, a.Name from Table2 a left outer join Table1 b on a.Name = b.Name where b.Id is null
SQL Optimization
1) The sql query becomes faster if you use the actual columns
names in SELECT statement instead of than '*'.
2) Sometimes you may have more than one subqueries in your main
query. Try to minimize the number of subquery block in your query.
For Example: Write the query as
SELECT name
FROM employee
WHERE (salary, age) = (SELECT MAX (salary), MAX (age)
FROM employee_details)
Instead of:
FROM employee
WHERE (salary, age) = (SELECT MAX (salary), MAX (age)
FROM employee_details)
Instead of:
SELECT name
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
3) Better use UNION ALL if a
distinguished result is not required. UNION ALL is faster than UNION
4) Do not call functions repeatedly within your Stored Procedures,
triggers and functions. For example, you might need the length of a string
variable in many places of your procedure, but don't call the LEN function whenever it's needed; instead, call
the LEN function once, and store the result in a
variable for later use.
5) Do not use
"SP_XXX" as a naming convention in Stored Procedures.
6) Use "Set Nocount On" to eliminate extra network trip in
Stored Procedures.
7) Do not use views that retrieve data from a single table only
(that will be an unnecessary overhead). Use views for writing queries that
access columns from multiple tables.
STUFF
function in SQL Server
The
STUFF function inserts a string into another string. It deletes a specified
length of characters in the first string at the start position and then inserts
the second string into the first string at the start position.
STUFF ( character_expression ,
start , length , replaceWith_expression )
SELECT STUFF('abcdef', 2, 3, 'ijklmn');
aijklmnef
What is
constraints
SQL Server users
constraints to enforce limitations on the data that can be entered into a
particular column in table. There are following types of constraints.
Unique, Default, Check, Primary Key, Foreign Key, Not Null.
Unique, Default, Check, Primary Key, Foreign Key, Not Null.
Difference
Between Implict Transaction And Explict Transaction
Implicit Transaction
is the auto commit. There is no beginning or ending of the transaction.
Explicit Transaction has the beginning, ending and rollback of transactions with the command
Begin Transaction
Commit Transaction and
Rollback Transation
In the explicit transaction, if an error occurs in between we can rollback to the begining of the transaction which cannot be done in implicit transaction.
Explicit Transaction has the beginning, ending and rollback of transactions with the command
Begin Transaction
Commit Transaction and
Rollback Transation
In the explicit transaction, if an error occurs in between we can rollback to the begining of the transaction which cannot be done in implicit transaction.
What is
the use of @@TRANCOUNT in SQL Server
Returns the number of active
transactions for the current connection.
PRINT
@@TRANCOUNT
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. (Net Answer)
They get stored in
master db in the syslogins table. (My Opinion)
Can we
create a Foreign Key with out Primary Key
Yes. If the table has
Unique Key then it is posible to create a Foreign key constraint
Default
security model in Microsoft SQL Server
Standard security setting
Microsoft
SQL Server's default protocol
Named Pipes
Number of
PRIMARY KEY/FOREIGN KEY Constraints in a Table in SQL Server 2005/2008
PRIMARY KEY :1, FOREIGN KEY :UNLIMITED (Recommended is 253)
DEFAULT
definitions cannot be created on columns defined with the following DATA TYPE
Timestamp
Explain
about SQL COALESCE..
This COALESCE function is used to return a value which is the
first non-null expression among all its arguments.
If all the arguments are NULL, in that case COALESCE will return NULL.
It is also used to display any other value instead of NULL value in the result.
If all the arguments are NULL, in that case COALESCE will return NULL.
It is also used to display any other value instead of NULL value in the result.
Example:
SELECT Name, COALESCE(Business_Phone,
Cell_Phone, Home_Phone) Contact_Phone
FROM Contact_Info
How will
you copy the structure of a table without copying the data ?
By using UNIVERSAL FASLE condition like 1=2,A=B....etc.
This is the only method for copying the structure of the table without copying the data.
This is the only method for copying the structure of the table without copying the data.
Example:
create table NEWTable AS select * from
OLDTABLE where 1=2
Are the two statements same :- select custid from tbl_order; select All custid from tbl_order;
Yes
both
the statements are same. By default, it is 'All'. That means the query will
fetch all the rows irrespective of duplicates.
If you want to remove the duplicates then write 'Distinct' instead of 'All'.
If you want to remove the duplicates then write 'Distinct' instead of 'All'.
Can we call a Trigger in store procedure ?
We cannot call a trigger explicitly from a Stored Procedure as it
is also a special kind of Stored Procedure.
A Trigger will fire automatically on the happening of an event like before or after insert, update or delete.
A Trigger will fire automatically on the happening of an event like before or after insert, update or delete.
What is linked server?
One sql server is added to another sql server, this concept is called linked server. Linked server is used to extract query from different sql server.
Maximum no., of columns in Wide Table..?
Wide tables can define up to 30,000 columns.
Difference
between Composite , Candidate and alternate keys ?
Composite Key :
A composite key is a combination of more than one column to identify a unique row in a table.
A composite key is a combination of more than one column to identify a unique row in a table.
Candidate Key:
All keys in a table that become unique called as 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.
Among of candidate keys if any single key or combination of keys made as primary key then rest candidate key called as alternate key.
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.
What's the
maximum size of a row
8060 bytes. Don't be
surprised with questions like 'what is the maximum number of columns per
table'. Check out SQL Server books online for the page titled: "Maximum
Capacity Specifications".
Difference
between SQL Server 2008 and SQL Server 2012
SQL Server 2008:
1. SQL Server 2008 is code named as Katmai.
2.The Maximum number concurrent connections to SQL Server 2008 is 32767.
3.The SQL Server 2008 uses 27 bit bit precision for spatial calculations.
4.TRY_CONVERT() and FORMAT() functions are not available in SQL Server 2008
5.ORDER BY Clause does not have OFFSET / FETCH options as in SQL Server 2012
1. SQL Server 2008 is code named as Katmai.
2.The Maximum number concurrent connections to SQL Server 2008 is 32767.
3.The SQL Server 2008 uses 27 bit bit precision for spatial calculations.
4.TRY_CONVERT() and FORMAT() functions are not available in SQL Server 2008
5.ORDER BY Clause does not have OFFSET / FETCH options as in SQL Server 2012
6.Sequence is not available in SQL Server 2008
SQL Server 2012:
1.SQL Server 2012 is code named as Denali
2.SQL server 2012 has unlimited concurrent connections.
3.The SQL Server 2012 uses 48 bit precision for spatial calculations
4.TRY_CONVERT() and FORMAT() functions are newly included in SQL Server 2012
5. ORDER BY Clause now have OFFSET / FETCH options to use paging to show required rows per page in applications and allow the user to scroll through each page of results rather than download the entire set
1.SQL Server 2012 is code named as Denali
2.SQL server 2012 has unlimited concurrent connections.
3.The SQL Server 2012 uses 48 bit precision for spatial calculations
4.TRY_CONVERT() and FORMAT() functions are newly included in SQL Server 2012
5. ORDER BY Clause now have OFFSET / FETCH options to use paging to show required rows per page in applications and allow the user to scroll through each page of results rather than download the entire set
6. Sequence is included in SQL Server 2012.Sequence is a user
defined object that generates a sequence of a number.
Here is an example using Sequence.
/****** Create Sequence Object ******/
CREATE SEQUENCE MySequence
START WITH 1
INCREMENT BY 1;
/****** Create Temp Table ******/
DECLARE @Person TABLE
(
ID int NOT NULL PRIMARY KEY,
FullName nvarchar(100) NOT NULL
);
/****** Insert Some Data ******/
INSERT @Person (ID, FullName)
VALUES (NEXT VALUE FOR MySequence, 'Umar Ali'),
(NEXT VALUE FOR MySequence, 'John Peter'),
(NEXT VALUE FOR MySequence, 'Mohamed Iqbal');
/****** Show the Data ******/
SELECT * FROM @Person;
The results would look like this:
ID FullName
1 Umar Ali
2 John Peter
3 Mohamed Iqbal
Here is an example using Sequence.
/****** Create Sequence Object ******/
CREATE SEQUENCE MySequence
START WITH 1
INCREMENT BY 1;
/****** Create Temp Table ******/
DECLARE @Person TABLE
(
ID int NOT NULL PRIMARY KEY,
FullName nvarchar(100) NOT NULL
);
/****** Insert Some Data ******/
INSERT @Person (ID, FullName)
VALUES (NEXT VALUE FOR MySequence, 'Umar Ali'),
(NEXT VALUE FOR MySequence, 'John Peter'),
(NEXT VALUE FOR MySequence, 'Mohamed Iqbal');
/****** Show the Data ******/
SELECT * FROM @Person;
The results would look like this:
ID FullName
1 Umar Ali
2 John Peter
3 Mohamed Iqbal
What are computed Columns?
Computed columns are the columns that can be used to store the
calculated
results based upon some other columns of the table
results based upon some other columns of the table
No comments:
Post a Comment