1. What are three SQL keywords
used to change or set permissions? GRANT,
DENY, and REVOKE.
2. What is the basic functions
for master, msdb, model, tempdb databases?
Master
- database holds information
for all databases located on the SQL Server instance and is the glue that holds
the engine together. Because SQL Server cannot start without a functioning
master database, you must administer this database with care.
msdb - database
stores information regarding database backups, SQL Agent information, DTS packages,
SQL Server jobs, and some replication information such as for log shipping.
tempdb - holds temporary
objects such as global and local temporary tables and stored procedures.
mode -l is essentially a
template database used in the creation of any new user database created in the
instance.
3. Constraints – Enforcing some role
PRIMARY KEY is a unique identifier for a
row within a database table. We can Only One time assign in the column of the
Table. It will be Not allow the Null and Repeated data in the table column.
UNIQUE same as Primary key But we can assign no of column in the table
and it will allow only one time Null
value.
FOREIGN
KEY Referential integrity. But we can assign no of column in
the table and it will allow repeated value Not allow the null value.
A CHECK constraint is
used to limit the values that can be placed in a column. The check constraints
are used to enforce domain integrity.
A NOT NULL constraint
enforces that the column will not accept null values. The not null constraints
are used to enforce domain integrity, as the check constraints.
4. What is De-normalization?
De-normalization is the process
of attempting to optimize the performance of a database by adding redundant
data. It is sometimes necessary because current DBMSs implement the relational
model poorly. A true relational DBMS would allow for a fully normalized
database at the logical level, while providing physical storage of data that is
tuned for high performance. De-normalization is a technique to move from higher
to lower normal forms of database modeling in order to speed up database
access.
5. How to get @@error and
@@rowcount at the same time?
If @@Rowcount is checked after
Error checking statement then it will have 0 as the value of @@Recordcount as it
would have been reset. And if @@Recordcount is checked before the
error-checking statement then @@Error would get reset. To get @@error and
@@rowcount at the same time do both in same statement and store them in local
variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR
6. What is a Scheduled Jobs or
What is a Scheduled Tasks?
Scheduled tasks let user
automate processes that run on regular or predictable cycles. User can schedule
administrative tasks, such as cube processing, to run during times of slow
business activity. User can also determine the order in which tasks run by
creating job steps within a SQL Server Agent job. E.g. Back up database, Update
Stats of Tables. Job steps give user control over flow of execution. If
one job fails, user can configure SQL Server Agent to continue to run the
remaining tasks or to stop execution.
7.How do you load large data to
the SQL server database?
BulkCopy is a tool used to copy
huge amount of data from tables. BULK INSERT command helps to Imports a data
file into a database table or view in a user-specified format.
8. What is the difference between
stroed procedure and stored function in SQL?
Function can return a table variable where as sp cannot.
T-SQl ignore error in Sp and proceed to next line where as function stops.
You Can use sp in XML For Clause , but Function can't be used there
9. Can we have an updateable view in SQL?
no , they are read only.
10 What is connection pooling? how can we acheive that in
asp.net?
there
is a pool for database connections, the moment when we request for database
connection ADO.net check if there is a recent connection in the pool with the
same connectionString, If it find, it used that otherwise create a new
connection.We can use connection Pooling in asp.net by System.Data.SqlClient.SqlConnectionPoolManager
furthermore, We can define pooling attribute in connection string
pooling=false|True,Min Pool Size=50,Max Pool Size=600
11. What is the maximum length of a varchar
in SQL Server? 8000
13. What are the
different types of replication? Explain.
The SQL Server 2000-supported
replication types are as follows:
Publisher—The server providing the source data that will be made available
to subscribers.
Article—A
collection of data that exists as part of a replication publication.
Publication—One or more articles that act as a unit of replication.
Distributor—The server that is responsible for providing data to subscribers.
Subscriber—A server or client that receives a publication.
Push Replication—A method of replication whereby the distributor delivers
the data to the
subscriber.
Pull Replication—A
method of replication whereby the subscriber requests the data from the
distributor.
Publisher/Distributor—A publishing server that acts as its own distributor.
· Transactional
· Snapshot
· Merge
Snapshot replication distributes
data exactly as it appears at a specific moment in time and does not monitor
for updates to the data. Snapshot replication is best used as a method for
replicating data that changes infrequently or where the most up-to-date values
(low latency) are not a requirement. When synchronization occurs, the entire
snapshot is generated and sent to Subscribers.
Transactional replication, an
initial snapshot of data is applied at Subscribers, and then when data
modifications are made at the Publisher, the individual transactions are
captured and propagated to Subscribers.
Merge replication is the process of
distributing data from Publisher to Subscribers, allowing the Publisher and
Subscribers to make updates while connected or disconnected, and then merging
the updates between sites when they are connected.
14.
What is Normalization
* Normalization is the process of organizing data in a database.
* This includes creating tables and establishing relationships
between those tables according to rules designed
both to protect the
data
* make the database more flexible by eliminating two factors:
1.redundancy
2.inconsistent dependency.
Rules
of Data Normalization
1NF Eliminate Repeating Groups - Make a separate table for each set
of related attributes, and give each table a primary key.
2NF Eliminate Redundant Data - If an attribute depends on only part
of a multi-valued key, remove it to a separate table.
3NF Eliminate Columns Not Dependent On Key - If attributes do not
contribute to a description of the key, remove them to a separate table.
BCNF
Boyce-Codd Normal Form - If there are non-trivial dependencies between
candidate key attributes, separate them out into distinct tables.
4NF Isolate Independent Multiple Relationships - No table may contain
two or more 1:n or n:m relationships that are not directly related.
5NF Isolate Semantically Related Multiple Relationships - There may
be practical constrains on information that justify separating logically
related many-to-many relationships.
ONF
Optimal Normal Form - a model limited to only simple (elemental) facts,
as expressed in Object Role Model notation.
DKNF
Domain-Key Normal Form - a model free from all
modification anomalies.
15.
What is Stored Procedures?
1.System stored procedures -- mainly used for administrating,
assisting, configuring and monitoring the SQL server.
2.Extended stored procedures
3.User stored procedures
16. What is
Trigger?
A trigger is a SQL procedure that initiates an action when an event (INSERT,
DELETE or UPDATE) occurs. Triggers are stored in and managed by the
DBMS.Triggers are used to maintain the referential integrity of data by
changing the data in a systematic fashion. A trigger cannot be called or
executed; the DBMS automatically fires the trigger as a result of a data
modification to the associated table.
Triggers can be viewed as similar to stored procedures in that both consist of
procedural
logic that is stored at the database
level. Stored procedures, however, are not event-drive and are not attached to
a specific table as triggers are. Stored procedures are explicitly executed by
invoking a CALL to the procedure while triggers are implicitly executed. In
addition, triggers can also execute stored procedures.
data manipulation language (DML) triggers and
data definitionlanguage (DDL) triggers.
DML triggers run when INSERT, UPDATE, or DELETEstatements modify data in a
specified table or view.
DDL triggers, which run in response to DDL events that occur on the server such
as creating, altering, or dropping an object, are used for database
administration tasks such as auditing and controlling object access
When you execute an INSERT operation,
the INSERTED table contains each row that
was inserted into the table, whereas
the DELETED table does not contain any rows.
When you execute a DELETE statement,
the DELETED table contains each ro
was deleted from the table, whereas the
INSERTED table does not contain any rows.
When you execute an UPDATE statement,
the INSERTED table contains the after
image of each row you updated, and the
DELETED table contains the before image of
each row that you updated.
The before image is simply a copy of
the row as it existed
before you executed the UPDATE
statement. The after image reflects the data in the
row after the UPDATE statement has
changed appropriate values.
Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within
itself, so when the trigger is fired because of data modification it can also
cause another data modification, thereby firing another trigger. A trigger that
contains data modification logic within itself is called a nested trigger.
17.
What is difference between DELETE & TRUNCATE commands?
Delete
command removes the rows from a table based on the condition that we provide
with a WHERE clause. 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.
TRUNCATE
TRUNCATE is faster and uses fewer system
and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating
the data pages used to store the table’s data, and only
the page deallocations are recorded in the
transaction log.
TRUNCATE removes all rows from a table, but
the table structure, its columns, constraints, indexes and so
on, remains. The counter used by an
identity for new rows is reset to the seed for the column.
You cannot use TRUNCATE TABLE on a
table referenced by a FOREIGN KEY constraint. Because
TRUNCATE TABLE is not logged, it cannot
activate a trigger.
TRUNCATE cannot be rolled back.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table
DELETE
DELETE removes rows one at a time and
records an entry in the transaction log for each deleted row.
If you want to retain the identity counter,
use DELETE instead. If you want to remove table definition
and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE
clause
DELETE Activates Triggers.
DELETE can be rolled back.
DELETE is DML Command.
DELETE does not reset identity of the
table.
18. What types
of Joins are possible with Sql Server?
1. A join combines records from two
tables in a relational database and results in a new (temporary) table, also
called joined table
2. Joins are used in queries to explain how different tables are related. Joins
also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER
JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS,
RIGHT OUTER JOINS and FULL OUTER JOINS.
INNER JOIN
An inner join essentially finds the
intersection between the two tables. This is the most common type of join used,
and is considered the default join type. Inner joins return all rows from
multiple tables where the join condition is met.
CROSS JOIN
While not used very commonly, a cross
join is the foundation upon which inner joins are built. A cross join returns
the cartesian product of the sets of rows from the joined tables. Thus, it is
an inner join where the join condition always evaluates to True.
If A and B are two sets then cross join
= A X B.
LEFT OUTER JOIN
A left outer join is very different
from an inner join. Instead of limiting results to those in both tables, it
limits results to those in the "left" table (A). This means that if
the ON clause matches 0 records in B, a row in the result will still be
returned but with NULL in each column from B.
This type of join returns all rows from
one table and only those rows from a secondary table where the joined fields
are equal (join condition is met).
A left outer join returns all the
values from left table + matched values from right table (or NULL in case of no
matching value).
RIGHT OUTER JOIN
A right outer join is much like a left
outer join, except that the tables are reversed. Every record from the right
side, B, will be returned, and NULL will be returned for columns from A for
those rows that have no matching record in A.
A right outer join returns all the
values from right table + matched values from left table (or NULL in case of no
matching value).
FULL OUTER JOIN
A full outer
join combines the results of both left and right outer joins. These joins will
show records from both tables, and fill in Nulls for missing matches on either
side.
19.
What is the difference of a LEFT JOIN and an INNER JOIN statement?
A LEFT JOIN will take ALL values from the first declared table and matching
values from the second declared table based on the column the join has been
declared on. An INNER JOIN will take only matching values from both tables
20. What is job?
It can be defined as a task performed by a computer system.
For example, printing a file is a job. Jobs can be performed by a single
program or by a collection of programs.
21.How do you find the error, how can you know the number of rows
affected by last SQL Statement?
@@errors->give the last
error occurred in the current DB.
select @@rowcount
Use @@ERROR which returns the error number for the last T-SQL
statement executed knowing the error.Use @@ROWCOUNT which returns the number of
rows affected by the last statement for finding the no of rows affected.
22. How do u call and execute a stored procedure in .NET?
system.Data;
system.Data.SqlClient;
SqlConnection sqCon = new SqlConnection(”connection
string”);
SqlCommand sqCmd = new SqCmd();
sqCmd.Connection = sqCon;
sqCmd.CommandText = procedure_name;
sqCmd.CommandType = CommandType.StoredProcedure;
sqComd.ExecuteReader();
23.
What is Stored Procedure?
A stored procedure is a named group
of SQL statements that have been previously created and stored in the server
database. Stored procedures accept input parameters so that a single procedure
can be used over the network by several clients using different input data. And
when the procedure is modified, all clients automatically get the new version.
Stored procedures reduce network traffic and improve performance. Stored
procedures can be used to help ensure the integrity of the database.
e.g.
sp_helpdb, sp_renamedb, sp_depends etc.
24. What is a trigger?
Triggers are basically used to implement business rules. A trigger
is also similar to stored procedures. The difference is that it can be
activated when data is added or edited or deleted from a table in a database.
Nested Trigger: A
trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when
the trigger is fired because of data modification it can also cause another
data modification, thereby firing another trigger. A trigger that contains data
modification logic within itself is called a nested trigger.
25. What is
View?
A simple view can be thought of as a subset of a table. It can be used for
retrieving data, as well as updating or deleting rows. Rows updated or deleted
in the view are updated or deleted in the table the view was created with. It
should also be noted that as data in the original table changes, so does data
in the view, as views are the way to look at part of the original table. The
results of using a view are not permanently stored in the database. The data
accessed through a view is actually constructed using standard T-SQL select
command and can come from one to many different base tables or even other
views.
The only restrictions are that the
view’s SELECT statement CANNOT do the following:
■ Use the COMPUTE or COMPUTE BY clause
■ Use the INTO keyword
■ Use the OPTION clause
■ Reference a temporary table or table
variable
■ Use the ORDER BY clause unless it also specifies the TOP operator
■ you cannot use TOP when you specify WITH CHECK OPTION
you can include a WHERE clause that
limits the range of rows that the view returns. However, the WHERE clause does
not restrict the changes that users can make through the view. To restrict the
changes that users can make, you use the CREATE VIEW command’s WITH CHECK
OPTION clause when defining the view.
The SELECT statement that is used to create the view must
follow these restrictions:
1. Column names must be explicitly
stated in the SELECT statement; you can’t use * or table name.*
to access columns.
2. You may not reference a column twice
in the SELECT statement unless all references,or all but one
reference, to the column is made in a complex expression. For example,
the following is illegal:
SELECT qty, orderid, qty
However, the following is legal:
SELECT qty, orderid, SUM (qty)
3.
You may not use a derived table that comes from using a SELECT statement
encased in
parentheses in the FROM clause of a SELECT statement.
4.
You can’t use ROWSET, UNION, TOP, ORDER BY, DISTINCT, COUNT(*), COMPUTE,
or
COMPUTE BY.
5.
Subqueries and outer or self JOINs can’t be used.
6.
The AVG, MAX, MIN, STDEV, STDEVP, VAR, and VARP aggregate functions
aren’t allowed
in the SELECT statement. If you need the functionality they provide,
consider replacing
them with either SUM() or COUNT_BIG().
7.
A SUM() that references a nullable expression isn’t allowed.
8.
A Common Language Specification (CLS) user-defined function can only
appear in the
SELECT list of the view, it can’t be used in WHERE or JOIN clauses.
9.
CONTAINS and FREETEXT aren’t allowed in the SELECT statement.
10. If you use GROUP BY, you can’t use
HAVING, ROLLUP, or CUBE, and you must use COUNT_
BIG() in the select list.
Common uses for views include:
Filtering rows
Protecting sensitive data
Reducing database complexity
Abstracting multiple physical databases into
one logical database
1. You can’t modify data in a view that
uses aggregate functions. Aggregates are functions
that return a summary value of some
kind, such as SUM () or AVG (). If you try to modify
such a view, you’ll get an error.
2. If you use a view to modify data,
the modification can affect only one base table at a time.
This means if a view presents data from
two tables, you can write a statement that will
update only one of those tables—if your
statement tries to update both tables, you’ll get
an error message.
Indexed View
1. The view must use the SCHEMABINDING
option.
2. If it references any user-defined
functions (more on these later in the book), then these must
Also be schema bound.
3. The view must not reference any
other views—just tables and UDFs.
4. All tables and UDFs referenced in
the view must utilize a two-part (not even three-part and
four-part names are allowed) naming convention (for example
dbo.Customers,BillyBob.SomeUDF) and must also have the same owner as the view.
5. The view must be in the same database as all objects
referenced by the view.
6. The ANSI_NULLS and
QUOTED_IDENTIFIER options must have been turned on (using the SET
command) at the time the view and all underlying tables were created.
7. Any functions referenced by the
view must be deterministic.
Using Partitioned Views
Partitioned views allow the data in a
large table to be split into smaller member tables. The data is partitioned
between the member tables based on ranges of data values in one of the columns.
The data ranges for each member table are defined in a CHECK constraint
specified on the partitioning column.
A view that uses UNION ALL to combine
selects of all the member tables into a single result set is then defined. When
SELECT statements referencing the view specify a search condition on the
partition column, the query optimizer uses the CHECK constraint definitions to determine
which member table contains the rows.
26. What is an Index?
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.
What is the
difference between clustered and a non-clustered index?
A clustered index is a special type of index that reorders the way records in
the table are physically stored. Therefore table can have only one clustered
index. The leaf nodes of a clustered index contain the data pages.
A nonclustered index is a special type of index in which the logical order of
the index does not match the physical stored order of the rows on disk. The
leaf node of a nonclustered index does not consist of the data pages. Instead,
the leaf nodes contain index rows.
27. What are the different index configurations a table can have?
A table can have one of the following index configurations:
No indexes
A clustered index
A clustered index and many nonclustered indexes
A nonclustered index
Many nonclustered indexes
28. What are cursors?
Well cursors help us to do an operation on a set of data that we
retrieve by commands such as Select columns from table. For example : If we
have duplicate records in a table we can remove it by declaring a cursor which
would check the records during retrieval one by one and remove rows which have
duplicate values.
In order to work with a cursor we need
to perform some steps in the following order:
Declare cursor
Open cursor
Fetch row from the cursor
Process fetched row
Close cursor
Deallocate cursor
Disadvantages:
Cursor plays there row quite nicely but
although there are some disadvantage of Cursor .
Because we know cursor doing roundtrip
it will make network line busy and also make time consuming methods.
First of all select query gernate
output and after that cursor goes one by one so roundtrip happen.Another
disadvange of cursor are ther are too costly because they require lot of
resources and temporary storage so network is quite busy.
29.
Which TCP/IP port does SQL Server run on?
SQL Server runs on port 1433 but we can also change it for better
security.
30 Can we use Truncate
command on a table which is referenced by FOREIGN KEY?
No. We cannot use Truncate command on a table with Foreign Key
because of referential integrity.
31. What command do we use
to rename a db? sp_renamedb ‘oldname’ , ‘newname’
32. What is the difference
between a HAVING CLAUSE and a WHERE CLAUSE?
Having Clause is basically used only with the GROUP BY function in
a query. WHERE Clause is applied to each row before they are part of the GROUP
BY function in a query.
33. What do you mean by
COLLATION?
Collation is basically the sort order. There are three types of
sort order Dictionary case sensitive, Dictonary - case insensitive and Binary.
34. What is a Join in SQL
Server? Join actually puts data from two or more tables into a single
result set.
35. When do you use SQL
Profiler?
SQL Profiler utility allows us to basically track connections to
the SQL Server and also determine activities such as which SQL Scripts are
running, failed jobs etc..
36. What is a Linked
Server?
Linked Servers is a concept in SQL Server by which we can add
other SQL Server to a Group and query both the SQL Server dbs using T-SQL
Statements.
37. Can you link only other
SQL Servers or any database servers such as Oracle?
We can link any server provided we have the OLE-DB provider from
Microsoft to allow a link. For Oracle we have a OLE-DB provider for oracle that
microsoft provides to add it as a linked server to the sql server group.
38. Which stored procedure
will you be running to add a linked server? sp_addlinkedserver, sp_addlinkedsrvlogin
39. What are the OS
services that the SQL Server installation adds?
MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution
transac co-ordinator)
40. Can you explain the
role of each service?
SQL SERVER - is for running the databases SQL AGENT - is for
automation such as Jobs, DB Maintanance, Backups DTC - Is for linking and
connecting to other SQL Servers
41. What are the
authentication modes in SQL Server?
Windows mode and mixed mode (SQL & Windows).
42. 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.
43. What
cursor type do you use to retrieve multiple recordsets? Explicit Cursor.
Note : To retrieve Singe record we use Implicit Cursor
44. What
is the difference between "translate" and "replace"?
eg : replace('abcabccab',
'abc', 'def') = defdefcab
eg :
translate('abcabccab', 'abc', 'def') = defdeffde
45. How
to create a database link ?
Mainly used to access
remote database.
CREATE [SHARED][PUBLIC]
DATABASE LINK <link_name>
[CONNECT TO
CURRENT_USER]
[USING <connect_string>]
OR
CREATE [SHARED][PUBLIC]
DATABASE LINK <link_name>
[CONNECT TO
<user> IDENTIFIED BY <password>]
[AUTHENTICATED BY
<user> IDENTIFIED BY <password>]
[USING
<connect_string>]
46.
What is the difference between a local and a global variable?
A local temporary table exists only
for the duration of a connection or, if defined inside a compound
statement, for the duration of the
compound statement.
A global temporary table remains in
the database permanently, but the rows exist only within a given
connection. When connection are
closed, the data in the global temporary table disappears. However,
the table definition remains with the
database for access when database is opened next time.
47.
How to know which index a table is using? SELECT table_name,index_name FROM user_constraints
48.
How to copy the tables, schema and views from one SQL server to another?
Microsoft SQL Server 2000 Data
Transformation Services (DTS) is a set of graphical tools and
programmable objects that lets user
extract, transform, and consolidate data from disparate sources
into single or multiple destinations.
49.
What is Self Join?
This is a particular case when one
table joins to itself, with one or two aliases to avoid confusion. A self
join can be of any type, as long as
the joined tables are the same. A self join is rather unique in that it
involves a relationship with only one
table. The common example is when company have a hierarchal
reporting structure whereby one
member of staff reports to another.
50.
What is Cross Join?
A cross join that does not have a
WHERE clause produces the Cartesian product of the tables involved
in the join. 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. The common example is when company wants to combine
each product with a pricing table to
analyze each product at each price.
Which virtual table does a trigger
use?
Inserted and Deleted.
51. SQL
Querys
DML : Insert ,update,Delete ,Select
DDL : Create ,Alter,Drop
DCL : Grant,Revoke,Deny
TCL : Commit,RollBack,Savepoint,SetTransaction
---------------------------------Rename_Database
Start----------------
exec sp_renamedb 'test','e'
create database test
use test
sp_rename 'test','s'
drop database s
exec sp_renamedb 'Reactionlistings','Reactionlistings_Old'
-----------ReName DB-----
EXEC sp_renamedb 'old_name', 'new_name'
-----------ReName Table-----
EXEC sp_rename 'old_name', 'new_name'
-----------ReName Column-----
EXEC sp_rename 'Table_Name.Old_ColumnName', 'New_ColumnName',
'COLUMN'
---------------------------------Rename_Database
End----------------
--------------------DB_ReadOnly_Code
Start-------------------
EXEC sp_dboption 'Database name', 'read only', 'true'
EXEC sp_dboption 'Database name', 'read only', 'False'
--------------------DB_ReadOnly_Code
End-------------------
--create
Database test_replication
use test_replication
Select * from sysobjects where type='u' -- Get User created tables
from Database
Select * from sysobjects where type='TR' -- Get User created
Trigger from Database
Select * from sysobjects where type='V' -- Get User created view
from Database
Select * from sysobjects where type='fn' -- Get User created
Function from Database
---------------Check
Constraint Start------------------
Create Table Test_Check_Constraint(Cid int,Cname
varchar(100),Csalary money Constraint CheckConstraint check(Csalary > 100
and Csalary <500))
---------------Check
Constraint End------------------
Default
Constraint :
Create table Test_Default_Constraint(Did int,Currentdate datetime
constraint Defaultdate default(getdate()))
53
- What is a Self join?- A join created by joining two or more
instances of a same table.
54. What is a view? - If we have
several tables in a db and we want to view only specific columns from specific
tables we can go for views. It would also suffice the needs of security some
times allowing specfic users to see only specific columns based on the
permission that we can configure on the view. Views also reduce the effort that
is required for writing queries to access specific columns every time.
55. What is an
Index?
- 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.
56.What is the
basic difference between - The difference is that, Clustered
index is unique for any given table and we can have only one clustered index on
a table. The leaf level of a clustered index is the actual data and the data is
resorted in case of clustered index. Whereas in case of non-clustered index the
leaf level is actually a pointer to the data in rows so we can have as many
non-clustered indexes as we can on the db.
57. When do we use
the UPDATE_STATISTICS command? - This command is basically used
when we do a large processing of data. If we do a large amount of deletions any
modification or Bulk Copy into the tables, we need to basically update the
indexes to take these changes into account. UPDATE_STATISTICS updates the
indexes on these tables accordingly.
58. What is the use
of DBCC commands? - DBCC stands for database consistency
checker. We use these commands to check the consistency of the databases, i.e.,
maintenance, validation task and status checks.
59.Can you give me
some DBCC command options?(Database consistency check) - DBCC
CHECKDB - Ensures that tables in the db and the indexes are correctly
linked.and DBCC CHECKALLOC - To check that all pages in a db are correctly
allocated. DBCC SQLPERF - It gives report on current usage of transaction log
in percentage. DBCC CHECKFILEGROUP - Checks all tables file group for any
damage.
60. What command do
we use to rename a db? - sp_renamedb ‘oldname’ , ‘newname’
61. What is the
difference between a HAVING CLAUSE and a WHERE CLAUSE? - Having
Clause is basically used only with the GROUP BY function in a query. WHERE
Clause is applied to each row before they are part of the GROUP BY function in
a query.
62. When do you use
SQL
Profiler? - SQL Profiler utility allows us to basically track
connections to the SQL Server and also determine activities such as which SQL
Scripts are running, failed jobs etc..
63. What is a Linked
Server? - Linked Servers is a concept in SQL Server by which
we can add other SQL Server to a Group and query both the SQL Server dbs using
T-SQL Statements.
32. Which stored
procedure will you be running to add a linked server? - sp_addlinkedserver,
sp_addlinkedsrvlogin
64. How do you
troubleshoot SQL Server if its running very slow? - First check the
processor and memory usage to see that processor is not above 80% utilization
and memory not above 40-45% utilization then check the disk utilization using
Performance Monitor, Secondly, use SQL Profiler to check for the users and
current SQL activities and jobs running which might be a problem. Third would
be to run UPDATE_STATISTICS command to update the indexes
65. What is log shipping? Can we
do logshipping with SQL Server 7.0 - Logshipping is a new feature of SQL Server
2000. We should have two SQL Server - Enterprise Editions. From Enterprise
Manager we can configure the logshipping. In logshipping the transactional log
file from one server is automatically updated into the backup database on the
other server. If one server fails, the other server will have the same db and
we can use this as the DR (disaster recovery) plan.
66. What is BCP?
When do we use it? - BulkCopy is a tool used to copy huge
amount of data from tables and views. But it won’t copy the structures of the
same.
67.
What is DataWarehousing?
Subject‐oriented, meaning that the data in
the database is organized so that all the data elements relating to the same
real‐world event or
object are linked together;
Time‐variant, meaning that the changes to
the data in the database are tracked and recorded so that reports can be
produced showing changes over time;
Non‐volatile, meaning that data in the
database is never over‐written
or deleted, once committed, the data is
static,
read‐only, but retained
for future reporting.
Integrated,
meaning that the database contains data from most or all of an organization's
operational applications, and that this data is made consistent.
68. What are different types of Collation
Sensitivity?
Case sensitivity ‐ A and a, B and b, etc.
Accent sensitivity ‐ a and á, o and ó, etc.
Kana Sensitivity ‐ When Japanese kana characters
Hiragana and Katakana are treated differently, it is called Kana sensitive.
Width sensitivity ‐ A single‐byte character (half‐width) and the same character represented as a double‐byte character (full‐width) are treated differently than
it is width sensitive.
69. What is OLTP (Online Transaction
Processing)?
In
OLTP ‐ online transaction processing systems
relational database design use the discipline of data modeling and generally
follow the Codd rules of data normalization in order to ensure absolute data
integrity. 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.
70.
Name 3 ways to get an accurate count of the number of records in a table?
SELECT
* FROM table1
SELECT
COUNT(*) FROM table1
SELECT
rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2
71.
What is the STUFF function and how does it differ from the REPLACE function?
STUFF
function is used to overwrite existing characters. Using this syntax, STUFF
(string_expression, start, length, replacement_characters), string_expression
is the string that will have characters substituted, start is the starting
position, length is the number of characters in the string that are
substituted, and replacement_characters are the new characters interjected into
the string. REPLACE function to replace existing characters of all occurrences.
Using the syntax REPLACE (string_expression, search_string,
replacement_string), where every incidence of search_string found in the
string_expression will be replaced with replacement_string.
72.
What is Service Broker?
Service
Broker is a message‐queuing
technology in SQL Server that allows developers to integrate SQL Server fully
into distributed applications. Service Broker is feature which provides
facility to SQL Server to send an asynchronous, transactional message. it
allows a database to send a message to another database without waiting for the
response, so the application will continue to function if the remote database
is temporarily unavailable.
73.What
are Sparse Columns?
A
sparse column is another tool used to reduce the amount of physical storage
used in a database. They are the ordinary columns that have an optimized
storage for null values. Sparse columns reduce the space requirements for null
values at the cost of more overhead to retrieve nonnull values
74.What
does TOP Operator Do?
The
TOP operator is used to specify the number of rows to be returned by a query.
The TOP operator has new addition in SQL SERVER 2008 that it accepts variables
as well as literal values and can be used with INSERT, UPDATE, and DELETES
statements.
75.
What is CTE?
CTE
is an abbreviation Common Table Expression. A Common Table Expression (CTE) is
an expression that can be thought of as a temporary result set which is defined
within the execution of a single SQL statement. A CTE is similar to a
derived table in that it is not stored as an object and lasts only for the
duration of the query
76.
Which are new data types introduced in SQL SERVER 2008?
The GEOMETRY Type: The GEOMETRY data
type is a system .NET common language runtime (CLR) data type in SQL Server.
This type represents data in a two‐dimensional
Euclidean coordinate system.
The GEOGRAPHY Type: The GEOGRAPHY
datatype’s functions are the same as with GEOMETRY. The difference between the
two is that when you specify GEOGRAPHY, you are usually specifying points in
terms of latitude and longitude.
New Date and Time Datatypes: SQL
Server 2008 introduces four new datatypes related to date and time:
DATE, TIME, DATETIMEOFFSET, and
DATETIME2.
DATE: The new
DATE type just stores the date itself. It is based on the Gregorian calendar
and handles years from 1 to 9999.
TIME: The new
TIME (n) type stores time with a range of 00:00:00.0000000 through
23:59:59.9999999. The precision is allowed with this type. TIME supports
seconds down to 100 nanoseconds. The n in TIME (n) defines this
level of fractional second precision, from 0 to 7 digits of precision.
The DATETIMEOFFSET Type: DATETIMEOFFSET (n)
is the time‐zone‐aware version of a datetime
datatype. The name will appear less odd when you consider what it really is: a
date + a time + a time‐zone
offset. The offset is based on how far behind or ahead you are from Coordinated
Universal Time (UTC) time.
The DATETIME2 Type: It is an
extension of the datetime type in earlier versions of SQL Server. This new
datatype has a date range covering dates from January 1 of year 1 through
December 31 of year 9999. This is a definite improvement over the 1753 lower
boundary of the datetime datatype. DATETIME2 not only includes the larger date
range, but also has a timestamp and the same fractional precision that TIME
type provides
77. How to copy the
tables,Schema and Views from one sql server to another?
There
are multiple ways to do this.
1) “Detach Database” from one server
and “attach Database” to another server
2) Manually script all the objects using
SSMS and run the script on new server.
3) Use Wizard of SSMS
78.How to copy data
from one table to another?
1 Insert Into Select
This is method is used when table is
already created in the database earlier and data is to be inserted into this
table from another table. If columns listed in insert clause and select clause
are same, they are not required to list them.
2 SELECT INTO
This method is used when table is
not created in the database earlier and
needs to be created when data from one table is to be inserted into newly
created table from another table. New table is created with same data types as
selected columns.
79. What does NULL mean?
The value NULL is a very tricky subject in the database world, so don't be
surprised if several applicants trip up on this question.
The value NULL means UNKNOWN; it does not mean '' (empty string). Assuming
ANSI_NULLS are on in your SQL Server database, which they are by default, any
comparison to the value NULL will yield the value NULL. You cannot compare any
value with an UNKNOWN value and logically expect to get an answer. You must use
the IS NULL operator instead.
80. What is the difference between a
return parameter and an OUTPUT parameter?
If
the applicant is able to answer this question correctly, the odds are good that
they have some experience working with stored procedures.
A return parameter is always returned by a stored procedure, and it is meant to
indicate the success or failure of the stored procedure. The return parameter
is always an INT data type.
An OUTPUT parameter is designated specifically by the developer, and it can
return other types of data, such as characters and numeric values. (There are
some limitations on the data types that can be used as output parameters.) You
can use multiple OUTPUT parameters in a stored procedure, whereas you can only
use one return parameter.
50. How can SQL Server Agent Mail
send messages in SQL Server 2005?
SQL Mail through Extended MAPI or Database mail.
SQL Server Agent Mail can be configured to use Database Mail or Extended MAPI.
81. What does the DEALLOCATE statement do
in SQL Server 2005?
Remove a reference to a cursor.
This statement is used to remove cursor references. If this is the last
reference being removed, the cursor's resources are released.
82. What does the CREATE SERVICE
statement do in SQL Server 2005?
This
statement is used to setup new tasks for Service Broker queues.
This statement is ue to create a new Service Broker service that is a set of
tasks that are run on messages
83. 1. What is a Cartesian product? What causes it?
Expected
answer:
A Cartesian product is the result of an unrestricted join of two or more
tables. The result set of a three table Cartesian product will have x * y * z
number of rows where x, y, z correspond to the number of rows in each table
involved in the join. It is causes by specifying a table in the FROM clause
without joining it to another table.
84.
Q: What is Data Mining
A: Data Mining is the process of sifting through extremely large amounts of
Data to find trends or relevant information
85. What is the system function to get the
current user’s user id?
USER_ID().
Also check out other system functions like USER_NAME(), SYSTEM_USER,
SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().
86. What is the difference between text
and image data type?
Text and image. Use text for character data if
you need to store more than 255 characters in SQL Server 6.5, or more than 8000
in SQL Server 7.0. Use image for binary large objects (BLOBs) such as digital
images. With text and image data types, the data is not stored in the row, so
the limit of the page size does not apply.All that is stored in the row is a
pointer to the database pages that contain the data.Individual text, ntext, and
image values can be a maximum of 2-GB, which is too long to store in a single
data row.
87. What is sorting and what is the difference
between sorting & clustered indexes?
The ORDER BY
clause sorts query results by one or more columns up to 8,060 bytes.
This will happen by the time when we retrieve data from database. Clustered
indexes physically sorting data, while inserting/updating the table.
88.Choosing the Right Backup in SQL
Server
SQL Server provides four different methods for
backing up your database:
A full backup makes a complete backup of
your database. You will almost always need to start your backup strategy with a
full backup of your database.
A file backup is useful when your database is so large that a full backup would take
too long.
A transaction log backup creates a copy of
all changes made to the database that are currently stored in the transaction
log.
A differential backup stores all changes
that have occurred to the database since the last full backup.
1.
Fully
Backing Up the Whole Database
BACKUP
DATABASE [dbname] to [backup_device]
For example, BACKUP DATABASE pubs to disk =
'c:\mssql\backup\pubs.bak'
2.
Differential
Backup:
A differential
backup creates a copy of all the changes that have taken place in the database
since the last full backup. The command is the same for a full database backup,
with the addition of one clause:
BACKUP DATABASE [database_name] to disk = 'file_name' with differential
3.
Transaction Log
Backup: This command creates a copy of all the
transactions in the transaction log at the time the backup was started:
BACKUP TRAN [database_name] to disk = [file_name]
4.
Backing Up the
Database One File at a Time
A complete database backup is certainly a powerful and simple option. In the
case of a very large database, however, a complete backup might simply take too
long to be feasible.
For instance,
this command will back up an individual file:
BACKUP DATABASE database_name FILE = file_name TO DISK = 'file_for_backup'
89. Export data to existing EXCEL file from
SQL Server table
insert into
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from
SQLServerTable
90. Export data from Excel to new SQL Server
table
select * into
SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel
8.0;Database=D:\testing.xls;HDR=YES','SELECT * FROM [Sheet1$]')
91. Export data from Excel to existing SQL Server
table
Insert into SQLServerTable Select * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel
8.0;Database=D:\testing.xls;HDR=YES','SELECT * FROM [SheetName$]')
92. Utilize BCP with SQL Server 2000
The
Bulk Copy Program (BCP) is a command-line utility that ships with SQL Server
2000. With BCP, you can import and export large amounts of data in and out of
SQL Server 2000 databases
QueryOutout:
1. Bcp
"Select ID,Description From Veenas.dbo.Item " QueryOut C:\Testing.xls
-c-S"KlientDev04" -Usa -POnline123
2. Bcp
Veenas.dbo.Item Out C:\Testing.xls
-c-S"KlientDev04" -Usa -POnline123
2. Bcp Veenas.dbo.Item in C:\Testing.xls -c-S"KlientDev04"
-Usa -POnline123
Bulk Insert:
1. Bulk Insert Hospitals From
'C:\DepartmentIn.txt' WITH ( FIELDTERMINATOR = ',')
2. BCP
Dynamic.dbo.Hospitals In C:\aa.txt -c -T -t","
3. Bulk
Insert Hospitals From 'C:\DepartmentIn.txt' WITH(FIELDTERMINATOR = ',')
93. Defining custom error messages
To
define a custom error message in SQL Server 2005, you can use the stored
procedure sp_addmessage,
which adds a record to the sys.messages system view. To execute this stored procedure,
you need to provide an error number (which will start above 50000 for
user-defined messages), a severity level, and the error message.
Example:
EXEC
sp_addmessage 50001, 1, N'This message is not that big of a deal. This is not
caught by error handling, and prints this message to the screen.'
BEGIN TRY
RAISERROR (50001,1,1) WITH LOG
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE(), ERROR_NUMBER ()
END CATCH
94. Database snapshots
Database
snapshots was introduced as a new feature in SQL Server 2005
Database snapshots are yet another great new feature in SQL Server 2005.
A database snapshot is a read-only copy of a database that reflects all
database data up to the point in time for which the snapshot is taken.
These database snapshots are good for reporting purposes because no
locks are taken for any queries run in the snapshot database or in the source
database. Snapshots are also useful in a disaster because you can revert your
existing database to an existing snapshot database or restore individual tables
and data as necessary in the event of adverse data manipulation statements.
Example
CREATE DATABASE SnapshotDatabase ON(
NAME = 'SourceDatabase_Data', FILENAME = 'C:\SQLServer\SnapshotDatabase.mdf'
) AS SNAPSHOT OF SourceDatabase
95. What is deadlock in sqlserver
deadlocking
occurs when two user processes have locks on separate objects and each process
is trying to acquire a lock on the object that the other process has. When this
happens, SQL Server identifies the problem and ends the deadlock by
automatically choosing one process and aborting the other process, allowing the
other process to continue. The aborted transaction is rolled back and an error
message is sent to the user of the aborted process. Generally, the transaction
that requires the least amount of overhead to rollback is the transaction that
is aborted.
Here are
some tips on how to avoid deadlocking on your SQL Server:
• Ensure the database design is
properly normalized.
• Have the application access server
objects in the same order each time.
• During transactions, don't allow any
user input. Collect it before the transaction begins.
• Avoid cursors.
• Reduce lock time. Try to develop your
application so that it grabs locks at the latest possible time, and then
releases them at the very earliest time.
• If appropriate, reduce lock
escalation by using the ROWLOCK or PAGLOCK.
• Consider using the NOLOCK hint to
prevent locking if the data being locked is not modified often.
96. What is CoRelated SubQuery?
It is very
similar to sub-queries where the parent query is executed based on the values
returned by sub-quries. but when come’s to co-related subqueries for every
instance of parent query subquery is executed and based on the result of
sub-query the parent query will display the record as we will have refernce of
parent quries in su-queries we call these as corelated subquries. so, we can define co-related sub
query as for every record retrival from the sub query is processed and based on
result of process the parent record is displayed.
The main
difference between SubQuery and co Related subquery is that in subquery child
query excuted first and then parent query will excute.But in Co-Related
subquery main query will excuted first and then Child query .Example Select name from emp where exits(Select
empid,salary from emp1 where emp1.name=emp.name)
97. 1.to
show randaom rows SELECT FirstName,LastName FROM Person.Contact TABLESAMPLE
SYSTEM (1 PERCENT)
98. Select all tables From Database
Select Table_Name from
INFORMATION_SCHEMA.tables where TABLE_TYPE = 'BASE TABLE'
99. Select all indexs From Particular Table EXEC sp_helpindex 'Table_Name'
100. select
* From Sysobjects where type=N'K'
type=N'K'=Display all Primary Keys
type=N'U'=Display all tables Name
type=N'P'=Display all Procedures
Name
type=N'S'=Display all System Tables
Name
type=N'TR'=Display all Triggers Name
101. How do I list the databases on my server?
EXEC sp_databases ,EXEC sp_helpdb,
select * from Master.dbo.SysDatabases
102. How do I show all the primary keys in a
database?
EXEC sp_pkeys '<Table_Name>' , EXEC sp_helpconstraint '<Table_Name>'
103. How do I show all the triggers in a
database?
EXEC sp_helptrigger 'Table_Name'
104.How do I show the columns for a table?
1. SELECT name FROM syscolumns WHERE [id] = OBJECT_ID
('Table_Name')
2. SELECT column name FROM INFORMATION_SCHEMA.COLUMNS WHERE
Table_Name='Table_Name'
105. How do I show the description property of
a column?
EXEC sp_addextendedproperty 'MS_Description', ‘some description', 'user',
dbo, 'table', Table_Name,
'column',column_name
106.How do I show the parameters for a
function or stored procedure?
EXEC
sp_help 'Procedure Name'
107. How do I show the stored procedures in a
database?
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'
108.How do I show the user-defined functions
(UDFs) in a database?
SELECT ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION' ORDER BY ROUTINE_NAME
109.How do I show the views in a SQL Server
database?
Select * FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW'
EXEC
sp_helptext 'view name'
110. Copy Structure of table from one table
and create new table
SELECT
TOP 0 * INTO Exam1 FROM Exam
111. See Database Size (Primary size, log file
size)—Exec sp_spaceused
112. Change Column Name of Table
Exec Sp_Rename
'Exam1.Examear','ExamYear','Column'
Exec Sp_Rename
‘TableName.OldColumnName','NewColumnName','Column'
113. To see
the Description of the Table and its Data types
1. Sp_Help Exam1,exec Sp_Help TableName
114. Find number of columns in a table
select
count(1) from syscolumns SC,Sysobjects SO where
sc.id =so.id and so.name =
‘TableName’
115. What is @@Identity in sqlserver2000?
@@Identity returns the last inserted identity
value.
116. Information_Schema
Select *
from Information_Schema.Table_privileges –Display table privileges
Select *
from Information_Schema.Tables -- Display tables in particular database
Select *
from Information_Schema.views --Display
Viws in paricular Datbase
Select *
from Information_Schema.Check_Constraints—Get the constraints information
Select *
from Information_Schema.Columns—Get the Columns informations
Select *
from Information_Schema.Domains-- Get the Domains informations
Select *
from Information_Schema.Key_Column_Usage –Display the column usage levels
Select *
from Information_Schema.Parameters -- display the Parameters of procedures
Select *
from Information_Schema.Routine_Columns -- Get the procedures informations
Select *
from Information_Schema.Routines Where Routine_Type='Procedure'
Select *
From Sys.Triggers -- Get Triggers info
from particular database
Select *
From Sys.tables -- Get ables info from
particular database
117.RowNumber Example
SELECT
ROW_NUMBER() OVER (ORDER BY ID) AS ROW From TableName
118.Linked Server
sp_addlinkedserver 'test_link','test_link', 'SQLOLEDB',NULL,NULL,'DRIVER={SQL Server};SERVER=server2;UID=sa;PWD=sa;','mydatabase'
119.
Different Types of Lock in SQL Server
There
are different types of lock in SQL Server 2000 and 2005. These locks are
applied in different situations. Here is the list of locks and the situation
for the locks.
SHARED - This
lock is applied for read operation where the data is not updated. A good
example would be the select statement.
UPDATE – This
locked on those resources that can be updated. This lock prevents the common
form of dead lock that occurs when multiple sessions are locking the data so
that they can update it later.
EXCLUSIVE - Used
for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures
that multiple updates cannot be made to the same resource at the same time.
INTENT - Used to
establish a lock hierarchy. The different types of intent locks are: intent
shared, intent exclusive, and shared with intent exclusive.
SCHEMA - Used
when an operation dependent on the schema of a table is executing. The
different types of schema locks are: schema modification and schema stability.
BULK UPDATE – This
lock is applied when there is a bulk copying of data and the TABLOCK is applied
KEY RANGE -
Protects the range of rows read by a query when using the serializable
transaction isolation level. Ensures that other transactions cannot insert rows
that would qualify for the queries of the serializable transaction if the
queries were run again.
120. Importing
files in SQL Server 2005 using OPENROWSET
OPENROWSET
has new BULK features which I'll definitely be taking advantage of in the
future. With OPENROWSET you'll be able
to return a result set from a file based on options in a format file (similar
to bcp or BULK INSERT), or also import a file in its entirety as a single
varbinary(max), varchar(max), or nvarchar(max) data type value.
It is the file import functionality that I'm
really looking forward to. This is built-in functionality, so you don't have to
depend on external applications to import external files into your SQL Server
tables. You use the SINGLE_BLOB,
SINGLE_CLOB, or SINGLE_NCLOB to tell SQL Server what kind of single-row,
single-column data is being read.
INSERT INTO REGION
(ID, REGION, DATA)
SELECT 2 AS ID, 'North America' AS REGION,* FROM
OPENROWSET( BULK
'C:\DATA\NorthAmerica.TXT',SINGLE_CLOB) AS MYTABLE
121. Creating
Partitions
Partitioning
lets you split a table across multiple storage units called filegroups, based
on a user specification
To
partition a table or index, you perform the following tasks:
1.
Create a partition function.
2.
Create a partition scheme mapped to a partition function.
3.
Create the table or index on the partition scheme.
ON PRIMARY ( NAME = db_dat,FILENAME =
'c:\test\db.mdf', SIZE = 2MB),
FILEGROUP FG1 ( NAME = FG1_dat,
FILENAME = 'c:\test\FG1.ndf', SIZE = 2MB),
FILEGROUP FG2 ( NAME = FG2_dat,FILENAME
= 'c:\test\FG2.ndf',SIZE = 2MB),
FILEGROUP FG3 ( NAME =
FG3_dat,FILENAME = 'c:\test\FG3.ndf',SIZE = 2MB),
FILEGROUP FG4 ( NAME =
FG4_dat,FILENAME = 'c:\test\FG4.ndf',SIZE = 2MB)
LOG ON ( NAME = db_log, FILENAME =
'c:\test\log.ndf', SIZE = 2MB,FILEGROWTH = 10% );
CREATE PARTITION FUNCTION partfunc
(int) AS RANGE LEFT FOR VALUES (1000, 2000, 3000, 4000, 5000);
PARTITION partfunc TO ([FG1], [FG2],
[FG3], [FG4], [FG5], [FG6])
CREATE PARTITION SCHEME partscheme AS
PARTITION partfunc TO
([FG1], [FG2], [FG3], [FG4], [FG5],
[FG6])
CREATE TABLE dbo.CustomerAddress (CustomerAddressID int IDENTITY(1,1) PRIMARY
KEY CLUSTERED,AddressTypeID tinyint NOT NULL, PrimaryAddressFlag bit NOT
NULL,AddressLine1 varchar(30) NOT NULL, AddressLine2 varchar(30)
NULL,AddressLine3 varchar(30) NULL,
City varchar(50) NOT
NULL,StateProvinceID int NULL, PostalCode char(10) NULL,CountryID int NULL)
ON partscheme(CustomerAddressID);
122. What is User Defined Functions?
User-Defined Functions allow to define its own T-SQL functions that can accept
0 or more parameters and return a single scalar data value or a table data
type.
What kind of User-Defined Functions can be created?
There are three types of User-Defined functions in SQL Server 2000 and they are
Scalar, Inline Table- Valued and Multi-statement Table-valued.
Scalar User-Defined Function
A Scalar user-defined function returns one of the scalar data types. Text,
ntext, image and timestamp data types are not supported. These are the type of
user-defined functions that most developers are used to in other programming
languages. You pass in 0 to many parameters and you get a return value.
Inline Table-Value User-Defined Function
An Inline Table-Value user-defined function returns a table data type and is an
exceptional alternative to a view as the user-defined function can pass
parameters into a T-SQL select command and in essence provide us with a
parameterized, non-updateable view of the underlying tables.
Multi-statement Table-Value User-Defined Function
A Multi-Statement Table-Value user-defined function returns a table and is also
an exceptional alternative to a view as the function can support multiple T-SQL
statements to build the final result where the view is limited to a single
SELECT statement. Also, the ability to pass parameters into a TSQL select
command or a group of them gives us the capability to in essence create a
parameterized, non-updateable view of the data in the underlying tables. Within
the create function command you must define the table structure that is being
returned. After creating this type of user-defined function, It can be used in
the FROM clause of a T-SQL command unlike the behavior found when using a
stored procedure which can also return record sets.
123. What is the STUFF function and how does it differ from the
REPLACE function?
STUFF function to overwrite existing characters. Using this syntax,
STUFF(string_expression, start, length, replacement_characters),
string_expression is the string that will have characters substituted, start is
the starting position, length is the number of characters in the string that
are substituted, and replacement_characters are the new characters interjected
into the string.
REPLACE function to replace existing characters of all occurance. Using this
syntax REPLACE(string_expression, search_string, replacement_string), where
every incidence of search_string found in the string_expression will be
replaced with replacement_string.
124. What is Rules and UserDataTypes?
You define check constraints within the
table definition and cannot reuse them. Rules
provide the same functionality as check
constraints, except that you create them as a
separate object.
Because rules are not associated with a
specific table or column when you create
them, they cannot reference columns or
tables in their definition. Instead, you use
variables as placeholders. Rules
provide the same features and complex comparisons
via AND, OR, and NOT as check
constraints and allow pattern matching
CREATE RULE EmailValidator
AS
@value like '%@%.[a-z][a-z][a-z]' or
@value like '%@%.[a-z][a-z].[a-z][a-z]';
Creating User-Defined Types
User-defined types (UDTs) have two
purposes in SQL Server 2005. You can use Transact-SQL-based UDTs to enforce
consistency in table definitions, and you can use Common Language Runtime (CLR)
UDTs to create new data types that do not exist in SQL Server. In this lesson,
you see how and when to creat
125. Advantages of Stored procedures?
· Stored procedure can reduced network
traffic and latency, boosting application performance.
· Stored procedure execution plans can be reused, staying cached in SQL
Server's memory,
1. Execution plan retention and reuse
2. Query auto-parameterization
3. Encapsulation of business rules and
policies
4. Application modularization
5. Sharing of application logic between
applications
6. Access to database objects that is
both secure and uniform
7. Consistent, safe data modification
8. Network bandwidth conservation
9. Support for automatic execution at
system start-up
10. Enhanced hardware and software
capabilities
11. Improved security
12. Reduced development cost and
increased reliability
13. Centralized security,
administration, and maintenance for common routines
reducing server overhead.
· Stored procedures help promote code reuse.
· Stored procedures can encapsulate logic. You can change stored procedure code
without affecting clients.
· Stored procedures provide better security to your data.
126.
Write a SQL Query to find first Week Day of month?
SELECT DATENAME(dw, DATEADD(dd, -
DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay
127.
How to find 6th highest salary from Employee table
SELECT
TOP 1 salary FROM (SELECT DISTINCT TOP 6 salary FROM employee
ORDER BY salary DESC) a ORDER BY
salary
128.
How can I enforce to use particular index?
You can use index hint
(index=index_name) after the table name. SELECT au_lname FROM authors
(index=aunmind)
129.
What are the differences between UNION and JOINS?
A
join selects columns from 2 or more tables. A union selects rows.
130.
What is the Referential Integrity?
Referential
integrity refers to the consistency that must be maintained between primary and
foreign keys, i.e. every foreign key value must have a corresponding primary
key value
131.
What is the use of SCOPE_IDENTITY() function?
Returns the most recently created identity value for the tables in the
current execution scope.
132.
What are the different ways of moving data between servers and databases in SQL
Server?
There
are lots of options available, you have to choose your option depending upon
your requirements. Some of the options you have are: BACKUP/RESTORE, detaching
and attaching databases, replication, DTS, BCP, logshipping, INSERT...SELECT,
SELECT...INTO, creating INSERT scripts to generate data.
103. Define candidate key, alternate key,
composite key.
A candidate key is one that can
identify each row of a table uniquely. Generally a candidate key becomes the
primary key of the table. If the table has more than one candidate key, one of
them will become the primary key, and the rest are called alternate keys. A key
formed by combining at least two or more columns is called composite key
133. What are defaults? Is there a column
to which a default can’t be bound?
A default is a value that will be
used by a column, if no value is supplied to that column while inserting data.
IDENTITY columns and timestamp columns can’t have defaults bound to them. See
CREATE DEFAULT in books online.
134. What is RAID and what are different
types of RAID configurations?
RAID stands for Redundant Array of
Inexpensive Disks, used to provide fault tolerance to database servers. There
are six RAID levels 0 through 5 offering different levels of performance, fault
tolerance.
135.what
are the DBCC commands that you commonly use for database maintenance?
DBCC CHECKDB, DBCC CHECKTABLE, DBCC
CHECKCATALOG, DBCC CHECKALLOC, DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC
SHRINKFILE etc. But there are a whole load of DBCC commands which are very
useful for DBAs
136. What is Data Integrity and it's
categories ?
Enforcing data integrity ensures the
quality of the data in the database. 1) Entity integrity
2) Domain integrity
3) Referential integrity
4) User-defined integrity
Entity
Integrity: Entity integrity defines a row as a
unique entity for a particular table. Entity integrity enforces the integrity
of the identifier column(s) or the primary key of a table (through indexes,
UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties).
Domain
Integrity: Domain integrity is the validity of
entries for a given column. You can enforce domain integrity by restricting the
type (through data types), the format (through CHECK constraints and rules), or
the range of possible values (through FOREIGN KEY constraints, CHECK
constraints, DEFAULT efinitions, NOT NULL definitions, and rules).
Referential
Integrity: Referential integrity preserves the
defined relationships between tables when records are entered or deleted.
• Adding records to a related table
if there is no associated record in the primary table.
• Changing values in a primary table
that result in orphaned records in a related table.
• Deleting records from a primary
table if there are matching related records.
User-Defined: Integrity User-defined integrity
allows you to define specific business rules that do not fall into one of the
other integrity categories. (all column- and table-level constraints in CREATE
TABLE, stored procedures, and triggers).
137. What is the use of DBCC commands?
databases, i.e., maintenance,
validation task and status checks.
DBCC CHECKDB - Ensures that tables in
the db and the indexes are correctly linked. And
DBCC CHECKALLOC To check that all
pages in a db are correctly allocated.
DBCC SQLPERF - It gives report on
current usage of transaction log in percentage.
DBCC CHECKFILEGROUP - Checks all
tables file group for any damage.
138.
What is COMMIT & ROLLBACK statement in SQL ?
Commit statement helps in termination of
the current transaction and do all the changes that occur in transaction
persistent and this also commits all the changes to the database.COMMIT we can
also use in store procedure.
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.
139. What is
DTS in SQL Server ?
If
a organization is big then it is also there that there is multiple option to
store data some people are using EXCEL some are using ACCESS and some of they
are using SQL SERVER and in some other format also but there a problem is arise
that how to merge that data into one format there is diffrent tool are there
for doing this funtion. One of product of SQL SERVER-2000 DTS helps in this
problem it provides a set of tool from that tool we can customise are database
acording to our need DTSRun is a command-prompt utility used to execute
existing DTS packages.
140. Extended stored procedures
An extended stored procedure is a C
or C++ DLL that can be called from Transact-SQL using the same syntax as
calling a Transact-SQL stored procedure. Extended stored procedures are a way
to extend the capabilities of Transact-SQL to include any resources or services
available to Microsoft Win32 applications.
For example, this command registers
the function xp_sample, located in xp_sample.dll, as a SQL Server extended
stored procedure:
sp_addextendedproc
'xp_sample','xp_sample.dll'
sp_dropextendedproc
'xp_sample','xp_sample.dll'
141. What is
Recovery Model in sqlserver?
A
recovery model is a database configuration option that controls how
transactions are
logged,
whether the transaction log is backed up, and what restore options are
available
for
the database. The recovery model you choose for your database has both
datarecovery
implications
and performance implications, based on the logging the recovery
model
performs or doesn’t perform.
In the Full
recovery model, the database engine logs all operations onto the
transaction
log,
and the database engine never truncate the log. The Full recovery model lets
you restore a database to the point of failure (or to an earlier point in
time
in SQL Server 2005 Enterprise Edition).
In the Simple
recovery model, the database engine minimally logs most operations
and
truncates the transaction log after each checkpoint. In the Simple recovery
model,
you cannot back up or restore the transaction log. Furthermore, you cannot
restore
individual data pages.
In the Bulk-Logged
recovery model, the database engine minimally logs bulk operations
such
as SELECT INTO and BULK INSERT. In this recovery model, if a log
backup
contains any bulk operation, you can restore the database to the end of
the
log backup, not to a point in time. The Bulk-Logged recovery model is
intended
to be used only during large bulk operations.
ALTER
DATABASE AdventureWorks SET RECOVERY BULK_LOGGED
142.
How to create Windows Login
CREATE LOGIN [Domain\User] FROM
WINDOWS
The
syntax to create a SQL Server login is
CREATE LOGIN login_name WITH
PASSWORD='password'
143.Creating
Queries That Use Full-Text Search
If your database contains many
columns that use string data types such as VARCHAR
or NVARCHAR, you might find that
searching these columns for data by using the
Transact-SQL = and LIKE operators
does not perform well. A more efficient way to
search text data is to use the SQL
Server FTS capabilities.
To do full-text searching, you first
must enable full-text indexes for the tables you
want to query. To query a full-text
index, you use a special set of functions that differ
from the operators that you use to
search other types of data. The main functions for
full-text search are CONTAINS and
FREETEXT.
The CONTAINS function searches for
exact word matches and word prefix matches.
For instance, the following
SELECT *FROM Person.Address WHERE CONTAINS(AddressLine1,
'"Stone*"') Note that you must also use double quotes if you use the
prefix identifier. If the double quotes are not included, the string will be
searched as an exact match, including
the prefix identifier.
144.CAST/CONVERT
The CAST and CONVERT functions let you convert between data types.
CONVERT is especially useful because it lets you change formatting when
converting certain types (for example, datetime) to strings.
145. What is a transaction and ACID
property?
A transaction is an atomic unit of work that must be completed in its
entirety. The transaction succeeds if it committed and fails if it is aborted.
Transactions have four essential properties: atomicity, consistency, isolation,
and durability (known as the ACID properties).
Atomicity: The work cannot be broken into
smaller parts. Although a transaction might contain many SQL statements, it
must be run as all-or-nothing proposition, which means that, if a transaction
is only partially complete when an error occurs, the work reverts to its state
prior to the start of the transaction.
Consistency: A transaction must operate on a
consistent view of the data and also leave the data in a consistency state. Any
work in progress must not be visible to other transactions until the
transaction has been committed.
Isolation: A transaction should appear to be
running by itself, the effects of other ongoing transactions must be invisible
to this transaction, and the effects of this transaction must be invisible to
other ongoing transaction.
Durability: When the transaction is committed,
it must be persisted so it is not lost in the event of a power failure. Only
committed transaction are recovered during power-up and crash recovery;
uncommitted work is roll back.
146. String Functions in SQL Server
1)
CHARINDEX: CHARINDEX
(expression1 ,expression2 [ , start_location ] )
expression1 Is sequence of characters
that to be found in expression2. start_location is optional parameter. This
will be useful if you want to search the expression1 in expression2 from a
specific location.
In simple words this is used to find
the starting position of a string (i.e. expression1) with in another string
(expression2).
SELECT
CHARINDEX('Kadiyala', 'Vijaya Kadiyala') AS String_position
Output : 8
2)
DIFFERENCE: DIFFERENCE(Expression1,
expression2)
This function is used to find the
similarity between two expressions. This is very similar to SOUNDEX in fact
this is a wraper over SOUNDEX. This function returns value from 0 to 4. 0 means
no similarity and 4 means very close similarity. In simple words this is used
to search for Phonetically Similar Data.
SELECT
DIFFERENCE('Vijaya','KADIYALA') AS SIMILARITY
Output:2
3)
PATINDEX: PATINDEX (expression1 ,expression2)
expression1 Is sequence of characters
that to be found in expression2.
In simple words this is used to find
the starting position of a string (i.e. expression1) with in another string
(expression2). So what is the difference between PATINDEX and CHARINDEX? Well
PATINDEX can be used with Wild characters but not with CHARINDEX. This is
similar to LIKE operation. The big advantage with this is you can even use
Regular Expressions.
SELECT PATINDEX('%y_l%', 'Vijaya
Kadiyala') AS String_position
Output:12
4)
REPLICATE: REPLICATE (expression ,No_Of_Times)
This function is used to copy the
same string again and again based on No_Of_Times parameter.
SELECT REPLICATE(a',3) as REP
Output:aaa
5)
LTRIM: LTRIM(char_expression)
This LTRIM (i.e. LeftTRIM) function
is used to trim the spaces or blanks on the left side of the string.
SELECT ( '*' + LTRIM (' STEVE ') +
'*') AS ltrim1
Output:*STEVE *
6)
RTRIM: RTRIM(char_expression)
This RTRIM (i.e. RightTRIM) function is used to trim the spaces or
blanks on the Right side of the string.
SELECT ( '*' + L TRIM (' STEVE ')
+ '*') AS ltrim1
7)
SUBSTRING:
SUBSTRING(expression,start_integer,length_integer)
The SUBSTRING() function is bascailly
returns a part of the string based on the starting point to
specified number of characters.
Ex: SELECT SUBSTRING(‘alagesan’,1,3) as
First_3 FROM Employee
OutPut:’ala’
8)
STUFF :
STUFF(char_expression1,start_integer,length_integer,char_expression2)
The STUFF() function is very similar
to REPLACE function where it is used replaces certain characters not based on
the pattern but based on the starting position and length. It replaces
characters in char_expression1 based on length_integer starting from
start_integer with the char_expression2
Ex: SELECT STUFF(‘alagesan’,1,5,’a’) as
First_3 FROM Employee
OutPut:’asan’
147.What
are Data commands in sql server?
DML
DML is abbreviation of Data
Manipulation Language. It is used to retrieve, store, modify, delete, insert
and update data in database Examples: SELECT, UPDATE, INSERT statements
DDL
DDL is abbreviation of Data
Definition Language. It is used to create and modify the structure of database
objects in database. Examples: CREATE, ALTER, DROP statements
DCL
DCL is abbreviation of Data Control
Language. It is used to create roles, permissions, and referential integrity as
well it is used to control access to database by securing it.Examples: GRANT,
REVOKE statements
TCL
TCL is abbreviation of Transactional
Control Language. It is used to manage different transactions occurring within
a database. Examples: COMMIT, ROLLBACK statements
148. What is the difference between UNION
ALL Statement and UNION ?
Answer:- The main difference between
UNION ALL statement and UNION is UNION All statement is much faster than
UNION,the reason behind this is that because UNION ALL statement does not look
for duplicate rows, but on the other hand UNION statement does look for
duplicate rows, whether or not they exist.
149. What is Database mirroring ?
Database mirroring is a new SQL
Server 2005 technology available for review for increasing database availability.
Database mirroring transfers transaction log records directly from one server
to another and can quickly fail over to the standby server. You can code client
applications to automatically redirect their connection information, and in the
event of a failover, automatically connect to the standby server and database.
Fast failover with minimal data loss has traditionally involved higher hardware
cost and greater software complexity. Database mirroring, however, can fail
over quickly with no loss of committed data, does not require proprietary
hardware, and is easy to set up and manage
Some important items to note about
database mirroring:
• The principal database must be in
the FULL recovery model. Log records that result from bulk-logged
operations
cannot be sent to the mirror database.
•The mirror database must be
initialized from a restore of the principal database with NORECOVERY, followed
by
restores in sequence of principal transaction log backups.
•The mirror database must have the
same name as the principal database.
150.
Difference Between IN and EXISTS
IN:Returns Collection f valuse if a
specified value matches any value in a subquery or a list.
Exists:Returns true if a subquery
contains any rows.
151. What is the system function to get the
current user's user id?
USER_ID(). Also check out other
system functions like USER_NAME(),
SYSTEM_USER, SESSION_USER,
CURRENT_USER, USER, SUSER_SID(), HOST_NAME().
152. What is the pivot operator in MS SQL
Server2005? What is the use of it and how do we use it?
The PIVOT operator rotates rows into
columns, optionally performing aggregations or other mathematical calculations
along the way. It widens the input table expression based on a given pivot
column and generates an output table with a column for each unique value in the
pivot column. The UNPIVOT operator performs an operation opposite to the one
PIVOT performs; it rotates columns into rows. The UNPIVOT operator narrows the
input table expression based on a pivot column.
For eg:
SELECT CAST(YEAR(SaleDate) AS
VARCHAR(4)), BigScreen, PoolTable, Computer
FROM SalesHistory PIVOT
( SUM(SalePrice) FOR Product
IN(BigScreen, PoolTable, Computer)) AS p
153. What are data objects?
Tables,Views,clusters,index,cluster
and sequences are callled dataobjects for a database.
154. What is the difference between the
full, simple and bulk log recovery models?
SIMPLE: Uses full or differential copies of
the database. Truncates the transaction logs automatically. Does not backup the
transaction log file(s).Changes made after the last backup cannot be restored
(they are lost).
FULL: Includes both database and
transaction log backups. The transaction logs must be manually truncated.
Recovery can be don to a specific point in time. Only transactions in process
at the time of the backup will be lost (those that are rolled back).
BULK: Includes both database and
transaction log backups, but individual transaction data is not logged for bulk
transactions. Transaction logs must be truncated manually. Restore cannot be
done to a specific point in time.
154. How to display the table data in XML
format using SQL Server.
SELECT * FROM customer FOR XML RAW
SELECT * FROM customer FOR XML AUTO
SELECT * FROM customer FOR XML AUTO,
ROOT('customers')
SELECT * FROM customer FOR XML AUTO,
ELEMENTS,
The
FOR XML Clause :
The For XML clause does most of the
work for us and it can be used in various ways. The basic syntax of the query
is:
SELECT * FROM customer FOR XML
[output mode], [display keyword]RAW and AUTO output modes
These are two major used output modes
which can further be customized by the display keywords to achieve most
structures of XML derived from a particular table. The RAW mode takes each
element as a row element and all the column values are taken as the attribute of
that row element. On the other hand the AUTO mode outputs each element as the
table name and the column values as attributes of these elements. Below is an
example of the XML Raw and For XML Auto query:
SELECT * FROM customer FOR XML RAW
155. How will you copy the structure of a
table without copying the data?
Select * into newtablename from
oldtablename where 1= 0
156. What is the difference between
"translate" and "replace"?
Replace replace every instence of character
with character sting by the given charator of string. this work for entire word
and pattern,.
Translate replace the one charecte at a
time.Translating the nth character match with the nth character with the
replacemnt string .
157. Difference between Store Procedure
and Trigger?
Triggers are used to initiate a
particular activity after fulfilling certain condition.It need to define and
can be enable and disable according to need.
Procedure we need execute manually,
trigger fired whn the event is occured(insert ,delete,update,..etc) .
One more thing when we use truncate
command the trigger ll not fire,
158. New features in SQL SERVER 2008 for
DATABASE developers
1.Increase the precision of storing
and managing DATE and TIME information.
2.Store semi-structured and sparsely
populated sets of data efficiently, using Sparse Columns.
3.New fully integrated Full-Text
Indexes enable high-performance, scalable, and manageable Full-Text Indexing.
4.Create large User-Defined Types and
User-Defined Aggregates greater than 8 KB.
5.Pass large amounts of data easily
to functions or procedures using new Table-Value Parameters.
6.Perform multiple operations
efficiently with the new MERGE command.
7.Model hierarchical data, such as
org charts, or files and folders, using the new HierarchyID data type.
8.Build powerful location-aware
applications, using SQL Server’s new standards-compliant spatial data types
and
spatial indexing capabilities.
9.Manage files and documents
efficiently with full SQL Server security and transaction support, using the
powerful
new FILESTREAM data type.
10.Easily identify dependencies
across objects and databases, using New Dependency Management.
11.Experience faster queries and
reporting with Grouping Sets through powerful ANSI standards-compliant
extensions
to the GROUP BY clause.
159. What is SSIS?
According to Microsoft SQL Server
Integration Services, “(SSIS) is an effective set of tools for both the
traditional demands of ETL operations, as well as for the evolving needs of
general purpose data integration.” In short, it is the next version of DTS
(Data Transformation Services). ETL stands for Extract, Transform and Loading.
In short it is a data migration tool that is flexible, fast, and has scalable
architecture that enables effective data integration in current business
environments
Experience efficient,
high-performance data access, using new Filtered Indexes for subsets of data.
160
What are the Security Enhancements in SQL Server 2005?
SQL Server 2005 enables administrators to manage permissions at a
granular level.
1. In the new SQL Server 2005, we can specify
a context under which statements in a module can execute.
2.SQL Server 2005 clustering supports Kerberos
authentication against a SQL Server 2005 virtual server.
3. Administrators can specify Microsoft
Windows-style policies on standard logins so that a consistent policy
is
applied across all accounts in the domain.
4.SQL Server 2005 supports encryption
capabilities within the database itself, fully integrated with a key
management infrastructure. By default, client-server communications are
encrypted.
161.What
is Information Schema in SQL Sever 2005?
Information Schema is the part of the
SQL- 92 standard which exposes the metadata of the database. In SQL server, a
set of views are created in each of the databases which exposes the metadata of
the database. The information schema is kept in a separate schema – information
schema – which exists in all databases, but which is not included in the search
path by default. For more information regarding Information schema please read
this article.
162. What is Full Text Search? How does it
get implemented in SQL server 2005?
Full-text
search allows fast and flexible indexing for keyword-based query of text data
stored in a Microsoft SQL Server database. In contrast to the LIKE predicate
which only works on character patterns, full-text queries perform linguistic
searches against this data, by operating on words and phrases based on rules of
a particular language.
163. What is Database Partitioning in SQL
Server 2005?
SQL
Server 2005 provides a new capability for the partitioning of tables across
file groups in a database. Partitioning a database improves performance and
simplifies maintenance. By splitting a large table into smaller, individual
tables, queries accessing only a fraction of the data can run faster because
there is less data to scan
164.What
are the New Data types introduced in SQL Server 2005?
XML
Data type,VARCHAR (MAX),NVARCHAR (MAX),VARBINARY (MAX)
165. What are Checkpoint in SQL Server ?
When we done operation on SQL SERVER
that is not commited directly to the database.All operation must be logged in
to Transaction log files after that they should be done on to the main
database.CheckPoint are the point which alert Sql Server to save all the data
to main database if no check point is there then Log files get full we can use
Checkpoint command to commit all data in the SQL SERVER.When we stop the SQL
Server it will take long time because Checkpoint is also fired.
165. What is the recommended way to send
mail from SQLAgent in SQL Server 2005?
Database Mail
You can use either Database Mail or
SQLMail with SQL Agent in SQL Server 2005. However since SQLMail will be
removed, it is recommended that you use Database Mail.
166. What does the Queue Reader Agent do
in SQL Server 2005 replication?
This agent reads the subscriber logs
and moves changes back to the publisher.
This agent is used when the queued
update model is chosen with transactional replication. It moves changes from
the subscribers back to the publishers.
167. What are the three possible functions
of the plus (+) operator in SQL Server 2005, the base installed T-SQL?
Add, string concatenation, unary plus
The three functions are Add, String
Concatenation, and Unary Plus.
168. What does the CEILING() function do?
Returns the smallest integer greater
than or equal to the value passed in.
CEILING() returns the smallest
integer that is great than or equal to the value passed in.
169. What is row versioning in SQL Server
2005?
Row versioning keeps a copy of each
row for use by applications or transactions to prevent readers from being
blocked by writers.
Row versioning is a method whereby
the database engine keeps a copy of a row's data as it existed before the start
of a transaction for queries to read this data and reduce locking contention if
they are configured.
170. Which of the following columns can be
indexed with SQL Server 2005 Full-Text Search?
char, varchar, nvarchar, and
varbinary, text, ntext, and image
All character columns, char, varchar
and nvarchar columns including max, text and ntext, and image columns are valid
for full-text searching.
171. What does the ERROR_PROCEDURE()
function return?
The name of the stored procedure that
caused an error.
The ERROR_PROCEDURE() function is
placed in the catch block of a TRY..CATCH construct and returns the name of the
procedure that caused an error to be thrown.
172. What does @@options return?
The current SET options for the
connection.
The @@options variable returns a list
of the options set for the current connection. This is returned as integer and
each bit in the integer represents an option.
173.What
does SEND do in SQL Server 2005?
Sends a service broker message using
a conversation.
SEND is used to send a message on an
existing conversation in the Service Broker architecture.
174. What can be used to ensure that a
field in a table only accepts a certain range of values?
This question can be answered a
couple of different ways, but only one answer is a "good" one. The
answer you want to hear is a Check constraint, which is defined on a database
table that limits the values entered into that column. These constraints are
relatively easy to create, and they are the recommended type for enforcing
domain integrity in SQL Server.
Triggers can also be used to restrict
the values accepted in a field in a database table, but this solution requires
the trigger to be defined on the table, which can hinder performance in certain
situations. For this reason, Microsoft recommends Check constraints over all
other methods for restricting domain integrity.
178.Sample
Queries:
1. Create Non Clustered Index
Create NONCLUSTERED
INDEX [NonClustRun] ON
[dbo].[MedicalJobHeader]
2. Disable Indexes
ALTER INDEX
[NonClustRun] ON [dbo].[MedicalJobHeader]
DISABLE
3. Sp_Help procedures
Exec Sp_HelpIndex ILR_AIM(Object-Table,Pro,Func,constraints
Details)---- Get details
Exec Sp_SpaceUsed 'ILR_AIM'
exec sp_addtype 'datetime', Datetime
4.General Queries(String
Functions)
Select Replace('Alagesan','ala','M') output: Mgesan
Select Stuff('Alagesan',1,4,'R') output: Resan
Select Substring('Alagesan',1,3) output: Ala
Select Char(65) output: 65
Select Ascii('A') output: 65
Select Top 0 * Into Customer1 From Customer –Create new table from exists without
data
ALTER AUTHORIZATION ON OBJECT::LandingInformatics
TO sa –persmission to database diagrams
ALTER AUTHORIZATION
ON DATABASE::LandingInformatics TO
sa
Exec Sp_Configure 'clr enabled', 1
5.
Select Data from another server
Select
* From OPENDATASOURCE( 'SQLOLEDB', 'Data
Source=KlientDev02;User ID=sa;Password=Online123' ).FreightController.dbo.Employee
6.Enable and disable
triggers
Enable Trigger
Tr_RegNo On CompanyAddressBook
Disable Trigger Tr_RegNo On
CompanyAddressBook
7.Constraints Example
1.ALTER TABLE
[dbo].[AddressBook] ADD CONSTRAINT [FK_
_AddressBookID] Foreign
Key(AddressBookID) References
AddressBook(AddressBookID)
2. Create NonClustered Index
IX_BankAddressBook_AccountNo On AddressBook(AccountNo)
11. Check Constraints
ALTER TABLE [dbo].[Employee] with check ADD CONSTRAINT [Email_Validate]CHECK(ID>0)
sp_helpConstraint Employee
8.Check Case Sensitive
select *
from Employee Where BINARY_CHECKSUM(Password) =BINARY_CHECKSUM('admin')
select * from Employee Where Convert(Varbinary,Password) =Convert(Varbinary,'Admin')
select *
from Employee Where
Password ='admin' COLLATE SQL_Latin1_General_CP1_CS_AS
9. Create Linked server
EXEC sp_addlinkedserver @server = 'NEWSERVER',@srvproduct
= 'Checking',
@provider = 'SQLNCLI', @datasrc = 'MyServer'
10.Using RowNumber()
Function
1.SELECT ROW_NUMBER() OVER(ORDER BY LastName) AS RowNum,Name FROM vEmployee
2.SELECT ROW_NUMBER() OVER(ORDER BY Department) AS RowNum,
RANK() OVER(ORDER BY Department) AS Ranking FROM vEmployee
3. SELECT ROW_NUMBER() OVER(ORDER BY Department) AS RowNum,
DENSE_RANK() OVER(ORDER BY Department) AS Ranking FROM vEmployee
11. Example for CASE
Select Case
When(TrailerID
>0)then (Select Name from
Vehicles)
ELSE case
when (TrailerID>0)Then (Select Name from Vehicles)
End
End as Vehicle From
JobHeader
12.Examples For Rules
create Rule
SampleRule as @Salary >0
sp_bindRule 'ableName.ColumnName'
sp_UnbindRule 'Employee.Salary'
drop rule
SampleRule
13. COALESCE Function
DECLARE @foo VARCHAR(5) SET @foo = NULL
SELECT COALESCE(@foo, '123456789') OutPut: 123456789
14.DateTime Functions
First
day of the Month
SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay
DATEADD
Functions
DATEADD Function adds a certain interval of time
to the specified date and time value
Syntax: DATEADD (datepart , number, date ) EX: Select DATEADD (yy , 1, GetDate())
DATEDIFF
Function
DATEDIFF
() gives the difference between the two date values.
Syntax:DATEDIFF ( datepart , startdate , enddate
)
DATEPART
Function
To retrieve any part of date and time use
DATEPART function.
Syntax: DATEPART ( datepart , date )
DATEPART
Output
SELECT
DATEPART(year,
'2009-02-13 18:35:06.523') 2009
SELECT
DATEPART(quarter,
'2009-02-13 18:35:06.523') 1
SELECT
DATEPART(month,
'2009-02-13 18:35:06.523') 2
SELECT
DATEPART(dayofyear,
'2009-02-13 18:35:06.523') 44
SELECT
DATEPART(day,
'2009-02-13 18:35:06.523') 13
SELECT
DATEPART(week,
'2009-02-13 18:35:06.523') 7
SELECT
DATEPART(weekday,
'2009-02-13 18:35:06.523') 6
DATENAME Function
DATENAME
Function returns a character string that represents date part of the specified
date.
Syntax:
DATENAME ( datepart , date )
SELECT
DATENAME (month, '2009-02-13 18:35:06.523')
=> Output : February
DAY, MONTH, and YEAR Functions
SELECT DAY('2009-02-1 18:35:06.523') as 'Day',
MONTH('2009-02-1 18:35:06.523') as 'Month', YEAR('2009-02-1 18:35:06.523') as
'Year
----Last Day of Current Year
SELECT
DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))
----First Day of Current Year
SELECT
DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
----Last Day of Current Month
SELECT
DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))
----First Day of Current Month
SELECT
DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
----First Day of Last Week
SELECT
DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'
----First Day of Current Week
SELECT
DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
----Last Day of Current Week
SELECT
DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'
----Yesterday
SELECT
DATEADD(d,-1,GETDATE()) 'Yesterday'
--–First Day Of Current Quarter
SELECT
Dateadd(qq, Datediff(qq,0,GetDate()), 0)
–--First Day Of Current Quarter
SELECT
Dateadd(qq, Datediff(qq,0,GetDate()), 0)
