Locking Hints - A range of table-level locking hints can be specified using the SELECT, INSERT, UPDATE, and DELETE statements to direct Microsoft® SQL Server 2000 to the type of locks to be used. Table-level locking... Read More

SELECT COUNT(*) AS Counter FROM syscolumns WHERE (name = 'empno') Read More

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... Read More

The row being fetched is missing. This means that the row that was being fetched from the cursor is missing. Read More

No Read More

access to one particular database used by the time card application for server logins. What security role should you assign them? db_accessadmin This user will not add logins, but rather grant database level access for existing... Read More

SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties -> Port number, both on client and the server. Read More

This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into... Read More

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. With a linked server, you can create very clean, easy to follow, SQL... Read More

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... Read More

Triggers can be classified as Key Triggers, Mouse Triggers ,Navigational Triggers. Key Triggers :: Key Triggers are fired as a result of Key action.e.g :: Key-next-field, Key-up,Key-Down Mouse Triggers :: Mouse Triggers are... Read More

A View can be updated/deleted/inserted if it has only one base table. If the view is based on columns from one or more tables then insert, update and delete is not possible. Read More

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... Read More

As we all know SQL server has performance issue with cursor and looping. When we use recursive functions or a cursor in stored procedures we have to compromise with performance. While integrating CLR we can write the looping and... Read More

Explanation: The answer is up in the air and this is more of a poll than a real QOD. Based on the ways things are going, the staff here sees just 1, though our hope would be that 3 or 4 would be released. Read More

Ball This should return "Ball" only since the ^ operator means not matching the next character. In this case, the first character should not be an "A". Read More

They are not really secure, that are only obfusticated. There are tools that you can use to get at the code. The code itself is just obfusticated. However, you cannot edit the code, the stored procedure or view would have to be dropped... Read More

Whats the best way to update data between SQL Servers? What are the scenarios you will need multiple databases with schema? How will you plan your replication? What is a publisher, distributor and subscriber in “Replication”? ... Read More

SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay Datepart Abbreviations year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww ... Read More

Determining word boundaries. Word breaking involves finding the boundaries of a word. This is different for different languages and SQL Server 2005 includes word breakers for 23 languages. Read More

Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries... Read More

What is a “Database Transactions “? What is ACID? What is “Begin Trans”, “Commit Tran”, “Rollback Tran” and “Save Tran”? What are “Checkpoint’s” in SQL Server? What are “Implicit... Read More

Relational tables have six properties: · Values are atomic. · Column values are of the same kind. · Each row is unique. · The sequence of columns is insignificant. · The sequence... Read More

You can use the OPENROWSET( ) function to run a query on a remote SQL server by using the following syntax:   SELECT *FROM OPENROWSET('SQLOLEDB', 'remote_server_name'; 'sa'; 'password... Read More

char[(n)] - Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character. nvarchar(n) - Variable-length... Read More

@a = 11 , @b = 5 Here is the explanation: Step 1 @a has the value of 5 and is binairy 101 @b has the value of 11 and is binairy 1011 After ?select @a = @a ^ @b , @b = @b ^ @a, @a = @a ^ @b? the values are ... Read More

server is feeling memory pressure and you want to release some memory and reduce SQL Server to 3GB or RAM. How can you do this? Change the max amount of memory allocated and shut down and restart SQL Server to have it... Read More

No If you change your execution context to a user, which is scoped at a database level, then any queries to linked servers or other databases will fail. There is a way around this restriction if trust relationships are setup. Read More

In LOB pages Since the size restrictions for varchar(max) are the same as for text data, this data is stored in LOB data pages. Read More

will have an equal prize amount. What T-SQL function can achieve this? RANK alongside the OVER function where the OVER function uses the players number of strokes taken The rank function will return the rank of the row in the... Read More

server that created the stored procedure, the login name and the contents. How would you do this? Create a DDL trigger on CREATE_PROCEDURE database event The answer is Create a DDL trigger on CREATE_PROCEDURE database event You... Read More

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... Read More

“Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.” In short, replication is all about having... Read More

Database normalization is a data design and organization process applied to data structures based on rules that help building relational databases. In relational database design, the process of organizing data to minimize... Read More

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... Read More

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... Read More

A correlated subquery is an inner subquery which is referenced by the main outer query such that the inner query is considered as being executed repeatedly. Example: ----Example of Correlated Subqueries USE... Read More

Try to pass value as string.   If you were passing a string value then the LZ should not be dropped, so I suspect that you are passing a numeric value then converting it back to a varchar which will drop any LZ.... Read More

-5 This will return -5 as the result. The + operator functions as a unary plus operator, which means that it performs no operation on the value it preceeds. Read More

A rolling update When performing a rolling update, doing the detach and attach on each server as opposed to detaching from all then attaching to all, it is possible that different reporting servers will display different results. Read More

This does not make sense. For such a small number of rows, it is unlikely that dropping the indexes will improve the performance of your load. If this were 100,000 rows, then it might make sense. Read More

as shipping instructions, secretaries' names, etc. will be moved to a new table along with the PK. What type of partitioning is this? Vertical partitioning If you are... Read More

No You cannot re-assocaite the application because when you add the application, SSNS recreates the objects. If they already exist, the create fails. Read More

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. Read More

and need to choose an algorithm for your data. Which of the following is not available to you? 128-bit AES While all of these are valid encryption algorithms on SQL Server 2005, the AES algorithms (128-bit, 192-bit, and 256-bit)... Read More

stored on a separate physical disk array and use that for reporting queries. However the queries still seem slow immediately after snapshot creation. Why? The snapshot still queries the original database. The snapshot of the... Read More

too large for your CD burner and there is no tape drive at the remote office. What feature of SQL Server 2005 would help you get the snapshot transferred? Compressed Snapshots In SQL Server 2005, you can specify a snapshot to be... Read More

This switch reserves memory space for Hot-Add memory metadata, available with AWE enabled. This switch is used with 32-bit SQL Server and AWE to reserve memory to reserve memory space for Hot-Add memory metadata. Read More

Set the encryption level to verify the certificate is issued by a trusted authority. Since you are asked to use replication, a replication solution is to use encryption for the connection and verify the certificate being used is... Read More

Fast Parse The fast parse mode that is set on columns for a flat file source connection can import a limited set of data types extremely quickly. Read More

None of the above. The SQL Variant type can store all datatypes except varchar(max), varbinary(max), xml, text, ntext, rowversion/timestamp (thought the data of a rowversion can be stored in a binary(8), which can be stored in a... Read More

would like to allow another administer to start, stop, and view the history for, but not delete history. This administrator will own the jobs. Which role should you assign? SQLAgentUserRole SQL Server 2005 provides 3 fixed roles... Read More

directly access the SQL Server. One of your developers complains about connecting to the Integration Services esrvice on your server. What could be the problem if you know the service is working properly? The firewall needs to... Read More

DBMS can be defined as a collection of related records and a set of programs a set of programs that access and manipulate these records. Read More

DESCRIBE is not a SQL Server Command. This a command used in ORACLE & MYSQL to display the sturcture of a Table. Read More

Transaction Lavel isolation:- The isolation level used during the execution of SQL statements determines the degree to which the activation group is isolated from concurrently executing activation groups. Thus, when activation group P executes... Read More

The snapshot still queries the original database. The snapshot of the production database will only reduce I/O for changed pages. Any data that has not been changed will be read from the source database, which is in this case is experiencing heavy I... Read More

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... Read More

Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a... Read More

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... Read More

Server and Database Level DDL Triggers. If developers have their own login to SQL Server and it does not have permissions to drop or create objects this will not be issue at all. However, there are still risk of... Read More

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... Read More

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. In order to work with a cursor we... Read More

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... Read More

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... Read More

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to... Read More

Expand-server-management-double click Activity Monitor. on left side you have three options to choose from, select those options and you can see all the locks related information. run this stored procedure in the database. ... Read More

The database server uses a B-tree structure to organize index information. B-Tree generally has following types of index pages or nodes: · root node: A root node contains node pointers to branch nodes which can be only one.... Read More

Recompilations might be the source of the slower stored procedure speed. To find out for sure, you need to do some performance investigation, such as looking at Showplans for each type of query versus calling the stored procedures and... Read More

. 'Count': Counts the number of non-null values. 'Count(*)': Counts the number of rows in the table, including null values and duplicates. Read More

SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 ON t1.a1=t2.a1 AND t1.a2=t2.a2)   This is the solution. The difference of (the set of t1) - (the set of t2) is   SELECT * FROM t1 LEFT OUTER JOIN t2... Read More

Ever wanted to delete files from the local machine that your SQL Server database is running? You can do it using the extended stored procedure xp_cmdshell like this:   EXEC master..xp_cmdshell ' ... Read More

I have very limited access to the Oracle database but full control of the SQL Server database.How do I join two different queries from two different databases?   Answer: To query to different data sources, you can make... Read More

not the older ODBC provider for SQL Server, or the ODBC provider for OLE DB. The parameter you will use in your connection string is "provider=sqloledb". The OLE DB provider performs much more efficiently than the ODBC provider,... Read More

The GetRows method is used to pull all the records from the recordset into an array, which is much faster than using embedded Transact-SQL to download a RecordSet to your application. Read More

SELECT sid, mark, COUNT(*) AS Counter FROM marks GROUP BY sid, mark HAVING (COUNT(*) > 1) Read More

SELECT TOP 1 salary FROM (SELECT DISTINCT TOP 6 salary FROM employee ORDER BY salary DESC) a ORDER BY salary Read More

There are clustered and nonclustered indexes. 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... Read More

2 and 2 Each select statement actually returns 2 rows. You can use this script to check this: create table mytable ( id int identity(1,1) , mychar varchar(20) ) go insert mytable select null insert mytable... Read More

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. ... Read More

Users can do equals, greater than, less than, etc, plus they can do logical AND, OR, NOT operations. Users can also group filters to allow more advanced filters. While it looks a little different than you may be used to, the filter... Read More

recover to a standby server using the previous night's backups. How do you recover the snapshot? There is nothing you can do. The snapshot is lost. Database snapshots cannot be... Read More

(For instance: if a word document is stored in the image or a varbinary(MAX) column and the value of the type column is '.pdf'. The row is not full-text indexed and an error message is written to the SQL Server Error Log ... Read More

Query the system catalog view: sys.conversation_endpoints The system catalog view sys.conversation_endpoints will show you which Service Broker endpoints, and therefore ports, are open on your server. Read More

t process any orders, the report should reflect the employee?s name and a zero for the number of orders. Which of the queries is the best one for Janice to use to return the information she has been requested to provide? SELECT ... Read More

What do you need to do now to ensure you can recover the routes in the event of a disaster? Back up the msdb database. The routes for forwarding are stored in msdb.sys.routes. If you back up the msdb database, the routes will be... Read More

Named Pipes The readpipe and makepipe utility combination will test named pipe connectivity. Read More

ALTER DATABASE Sales SET DISABLE_BROKER To disable the receipt of messages, you can disable the Service Broker by disabling it with the ALTER DATABASE command. The SET command is "DISABLE_BROKER". Read More

You then find out that Bob is a part of the HR group because of cross training and needs to use the same objects from the same application except for the Vacation table, to which he should not have access. No column permissions are... Read More

Bob. How can you determine which user is executing statements in Profiler? Check the Sessionloginname data column. The SESSIONLOGINNAME column, not shown by default, will contain the original login name of the user even if their... Read More

memory pressure, so you specify a new server with 8GB of RAM. What else should you configure on these instances? Min and Max memory for each instance If you are designing multiple instances, you will want to specify the amount... Read More

All of the above are valid On a Windows XP host, the password policy is not enforced because the appropriate API call is not available on the operating system. Read More

2 Since you have two separate schedules for the maintenance operations, you will need to have two separate plans to handle this need. Each plan can only be executed on one schedule, so one is needed for a single daily execution,... Read More

What could be the problem? The CLR environment needs to be enabled. By default on SQL Server 2005, the CLR runtime environment, which is what executes the .NET assemblies, is not enabled. You need to... Read More

CREATE DATABASE test_schema GO USE test_schema GO CREATE SCHEMA Schema1 CREATE TABLE Table1 (col1 int) GO CREATE SCHEMA Schema2 GO CREATE TABLE Table1 (col1 int) GO SELECT SCHEMA_NAME(schema_id), name... Read More

What are indexes? What are B-Trees? I have a table which has lot of inserts, is it a good database design to create indexes on that table? What are “Table Scan’s” and “Index Scan’s”? What are the two types of... Read More

Business logic handlers are written in managed code and allow us to execute custom business logic during the merge synchronization. We can invoke the business logic handler in case of non-conflicting data changes. Business logic... Read More

There are different variants of SQL Server 2005 commercially available. ·         Express – Free and only for one user ·         ... Read More

SQL Server 2005 now supports sending E-mail from the database. It is called as database mail and it uses DatabaseMail90.exe. Gone are the days when we were using a third party component for this. Receiving an... Read More

SQL Service broker is a new technology introduced in SQL Server 2005 for building database-intensive distributed applications. Basically, service broker has been built for developing applications that consist of individual components... Read More

1.     Triggers allow us to execute a batch of SQL code when an insert, update or delete command is executed against a specific table. Triggers are "attached" to a single table and allow us to setup our... Read More

A relation is said to be in second normal form (2NF) if and only if it is in 1NF and every non-key attribute is fully dependent on the primary key. Read More

A relation R is in third normal form (3NF) if and only if it is in 2NF and every non-key attribute is non-transitively dependent on the primary key. An attribute C is transitively dependent on attribute A if there exists an attribute B... Read More

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): ... Read More

Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise. Read More

Here are some disadvantages of a stored procedure 1) Stored procedures split your code between the one program and process and another program and process. Obviously, it is more convenient to have to program in only one place.... Read More

Data Transformation Services is used to transfer the data from one source to our required destination. Considering am having some data in sql server and I need to transfer the data to Excel destination. Its highly possible with dialogue... Read More

Read More

Normalization is the technique of breaking the complex tables into much understandable smaller one to improve the optimzation of the database structure. The concept of "Fucntional Dependancy" is followed in Second Normal Form (2NF) which means all... Read More

Vertical partitioning If you are moving some columns from one table to a new table, this is vertical partitioning. Read More

There is nothing you can do. The snapshot is lost. Database snapshots cannot be backed up, so once the server failed, the database snapshot was lost. Read More

No You cannot re-assocaite the application because when you add the application, SSNS recreates the objects. If they already exist, the create fails. Read More

The only thing you have to do is to insert "for XML Auto" in your query and the result will be returned in XML format. Eg. Let there be a table with name tblABC Normally ??? Select * from tblABC For XML Data ??? Select * from tblABC for... Read More

Analytical Function:- return the infornation that about the relative possition of record (ROW_NUMBER,Rank,DENSE_RANK) and allow to campare with succesor and precedessors when they sort in spacific order. select ename, deptno, sal, hiredate,... Read More

SQL native client supports client side data access library for Microsoft SQL server. It supports various features such as TDS implementation, data types support, query notifications, etc. It can also receive multiple results in a single database.... Read More

A cost based query optimizer is provided in SQL which primarily reduces cost. It reduces cost by limiting the resources for a given query. Initially it looks at the data base schema, database statistics and the load present in the system. After... Read More

A DBMS has to be persistent, that is it should be accessible when the program created the data ceases to exist or even the application that created the data restarted. A DBMS also has to provide some uniform methods... Read More

A. Third Normal Form (3NF) is most preferable normal form in RDBMS.Normalization is the process of designing a data model to efficiently store data in a database. The rules of 3NF are mentioned here ... Read More

A noncorrelated subquery is subquery that is independent of the outer query and it can executed on its own without relying on main outer query. Example: ----Example of Noncorrelated Subqueries USE AdventureWorks; ... Read More

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... Read More

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table... Read More

· Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same realworld event or object are linked together; · Time-variant, meaning that the changes to the data... Read More

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 Read More

Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another... Read More

They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves... Read More

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For... Read More

Windows mode and Mixed Mode – SQL & Windows. To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL... Read More

SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY('productlevel'), SERVERPROPERTY ('edition') Read More

Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets... Read More

Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping... Read More

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table.... Read More

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints. Read More

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.  Constraint will suggest that value which is larger than 11 character can not be... Read More

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. what is NULL?  Simply put NULL is unknown or missing... Read More

· 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, reducing server overhead. ·... Read More

BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user... Read More

Use sp_configure to display or change server-level settings. To change database-level settings, use ALTER DATABASE. To change settings that affect only the current user session, use the SET statement. E.g. sp_CONFIGURE ... Read More

The master database holds information for all databases located on the SQL Server instance and is theglue that holds the engine together. Because SQL Server cannot start without a functioning masterdatabase, you must administer... Read More

They get stored in System Catalog Views sys.server_principals and sys.sql_logins. Read More

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... Read More

Filtered Index is used to index a portion of rows in a table that means it applies filter on INDEX which improves query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.... Read More

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... Read More

Synonyms give you the ability to provide alternate names for database objects. You can alias object names; for example, using the Employee table as Emp. You can also shorten names. This is especially useful when dealing with... Read More

1. Expand server – management-currentActivity-expand locks/processid and you will be able to see all the locks related information. 2.Expand server – management-currentActivity-expand Locks/object you can see locks by... Read More

XPath uses a set of expressions to select nodes to be processed. The most common expression that you’ll use is the location path expression, which returns back a set of nodes called a node set. XPath can use both an... Read More

SQL Server now supports the use of TRY…CATCH constructs for providing rich error handling. TRY…CATCH lets us build error handling at the level we need, in the way we need to, by setting a region where if any error occurs, it... Read More

RaiseError generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in thesys.messages catalog view or build a message dynamically. The message is... Read More

The xml data type lets you store XML documents and fragments in a SQL Server database. An XML fragment is an XML instance that is missing a single top-level element. You can create columns and variables of the xml type and store... Read More

Run following query in Query Editor. USE <database_name>; GO SELECT SCHEMA_NAME(schema_id) AS schema_name ,name AS table_name FROM sys.tables WHERE OBJECTPROPERTY(OBJECT_ID,'IsIndexed') =... Read More

A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column... Read More

sqlcmd is enhanced version of the isql and osql and it provides way more functionality than other two options. In other words sqlcmd is better replacement of isql (which will be deprecated eventually) and osql (not included in... Read More

Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic. Different Ranking functions are: ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>... Read More

One of the purposes of Extensible Markup Language (XML) is to solve challenges like this, but until all applications become XML-enabled, consider using our faithful standby, the bulk copy program (bcp) utility. This... Read More

In SQL Server, the physical mechanism that UNIQUE and PRIMARY KEY constraints use to enforce uniqueness is a unique index. Because SQL Server 2000 supports indexes on computed columns, you can create UNIQUE and PRIMARY KEY... Read More

You can get a list of tables in a database by calling the INFORMATION_SCHEMA.Tables view like this:   SELECT * FROM information_schema.tables   This returns the name of the current database, the owner,... Read More

If you have ever monitored any blocking problems in SQL Server, you know that sp_who only shows you the spid (SQL Server's internal Process ID) that is causing the blocking for each spid that is blocked. Often a blocked spid is... Read More

Many times, you'll want to know the last identity (key) value that was used in an insert. The biggest reason for this is so that the same value can be reused when inserting a foreign key. This is done differently between SQL... Read More

To find rows in one set that do not exist in another set, use the except operator (as defined in SQL-92 and SQL-99). For example, here's how you find column1 from Table1 that does not exist in column2 of Table2: Select... Read More

The biggest benefit of using derived tables over using temporary tables is that they require fewer steps, and everything happens in memory instead of a combination of memory and disk. The fewer the steps involved, along with less I/O,... Read More

if they meet all of the following criteria: ·          The computed column's expression is deterministic. ·          The... Read More

avoid as a matter of routine applying them to large result sets. This is because complex user-defined functions have the potential of involving high overhead, and there is no way to know for sure how they will affect performance when... Read More

SQL Server 2000 offers a new data type called "table." Its main purpose is for the temporary storage of a set of rows. A variable, of type "table," behaves as if it is a local variable. And like local variables, it has a limited scope... Read More

For example, if you need to reuse the value of the length of a string over and over within your code, perform the LEN function once on the string, and this assign the result to a variable, and then use this variable, over and over, as... Read More

which it does not. While SUM() is used to sum a group a data, the theoretical PRODUCT() function would find the product of a group of data. One way around the problem of there not being a PRODUCT() function in SQL Server is to... Read More

By design, an identity column does not guarantee that that each newly created row will be consecutively numbered. This means they will most likely be occasional gaps in the identity column numbering scheme. For most applications,... Read More

Let's find out why this may not be a good idea. First, let's look at the following code: SELECT employee_id FROM employees WHERE age = 30 and service_years = 10 Assuming that both the age and the... Read More

The firehouse cursor is really an incorrect term because it is not a cursor. A firehose cursor is just a method to quickly move data from SQL Server to the client that requested it. Essentially, a firehose cursor sends the... Read More

Each and every time you use ADO to execute Transact-SQL code to get data from SQL Server, multiple, time-consuming steps have to occur. For example: ·         ... Read More

when appropriate. This is especially true if you are not encapsulating your Transact-SQL in stored procedures as recommended. While calling a view is not usually as efficient as using a stored procedure to retrieve data, it is much more... Read More

it is performance suicide. Also avoid ODBCDirect. Instead, use RDO or ADO. Read More

instead of calling a separate stored procedure for every database task you want to perform. This helps to reduce network traffic and server overhead. Read More

This way, you can limit how may rows are returned, even if the user doesn't enter any criteria to help reduce the number or rows returned to the client. Read More

try to put the loop inside a stored procedure so it can be executed on the server without having to make round trips between the client and server. Read More

CacheSize property of the ADO Recordset object determines how many rows are fetched from a server-side cursor at a time. The default is one. This means each row of the recordset is returned one at a time from the server to the... Read More

don't use the Refresh method to identify the parameters of a stored procedure. This produces extra network traffic and slows performance. Instead, explicitly create the parameters yourself using ADO code. Read More

In effect, when you use this method, it re-executes the entire query and repopulates the Forward-Only cursor, increasing server overhead. Read More

Ttry to follow these two suggestions if you want optimum speed: 1) use in-process dlls; and 2) use early-binding. Read More

always use strongly typed variables. Avoid using the variant data type (which is not always possible), as it has greater overhead than the other data types. Read More

be sure to strongly type them. Avoid using the AS OBJECT keywords, instead, always explicitly specify the type of object you want to create. Read More

SET ROWCOUNT 1 DELETE yourtable FROM yourtable a WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1 WHILE @@rowcount > 0 DELETE yourtable FROM yourtable a ... Read More

SELECT UPPER('test') Read More

SELECT CEILING (7.1) Read More

Only 1 clustered index is possible. Read More

You can use index hint (index=<index_name>) after the table name. SELECT au_lname FROM authors (index=aunmind) Read More

When you create an index in the database, the index information used by queries is stored in index pages. The sequential index pages are chained together by pointers from one page to the next. When changes are made to the... Read More

Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in... Read More

128bit Read More

Sometimes we have to select data from two or more tables to make our result complete. We have to perform a join. Read More

join selects columns from 2 or more tables. A union selects rows. Read More

Derived tables are SELECT statements in the FROM clause referred to by an alias or a user-specified name. The result set of the SELECT in the FROM clause forms a table used by the outer SELECT statement. For example, this... Read More

SQL Server uses these resource lock modes. Lock mode Description Shared (S) Used for operations that do not change or update data (read-only operations), such as a SELECT statement. Update (U) Used on... Read More

You can use the sp_procoption system stored procedure to mark the stored procedure to automatic execution when the SQL Server will start. Only objects in the master database owned by dbo can have the startup setting changed... Read More

When used in a stored procedure, the RETURN statement can specify an integer value to return to the calling application, batch, or procedure. If no value is specified on RETURN, a stored procedure returns the value 0. The... Read More

It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order: 1. The stored procedure in the master database. ... Read More

1) Server restart 2) Plan is aged out due to low use 3) DBCC FREEPROCCACHE (sometime desired to force it) Read More

CREATE PROCEDURE SP1 AS BEGIN TRAN INSERT INTO MARKS (SID,MARK,CID) VALUES (5,6,3) EXEC SP2 ROLLBACK GO CREATE PROCEDURE SP2 AS BEGIN TRAN INSERT INTO MARKS (SID,MARK,CID) VALUES (100,100,103) ... Read More

RAISERROR - Returns a user-defined error message and sets a system flag to record that an error has occurred. Using RAISERROR, the client can either retrieve an entry from the sysmessages table or build a message dynamically... Read More

Triggers are nested when a trigger performs an action that initiates another trigger, which can initiate another trigger, and so on. Triggers can be nested up to 32 levels, and you can control whether triggers can be nested... Read More

Trigger will get execute automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or view. We have to call stored procedure manually, or it can execute automatic when the SQL Server starts ... Read More

SQL Server excludes an ORDER BY clause from a view to comply with the ANSI SQL-92 standard. Because analyzing the rationale for this standard requires a discussion of the underlying structure of the structured query language... Read More

SQL Server is a DBMS system provided by Microsoft. SQL Server is sometimes mistakenly referred to as SQL. SQL Server is a DBMS system provided by Microsoft. SQL Server is sometimes mistakenly... Read More

Transactional deadlock errors. This level of error severity indicates a transaction deadlock error. Read More

Distributor, Publisher, then Subscriber. You always perform an upgrade in this order: distributor, publisher, subscriber. Read More

You need to enable RPC. By default, RPC is disabled in SQL Server 2005. You need to set the "remote access option" in your server configuration to 1 to allow the execution of stored procedures from a remote server. Read More

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. Read More

The owner of the message type. This parameter determines the owner of the message type. This defaults to the current user. Read More

Add, string concatenation, unary plus The three functions are Add, String Concatenation, and Unary Plus. Read More

Blocking Transformation The Sort component is a blocking transformation meaning that it needs all rows in order to perform its function. Read More

DDL triggers DDL triggers cannot be signed, but all the other objects can. Read More

Use the select * from OPENROWSET(BULK...) command. SQL Server 2005 includes a new option with the OPENROWSET command for getting large amounts of data from an OLEDB data source. It is the BULK option and works similar to the BULK... Read More

1024 This performs a bitwise AND operation between the two integers and sets the result to this. Since 1024 is a single set bit in it's value, if the corresponding bit is set to 1, then in the result the bit is set to 1. In... Read More

This agent reads the publisher log for transactions to send to the distributor. This agent is tasked with reading the transaction log in transactional replication and moving those transactions that need to be replicated to the... Read More

Use Database Mirroring to fail over between the old reporting database and the new one. The update process for a Scalable Shared database with minimal downtime involves putting out a new copy of the database, detaching the old... Read More

This is the number of seconds that a serialplan cannot exceed if it is to be used. A parallel plan is used if the estimate exceeds this value. This is the threshold at which SQL Server determines whether a serial or parallel plan... Read More

Service Broker does not consider the collation, using byte-by-byte matching for names. Neither collation is used. Service Broker operates in a collation independent method that removes collation information from the messages. Read More

A reporting database server A scalable shared database is a feature that allows you to setup read-only database on a separate server for reporting purposes. This database provides an identicle view of your data from another server. Read More

SQL Mail through Extended MAPI or Database mail. SQL Server Agent Mail can be configured to use Database Mail or Extended MAPI. Read More

Seconds This value measures the number of seconds for a plan where the optimizer chooses between serial and parallel plans. Read More

Add a field to the model in Report Builder using the built in formula/function support While Report Builder cannot be used to build or maintain models, it does allow you to add a virtual field that exists only within... Read More

Booleans are formatted as True/False and there are no other built in options, but you could build an expression using IIF that would let you do other formatting From the format dialog there are no extra formatting options for... Read More

Saves the report to the report server File|Save writes the report to the Report Server. Users have the option to also save the report to disk by using File|Save to File. Report Builder users cannot modify the model. Read More

Table, Matrix, Chart Report Builder can build a report formatted as a table, chart, or matrix (cross tab), but only ONE can be used in any given report. Read More

Users can pick from a small number of predefined formats and they have the option to specify a custom format There are give built in formats; general, currency, percentage, two place decimal, and exponent. Users can also define a... Read More

False There is no LIKE support, the next best thing is the CONTAINS function which works as if you specified both a leading and trailing wild card. Read More

Windows 2003 and XP Pro Both Windows 2003 Server and later as well as Windows XP Professional support Instant File Initialization. Read More

Once the file is grown, zeros are written to the new space, causing a delay. When a database file grows, unless instant file initialization is turned on, the server must allocate disk space and then write 0s into all that space. This... Read More

nscontrol.exe The nscontrol application can be used with various parameters to administer a SQL Server 2005 Notification Services instance. Read More

Yes An event can have multiple triggers assigned to it. Read More

None This is a trick questions. No users are added to application roles. Application roles are invoked by a user. Read More

TCP/IP Only those clients connecting with TCP/IP can use Kerberos authentication. Read More

Windows 2003 Server You can only enforce password policy on the Windows 2003 Server platform and newer. Read More

The Object Explorer for the server has a "Triggers" folder. The server-scoped DDL triggers will appear in Management Studio in the Object Explorer under the "Triggers" folder. Read More

You cannot back up a database snapshot. A database snapshot cannot be backed up or restored. Read More

Have users save the RDL files locally and check into source control using a source control utility, or write code to script them out each night and check them into source control Users can do faux source control by just saving... Read More

Report Builder Report Builder is bundled as part of Reporting Services 2005 and is targeted at end users needing to build ad hoc reports. BI Design Studio could be used, but typically requires more time and experience... Read More

Snapshot and transactional Oracle publishing with SQL Server works as both snapshot and transactional publication issues. What does @@textsize return? The current value of the TEXTSIZE option for the SET command. This... Read More

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. Read More

It is still sent to the distributor and executed on the subscriber. A transaction that fails in the publication database is still sent to the distributor and subscriber. YUou can trap the error, but you would need to trap it on the... Read More

<replacement> <pat>SS2K5 </pat> _{SQL Server 2005} _{SQL Server 2K5} </replacement> A search for "SS2K5" doesn't return results with fields that you... Read More

Off The correct answer is off ? The TRUSTWORTHY database property indicates whether the installed instance of SQL Server trusts the database such that it can reach out and perform actions outside the database. By default it is OFF... Read More

In the Windows Application Log Event messages are logged in the Windows Application log. Read More

An XML file in the file system named tsxxx.xml where xxx is a code. The thesaurus file for SQL Server 2005 is an XML file containing data and stored in SQL_Server_install_pathMicrosoft SQL ServerMSSQL.1MSSQLFTDATA directory. There is... Read More

1 and 3 Tracer tokens measure two things. One is the amount of time elapsed between a command being committed on the publisher and being written to the distribution database. The other is from the writing to the distribution... Read More

dtexec.exe The dtexec.exe utility is used to configure and execute Integration Services packages from the command line. Read More

available with a service master key. What method would you call to create this key? The Regenerate method under the ServiceMasterKey object. To create a Service Master Key in SMO, you would get a handle to the ServiceMasterKey... Read More

It does not support an error output. The raw file source has only one output and does not support an error output. It also reads faster than other data sources because it has no parsing or translation and does not use a connection... Read More

Edit the nsservice.exe.config file in the C:Program FilesMicrosoft SQL Server90NotificationServices... Read More

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. Read More

A dialog conversation is a conversation between services. A dialog conversation is a conversation between services. A conversation includes messages being passed back and forth as part of a contract. Read More

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. Read More

Tracer Tokens. You can use tracer tokens in SQL Server 2005 replication to measure latencies. These are small amounts of data written into your transaction logs. Read More

Add them to the Publication Access List in the publication database. All access to publications is controlled with the Publication Access List (PAL), which is similar to the Windows ACLs. A list is created for each publication that... Read More

Resources outside the SQL Server A credential is used to access resources outside of SQL Server. It contains the authentication information needed for accessing these resources. Read More

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. Read More

Use the ALTER QUEUE statement to turn activation on. One of the troubleshooting steps if messages remain in the queue is to check for an active queue monitor. If one is not on, then it needs to be activated. The ALTER QUEUE... Read More

Yes You can use full-text search for formatted data such as Word that contains text stored in a varbinary column. Read More

All of the above. SQL Server 2005 replicated databases support the following schema changes for objects: * ALTER TABLE * ALTER VIEW * ALTER PROCEDURE * ALTER FUNCTION * ALTER TRIGGER (DML only) Read More

Stop the server and restart it with a trace flag to allow system databases to be detached. Then copy a new version from the /INSTALL folder. By starting SQL Server with trace flag 3608 you can detach the msdb database. You can copy... Read More

Enabled for local connections only. The dedicated adminstrator connection is enabled for local connections only by default. Read More

Asymmetric Key A certificate is an asymmetric key that provides encryption in addition to authentication of an entity. Read More

Analysis Services Of these, only Analysis services is instance-aware. Read More

An acronym that describes concurrency support in a database The answer is an acronym that describes concurrency support in a database. ACID is an acronym for the following four terms: * Atomicity - Every operation within a transaction... Read More

TechPreparation The execution context switches can be nested, so changing to TechPreparation, then Steve, then issuing a Revert will return you to the context of TechPreparation. This works in the same manner as a stack. Read More

This utility will report the differences in data between two tables. This utility will report the data differences between two tables. They must be alike in structure and give back the T-SQL to bring the data in the tables to be... Read More

11-16 Severity levels 11-16 are correctable by users. Below 11, these are informational warnings and not raised as system errors. Errors above 16 must have an administrator or Microsoft correct. Read More

The timestamp datatype does not store time values. The answer is the timestamp datatype does not store time values. The better name for this datatype is rowversion, and it is actually a database-wide unique varbinary value. When you... Read More

You can monitor the Assembly Load event in a trace. While you could scan trace results for the names of functions and procedures using the assembly, it is possible that you may not know all the places the assembly is being called... Read More

Full Only the Full recovery model supports point in time restoration of your database. Read More

Yes By definition, the root element is required in a well-formed XML document and it contains all other content nested inside it. Read More

DBCC CHECKDB WITH NO_INFOMSGS This SMO method executes a DBCC CHECKDB with the NO_INFOMSGS option set. Read More

Yes Yes you can. Read More

8 The footnote for nested stored procedures limits the number at 8. Read More

Yes Yes, an index can be put on a view but the first index must be a unique clustered index. Read More

Restores your previous execution context. If you have changed your execution context with EXECUTE AS, the REVERT statement will restore the last context prior to the EXECUTE AS. Read More

You will receive as results: bob An error The NO REVERT option with EXECUTE AS prevents the return of execution context to the previous value. If you run the REVERT statement, you will receive the following error: Read More

Answer A and B Explanation Either of the commands in A and B will disable the trigger. Read More

Handles Volume Shadow Service Copy functions. The SQL Writer service was introduced to handle access to the data files in SQL Server. It allows backup programs, like VSS, to function while SQL Server is still running. Read More

AES (256 bit) The longer the key, the better the encryption, so choose longer keys for more encryption. However there is a larger performance penalty for longer keys. DES is a relatively old and weaker algorithm than AES.... Read More

SQL Server Integration Services You must install Integration Services in order to properly create and execute maintance plans since these plans are a subset of SSIS packages. Read More

All communication points with SQL Server using network protocols use an endpoint. SQL Server creates defaults for each protocol that you enable. Read More

An Asymmetric Key A certificate is like an asymmetric key and can validate the identity of the issuer. Read More

Unlimited, no different from the other SQL Server 2000 editions. The answer is actually an unlimited number of connections, though this in a practical sense is limited by memory on the machine (The actual limitation is 32k connecitons)... Read More

Coalesce returns the first non-null expression among its arguments. Lets say we have to return a non-null from more than one column, then we can use COALESCE function. SELECT COALESCE(hourly_wage, salary, commission) AS &... Read More

OPENXML can parse the xml data in SQL server very efficiently in SQL Server. OpenXML primarily gives the ability to insert XML data to the relational database, however we can query the data too using OpenXML. We need to specify the... Read More

temp table is a temporary table that is generally created to store session specific data. Its kind of normal table but it is created and populated on disk, in the system database tempdb — with a session-specific identifier... Read More

A cursor is a set of rows together with a pointer that identifies a current row. In other word, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, its like recordset in the ASP... Read More

 You can use Having Clause with the GROUP BY function in query and WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. Read More

 8060 bytes. Don't be surprised with questions like 'what is the maximum number of columns per table'. Check out SQL Server books online for the page titled: "Maximum Capacity Specifications". Read More

 A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction.... Read More

 SQL Server users constraints to enforce limitations on the data that can be entered into a particular column in table. There are following types of constraints. Unique, Default, Check, Primary Key, Foreign Key, Not Null.... Read More

There are four types of triggers. 1. Insert 2. Delete 3. Update 4. Instead of Read More

Clustered Index: Clustered index physically rearrange the data that users inserts in your tables. It is nothing but a dictionary type data where actual data remains. Non-Clustered Index: It Non-Clustered Index contains... Read More

Replication uses a publishing industry metaphor to represent the components in a replication topology, which include Publisher, Distributor, Subscribers, publications, articles, and subscriptions. It is helpful to think of Microsoft... Read More

Microsoft SQL Server 2005 provides the following types of replication for use in distributed applications: Transactional replication. Merge replication. Snapshot replication. The type of... Read More

Replication uses a number of standalone programs, called agents, to carry out the tasks associated with tracking changes and distributing data. By default, replication agents run as jobs scheduled under SQL Server Agent, and SQL... Read More

This section provides information about the stages involved in implementing replication. The process of implementing replication is different depending on the type of replication and the options you choose, but, in general,... Read More

Revisiting basic syntax of SQL? What are “GRANT” and “REVOKE’ statements? What is Cascade and Restrict in DROP table SQL? What is a DDL, DML and DCL concept in RDBMS world? What are different types of joins in SQL?... Read More

What are steps to load a .NET code in SQL SERVER 2005? How can we drop a assembly from SQL SERVER? Are changes made to assembly updated automatically in database? Why do we need to drop assembly for updating changes? How to see... Read More

What do we need Queues? What is “Asynchronous” communication? What is SQL Server Service broker? What are the essential components of SQL Server Service broker? What is the main purpose of having Conversation Group? How to... Read More

What is XML? What is the version information in XML? What is ROOT element in XML? If XML does not have closing tag will it work? Is XML case sensitive? What’s the difference between XML and HTML? Is XML meant to replace... Read More

What is Integration Services import / export wizard? What are prime components in Integration Services? How can we develop a DTS project in Integration Services? Read More

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... Read More

In previous versions of SQL Server, applications had to process or cancel all result sets from one batch before it could execute any other batch on that connection. SQL Server 2005 introduces a new connection attribute that allows... Read More

SQL Server 2005 enables administrators to manage permissions at a granular level. ·         In the new SQL Server 2005, we can specify a context under which statements in a module can... Read More

Online Analytical Processing (OLAP) allows us to access aggregated and organized data from business data sources, such as data warehouses, in a multidimensional structure called a cube. The arrangement of data into cubes avoids a... Read More

According to MSDN Data, mining is “the process of extracting valid, authentic, and actionable information from large databases.” Microsoft data mining tools are different from traditional data mining applications in significant... Read More

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... Read More

The integration with Microsoft Office system means the following. ·         Table Analysis Tools for Excel: Provides an easy-to-use add-in that leverages SQL Server 2005 Data Mining behind... Read More

With this feature the database engine can be directly exposed as a web service without a middle tier or even an IIS. This will enable the user to directly call a stored procedure by calling a web method. This feature is designed with... Read More

A database snapshot is a read-only, static view of a database, the source database. Each database snapshot is transaction-consistent with the source database as it existed at the time of the snapshot’s creation. Read More

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... Read More

As of now there are two service packs available for the SQL Server 2005. ·         Service Pack 1 – Has major changes or enhancements to SQL Server 2005 in Analysis Services, Data... Read More

SQL Server 2005 has added some new data types to its existing data types. XML Data type ·         VARCHAR (MAX) ·         NVARCHAR (MAX) ... Read More

Binary Data Binary data consists of hexadecimal numbers. For example, the decimal number 245 is hexadecimal F5. Binary data is stored using the binary, varbinary, and image data types in Microsoft® SQL Server... Read More

Transaction is logical unit between two commits and commit and rollback. Read More

TRUNCATE TABLE DELETE TABLE TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, ... Read More

Subquery is a query whose return values are used in filtering conditions of the main query. Read More

Subquery is a query whose return values are used in filtering conditions of the main query. Read More

     Each SQL statement within the query for Intersect, Minus, Union, Union ALL must have the same number of fields in the result sets with similar data types. ... Read More

Database Link is a named path through which a remote database can be accessed. An object stored in the local database that identifies a remote database, a communication path to the remote database, and optionally, a username and password... Read More

1) To protect some of the columns of a table from other users. 2) To hide complexity of a query. 3) To hide complexity of calculations. Read More

Yes. I tried using commit and Rollback in Instead of Triggers and it worked fine. Read More

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. Read More

A relationship is said to be in first normal form, if and only if all the cells in a table contain atomic values. Read More

A relation R is in Boyce-Codd normal form (BCNF) if and only if every determinant is a candidate key. The definition of BCNF addresses certain (rather unlikely) situations which 3NF does not handle. The characteristics of a relation... Read More

As the name indicates, denormalization is the reverse process of normalization. It's the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced... Read More

User defined data types let you extend the base SQL Server data types by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In... Read More

Bit data type is used to store Boolean information like 1 or 0 (true or false). Until SQL Server 6.5 bit data type could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit data type can represent... Read More

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... Read More

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. Read More

Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults. Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY ... Read More

A check constraint allows you to specify a condition on each row in a table. Note:  1) Check constraint can NOT be defined on a VIEW. 2) The check constraint defined on a table must refer to only columns in that... Read More

ADD Check Constraint: The syntax for creating a check constraint in an ALTER TABLE statement is: ALTER TABLE table_name add CONSTRAINT constraint_name CHECK (column_name condition)  [DISABLE]; The DISABLE keyword is... Read More

Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be... Read More

Straight forward. SQL is a single statement to finish up our work.Considering, I need some data from a particular table. “Select * from table” will fetch the necessary information. Where as I need to do some row by row processing. In... Read More

Both constraints will share a common property called uniqueness. The data in the column should be unique. The basic difference is, · Primary key won’t allow null value. Whereas, unique key will accept null value but only one... Read More

Read More

Stored procedures are precompiled T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements. As, its precompiled... Read More

They are three kinds of stored procedures,1.System stored procedure – Start with sp_2. User defined stored procedure – SP created by the user.3. Extended stored procedure – SP used to invoke a process in the external systems... Read More

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. Read More

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... Read More

Comparing to SQL Server 2000 SQL Server 2005 have more features 1. Reporting?services are available 2. .Net integration 3.?HTTP protocols Read More

delete from emp where en not in (select min(en) from emp order by columnname) Read More

varchar2: used for variable length character string ..stores the value on the disk varchar: exactly same as varchar2 the difference is that it doesnt store the value since it hav to be retrieved for further use char: char is used for fixed... Read More

WHERE clause is used to impose condition on SELECT statement as well as single row function and is used before GROUP BY clause HAVING clause is used to impose condition on GROUP Function and is used after GROUP BY clause in the query Read More

Tables,Views,clusters,index,cluster and sequences are callled dataobjects for a database. Read More

Hints are options or strategies specified for enforcement by the SQL Server 2005 query processor on SELECT, INSERT, UPDATE, or DELETE statements. The hints override any execution plan the query optimizer might select for a query. Read More

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is... Read More

We can use SQL Server as a back end in our application for storing and managing our important data. Read More

True Absolutely true. Users can define any portion of a filter to be a run time prompt, letting other users easily change the filter as needed. Not only is it easy to set up, Report Builder automatically populates a list of all possible choices... Read More

True True. Subreports can be used for a master-detail relationship, or the subreport can be a separate item, but in either case RS will query to get the data for the report once for each detail row. If end users are going to only occasionally look... Read More

Question : What does this return? declare @i int select @i = -5 select +@i         Answers: -5 This will return -5 as the result. The + operator functions as a unary plus operator, which means that it... Read More

The current value of the TEXTSIZE option for the SET command. This returns the current value of the TEXTSIZE option that can be changed with the SET command. The default is 4096 bytes. Read More

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... Read More

DTS is a set of tools you can use to import, export, and transform heterogeneous data between one or more data sources, such as Microsoft SQL Server, Microsoft Excel, or Microsoft Access. The Import/Export Wizard is for building relatively... Read More

There is one method for copying the structure of table without copying the data is create table NEWTable AS select * from OLDTABLE where 1=2 Read More

1.The major difference between procedure and function is that procedure is to be compile only one time and vice versa.Execution of procedure is faster then function 2.Function can be used in select Queries statement,but in stored procedure it'... Read More

A Bluetooth device playing the role of the ?master? can communicate with up to 7 devices playing the role of the ?slave?. This network of ?group of up to 8 devices? (1 master + 7 slaves) is called a piconet. A piconet is an ad-hoc computer network... Read More

Microsoft SQL server is primarily produced by Microsoft and it uses Transact-SQL as its query language. This is a Relational database management system which implements ANSI and ISO standards. It is one of the most popular software for Data base... Read More

There are over 80 distinct system privileges. Each system privilege allows a user to perform a particular database operation or class of database operations. Such as:- 1. ANALYZE ANY 2. CREATE CLUSTER 3. CREATE ANY CLUSTER 4. ALTER ANY CLUSTER ... Read More

By Inserting a Disc into the CD/ DVD Rom , and following the instruction we can install Read More

Architecture of MYSQL comprises of three main components they are ? SQLOS ? Relational Engine ? Protocol Layer. SQLOS implements basic functions such as thread scheduling, I/O and memory management. Relational engine is used to support databases,... Read More

We can't In a trigger we can't specified two table name that means CREATE TRIGGER < > ON <{ table | view }> { { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] } CREATE TRIGGER trigger_name ON {... Read More

SQL server basically supports about 2GB memory on a x86 hardware. If address window extension is installed in the operating system it will support up to 64 GB. On x64 hardware 8 TB of memory is supported. 4 GB of memory is supported when x86 versions... Read More

Functions such as buffer pool management, synchronization primitives, memory and I/O management, etc are supported by SQLOS. This forms the base component in SQL. Thread management is entirely performed by SQLOS, it also monitors worker threads... Read More

Yes, coming to your answer clustered index is used and applied only and primary keys For example if a table student is created consider here in this table student id is the primary key so clustered index is applied on the student id column. ... Read More

SQL server implements its own thread and memory and it doesn?t rely on the operating system because it needs specialized features. All the different operations are divided into tasks. It takes care of all the background works and client process... Read More

External interface is implemented by SQL server. Communications of external tasks are routed through Microsoft defined format onto the Protocol layer. Tabular data systems are used to transfer data between a client and server. Access to SQL is... Read More

Dirty Pages: Data that has been modified and Stored in the buffer cache and has not yet been written in to the hard disk. (Transaction Logs) Dirty reads: Reading the data that is actually read by Transaction2 which was modified by Transaction 1... Read More

Messaging and message queuing platform is provided by SQL server applications. It is a part of database engine and provides an asynchronous programming environment. It communicates over TCP/IP and it is used to synchronous different applications. Read More

In dynamic SQL method 4 in queries(select ) there are variable number of column and bind variable . Other word we can say that until run time we don't how many bind varible there. Exemple :- select :select_list Column from tablename where ... Read More

table in SQL Server : This # symbol prefixed with the tablename indicates that it is a local temporary table. This table will be existing till the session exists. Once the session expires the table will be dropped automatically. The table that... Read More

This command line tool is available in the package of Microsoft SQL server. This state about the management features of SQL server. Queries can be written and executed using this prominent tool. Scripts using this command tool are stored as . ... Read More

system.data.sqlclient Read More

1. Without any Null Value for Column1: Select * From Table1 Where Column1 Is Not Null 2. Instead of Null values it returns as '0': Select IsNull(Column1,0) Column1,* From Table1 Read More

It is possible that the statistics have changed since a stored procedure was compiled and thus may be executing a less the optimal execution plan, thus it may run slower. Read More

Sql Server:Runs only on windows platform,Hardware independent oracle:Runs in any platform and hardware dependent. Read More

It is the successor of SQL server 2000. It has support to manage XML data in addition to relational data. XML data type is converted into binary before storing it in the database. Using TDS packets SQL server 2005 allows data base server to be... Read More

Question : If you run this, what does it return? select applock_mode('public', 'SalesApp', 'Transaction')           Answers: The type of lock being held by an application that... Read More

1NF: Eliminate Repeating Groups Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain. 2NF:... Read More

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each... Read More

Cursor is Database Object. However, this question is very very interesting. We define cursor same was as datatypes using DECLARE statement and it can be used same way as any other datatypes. Read More

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case sensitivity, accent marks, kana... Read More

User-Defined Functions allow defining its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type. Different Kinds of User-Defined Functions created are... Read More

Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled... Read More

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. ... Read More

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... Read More

SELECT titles.pub_id, AVG(titles.price) FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id WHERE publishers.state = 'CA' GROUP BY titles.pub_id HAVING AVG(titles.price) > 10 ... Read More

SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the... Read More

SELECT * FROM table1 SELECT COUNT(*) FROM table1 SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2 Read More

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow... Read More

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... Read More

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... Read More

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that... Read More

Unindexed table or Heap. Microsoft Press Books and Book on Line (BOL) refers it as Heap. A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only... Read More

SQL Server can be linked to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group Read More

To rename db sp_renamedb 'oldname' , 'newname' If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename... Read More

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key... Read More

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... Read More

Service Broker is feature which provides facility to SQL Server to send an asynchronous, transactional message. So how it works is SQL Server uses Service Broker to send message from database to message queue. This message... Read More

Policy Management in SQL SERVER 2008 allows you to define and enforce policies for configuring and managing SQL Server across the enterprise. Policy-Based Management is configured in SQL Server Management Studio (SSMS). Navigate... Read More

Database mirroring can be used with replication to provide availability for the publication database. Database mirroring involves two copies of a single database that typically reside on different computers. At any given time,... Read More

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... Read More

MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions,... Read More

· Using CTE improves the readability and makes maintenance of complex queries easy. · The query can be divided into separate, simple, logical building blocks which can be then used to build more complex CTEs until final... Read More

In SQL Server 2008, SQL Server objects such as user-defined functions can be created using such CLR languages. This CLR language support extends not only to user-defined functions, but also to stored procedures and triggers. You... Read More

Language Integrated Query (LINQ) adds the ability to query objects using .NET languages. The LINQ to SQL object/relational mapping (O/RM) framework provides the following basic features: · Tools to create classes (usually... Read More

Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Isolation levels are... Read More

EXCEPT clause is similar to MINUS operation in Oracle. The EXCEPT query and MINUS query returns all rows in the first query that are not returned in the second query. Each SQL statement within the EXCEPT query and MINUS query... Read More

Example  : Simple TRY…CATCH without RAISEERROR function BEGIN TRY DECLARE @MyInt INT; -- Following statement will create Devide by Zero Error SET @MyInt = 1/0; END TRY BEGIN CATCH SELECT &... Read More

Master database is system database and it contains information about running server’s configuration. When SQL Server 2005 is installed it usually creates master, model, msdb, tempdb resource and distribution system database... Read More

In SQL SERVE 2008 Data Compression comes in two flavors: · Row Compression · Page Compression Row Compression Row compression changes the format of physical storage of data. It minimize the metadata (column... Read More

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server. DBCC commands are used to perform following tasks. · Maintenance tasks on database, index, or... Read More

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... Read More

Catalog views return information that is used by the SQL Server Database Engine. Catalog Views are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized... Read More

Filestream allows you to store large objects in the file system and have these files integrated within the database. It enables SQL Server based applications to store unstructured data such as documents, images, audios, videos... Read More

A dirty read occurs when two operations say, read and write occurs together giving the incorrect or unedited data. Suppose, A has changed a row, but has not committed the changes. B reads the uncommitted data but his view of the... Read More

Aggregate functions perform a calculation on a set of values and return a single value. Aggregate functions ignore NULL values except COUNT function. HAVING clause is used, along with GROUP BY, for filtering query using aggregate... Read More

TABLESAMPLE allows you to extract a sampling of rows from a table in the FROM clause. The rows retrieved are random and they are not in any order. This sampling can be based on a percentage of number of rows. You can use... Read More

ROW_NUMBER() returns a column as an expression that contains the row’s number within the result set. This is only a number used in the context of the result set, if the result changes, the ROW_NUMBER() will change. Read More

UNION The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION,... Read More

Performance Comparison. I suggest all of my readers to go here for update article. UNION The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the ... Read More

The xp_cmdshell extended store procedure makes it possible to execute operating system commands from within SQL Server. Example:   EXEC Master..xp_cmdshell 'Dir c:' Read More

With proper indexes, the TOP N clause and SET ROWCOUNT N statement are equally fast, but with unsorted input from a heap, TOP N is faster. With unsorted input, the TOP N operator uses a small internal sorted temporary table in which... Read More

By default, SQL Server 7.0 installation sets up SQL Server to run in a case insensitive mode. For most applications this may work great, but there are certain situations were case sensitive searches are required. For instance, if a... Read More

Lets break down your question into several steps. First, let's create a sample table using the following code:   create table dups (            ... Read More

You can use a variable as your IN clause, but this requires that you use the EXEC function to run the statement. Read More

The percent sign (%) is a wildcard in SQL Server. It can be used at the beginning or end of a string. So the following syntax will return all of the records you mentioned:   SELECT * FROM TestTable WHERE Col... Read More

All you need to do is compare the table to itself to find out which candidates are duplicates. Do this by assigning aliases to the table so you can use it twice, once as A and again as B, like this:   delete ... Read More

Checking whether a table exists in a Microsoft SQL Server database is easy. You can use this query:   SELECT 'x' FROM sysobjects WHERE type = 'U' and NAME = 'mytable' But... Read More

To date, Microsoft has released six different versions of SQL Server 7.0. These versions include the Desktop, Standard, and Enterprise Editions, as well as the Developer, Microsoft Developer (MSDE), and Small Business Server Editions.... Read More

You can set SQL Server to display information regarding the amount of disk activity generated by T-SQL statements. This option displays the number of scans, the number of logical reads (pages accessed), and the number of physical... Read More

Use 'Derived tables' wherever possible, as they perform better. Consider the following query to find the second highest salary from the Employees table:   SELECT MIN(Salary) FROM Employees WHERE... Read More

1.       If you have the choice of using a join or a subquery to perform the same task, generally the join is faster. But this is not always the case, you can may want to test the query using both methods... Read More

Often when creating Transact-SQL code using the WHILE statement, you can avoid using BREAK by moving a few lines of code around. If this works in your case, then by all means don't use BREAK. But if your efforts to avoid using BREAK... Read More

you can offload much (if not most) of the data processing work from the other tiers and place it on SQL Server. The more work you can perform within SQL Server, the fewer the network roundtrips that need to be made between the various... Read More

PRIVATEWhile ADO (and other VB object libraries) make database manipulation easy for the programmer, using these shortcuts can kill SQL Server performance. As a rule of thumb, encapsulate your DML (Data Manipulation Language) in... Read More

only retrieve one row at a time, making a round-trip for every row needed by the application. This can be very expensive in resources and it hurts performance. Of course, you can't always know what rows will be needed ahead of time,... Read More

This significantly reduces network traffic and speeds up query execution. Read More

This is much more efficient that sending the Transact-SQL code to SQL Server as many different messages. Read More

don't create them using ADO's methods. Instead, encapsulate the transaction in stored procedure so that it executes on the server. Read More

create them explicitly, not implicitly. Read More

• Use views and stored procedures instead of heavy-duty queries. This can reduce network traffic, because your client will send to server only stored procedure or view name (perhaps with some parameters) instead of... Read More

• Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table,... Read More

SELECT TOP 1 sal FROM (SELECT MAX(sal) AS sal FROM sal1 UNION SELECT MAX(sal) AS sal FROM sal2) a ORDER BY sal DESC Read More

SELECT name, CASE WHEN SUM(qty) IS NULL THEN 0 WHEN SUM(qty) > 0 THEN SUM(qty) END AS tot FROM [order] RIGHT OUTER JOIN product ON [order].prodid = product.prodid GROUP BY name Result: ... Read More

Indexes in databases are similar to indexes in books. In a database, an index allows the database program to find data in a table without scanning the entire table. An index in a database is a list of values in a table with... Read More

Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much. Read More

One of the hardest tasks facing database administrators is the selection of appropriate columns for non-clustered indexes. You should consider creating non-clustered indexes on any columns that are frequently referenced in... Read More

When you create a clustered index, the data in the table is stored in the data pages of the database according to the order of the values in the indexed columns. When new rows of data are inserted into the table or the... Read More

bigint Binary bit char cursor datetime Decimal float image int money Nchar ntext nvarchar real smalldatetime Smallint smallmoney text timestamp tinyint Varbinary Varchar uniqueidentifier Read More

To ensure uniqueness across machines, the ID of the network card is used (among others) to compute the number. Read More

Joins can be categorized as: • Inner joins (the typical join operation, which uses some comparison operator like = or <>). These include equi-joins and natural joins. Inner joins use a comparison operator to... Read More

A table can be joined to itself in a self-join. Read More

Code Example 1: select o.name, i.name from sysobjects o, sysindexes i where o.id = i.id Code Example 2: select o.name, i.name from sysobjects o inner join sysindexes i on o.id = i.id You will... Read More

Microsoft® SQL Server™ 2000 uses locking to ensure transactional integrity and database consistency. Locking prevents users from reading data being changed by other users, and prevents multiple users from changing the... Read More

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of... Read More

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. A lower isolation level increases concurrency, but at the expense of... Read More

a. Temporary Stored Procedures - SQL Server supports two types of temporary procedures: local and global. A local temporary procedure is visible only to the connection that created it. A global temporary procedure is... Read More

if a new index is added from which the stored procedure might benefit, optimization does not automatically happen (until the next time the stored procedure is run after SQL Server is restarted). Read More

• The sp_recompile system stored procedure forces a recompile of a stored procedure the next time it is run. • Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that... Read More

You use the FOR XML clause of the SELECT statement, and within the FOR XML clause you specify an XML mode: RAW, AUTO, or EXPLICIT. Read More

Stored procedures are nested when one stored procedure calls another. You can nest stored procedures up to 32 levels. The nesting level increases by one when the called stored procedure begins execution and decreases by one... Read More

An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using... Read More

Views can have only select statements (create, update, truncate, delete statements are not allowed) Views cannot have “select into”, “Group by” “Having”, ”Order by” Read More

1) Functions can be used in a select statement where as procedures cannot 2) Procedure takes both input and output parameters but Functions takes only input parameters 3) Functions cannot return values of type text,... Read More

Triggers are a special class of stored procedure defined to execute automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or view. Triggers are powerful tools that sites can use to enforce their... Read More

CREATE TABLE BaseTable ( PrimaryKey int IDENTITY(1,1), Color nvarchar(10) NOT NULL, Material nvarchar(10) NOT NULL, ComputedCol AS (Color + Material) ) GO --Create a view that... Read More

CREATE TRIGGER reminder ON titles FOR INSERT AS EXEC master..xp_sendmail 'MaryM', 'New title, mention in the next report to distributors.' Read More

Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as... Read More

A view is a virtual table made up of data from base tables and other views, but not stored separately. o Views simplify users perception of the database (can be used to present only the necessary information while... Read More

Views or tables participating in a view created with the SCHEMABINDING clause cannot be dropped. If the view is not created using SCHEMABINDING, then we can drop the table. Read More

The angle between the x-axis and a ray. This is a mathematical function that returns the angle between the positive x-axis and the ray that passes through the two coordinates passed in. The angle is in radians. Read More

The latency between transaction commits at the publisher and subscriber exceeds the warning level. This status can actually mean two different things. Either the latency between the commit of a transaction at the publisher and the... Read More

A GUID that identifies the database on which Service Broker is running. Each database has a Service Broker identifier. This is a GUID in the service_broker_GUID column that identifies the databases on which Service Broker is... Read More

Differential Change Map and Bulk Change Map There are two internal structures that track extents modified by bulk copy operations or that have changed since the last full backup. They are the Differential Changed Map (DCM) and the... Read More

Make a new copy of the production database each month and then copy that to the SAN. Attach it as a new Scalable Shared Database each month to the reporting servers. A Scalable Shared Database does not support database snapshots, so... Read More

SQL-DMO SMO does not support compatibility modes 60 or 65, so you would need to use DMO instead. Read More

Determines the number of partitions used in an index crawl. This option helps optimize the full-text indexing process by specifying the number of partitions the SQL Server uses during index crawls. Read More

TEXT_IN_ROW_DATA The three types of allocation units are: IN_ROW_DATA, LOB_DATA, and ROW_OVERFLOW_DATA. Each heap or index has IN_ROW_DATA which holds part of the data. LOB_DATA is used for large object data types and ROW_OVERFLOW_DATA... Read More

True Absolutely true. Users can define any portion of a filter to be a run time prompt, letting other users easily change the filter as needed. Not only is it easy to set up, Report Builder automatically populates a list of all... Read More

False False. Supported formats include Excel, CSV, XML, TIFF, MHTML, and PDF. XML or CSV could be easily imported into Access, but there is way to add the data directly from Report Builder Read More

Users can add one or more images, but they will appear in the header or footer of the report, they cannot be added to the detail row. Images that are stored as row data in the database can be rendered at the detail level. Multiple... Read More

Run Profiler Profiler will work as long as you have permissions to profile the server and is the the best solution because it requires no change to the Report Server itself. There is a way to log all report SQL to a log file, but... Read More

RAID 5 Only RAID 5 (of those listed) contains parity information. Read More

True True. Subreports can be used for a master-detail relationship, or the subreport can be a separate item, but in either case RS will query to get the data for the report once for each detail row. If end users are going to only... Read More

RESTORE VERIFYONLY After a backup file has been written to disk or tape, its integrity can be checked with the RESTORE VERIFYONLY command. This command will verufy the backup set is complete and that the files are readable. Read More

The current filter and the number of rows that matched that filter By default, the current filter definition and the number of rows that matched the filter are added to the end of the report. They can be removed and added back as... Read More

Triple DES When you create a Database Master Key, it is encrypted using the password you supply with the Triple DES algorithm. Read More

Use the WSDL=DEFAULT parameter in the create endpoint statement. When using the CREATE ENDPOINT command, you can specify the WSDL=DEFAULT parameter to generate a default WSDL response or use WSDL="spname" where spname is the name... Read More

No A database master key is not created when a database is created. It must be created by an administrator. Read More

Objects from unsigned CLR assemblies with EXTERNAL_ACCESS or UNSAFE permissions In order to execute objects from CLR assemblies with EXTERNAL_ACCESS or UNSAFE permissions, the assemby must either be signed with a key pair or the... Read More

It means the sets in this file are accent sensitive. The diacritics_sensitive determines if the terms in the expansion and replacement sets are accent sensitive. A value of 0 means accent insensitive and a value of 1 means accent... Read More

Build and deploy a model Report Builder requires a model and that model cannot be defined using Report Builder. The advantage of using models is that users do not need to understand SQL statements to build a report (but they will... Read More

One click deployment launched from a menu on the Report Manager home page One click deployment is fast and easy, users click the menu on the Report Manager page. It is not a web application, but rather a .Net Winform application. ... Read More

HTTP endpoints Native XML Web Services in SQL Server 2005 require HTTP endpoints to communicate with clients. Read More

Expansion sets are synonyms for the search term and returned as results if they appear along with the search term. The expansion set is the group of values that are synonyms and can be substituted for the search term. For example,... Read More

CREATE PROCEDURE sp_who AS PRINT 'SURPRISE' GO EXECUTE sp_who Information about current SQL Server users and processes is displayed. The explanation could be found in the Books Online under Creating a... Read More

A report model provides familiar business names for database tables and fields A Report model provides business names and terms for database fields and tables. It allows for predefined relationships between tables as well as grouping... Read More

Opens SQL Server 2005 Management Studio. sqlwb.exe actually opens Management Studio and can be configured to optionally open a solution, project, or script file when it starts. Read More

It supports BLOB object data. The Raw File Destination connection does not use a connection manager, supports NULL data, and only has one input. It also does not support BLOB data or have an error output. Read More

These are defined for each contract. Each application that sets up queues and contracts inside Service Broker must define the message types that are valid for the contract. Read More

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... Read More

The maximum precision for numeric and decimal data. This function returns the maximum precision for numeric and decimal data as set on the server. The default for SQL Server 2005 is 38. Read More

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. Read More

None of the above Explanation from BOL: SQL Server 2005 does not enforce the .mdf, .ndf, and .ldf file name extensions, but these extensions help you identify the different kinds of files and their use. Read More

Notification Services installation has a 10 minute timeout and the database creation probably took longer than that. The Notification Services timeout for database creation is 10 minutes. If the database takes longer to create, the... Read More

A backup of the database holding Notification Services and the XSD and XSLT files as well as the name and password for the service account. To recover SSNS, you need the database backup as well as the operational files, XSD and... Read More

EXTERNAL_ACCESS The most restrictive permission set should always be used for .NET assemblies in keeping with a secure SQL Server environment. Only the EXTERNAL_ACCESS and UNSAFE permission sets will allow registry access and UNSAFE... Read More

(DT_STR, 20, 1252)[MyColumn] The correct answer is (DT_STR, 20, 1252)[MyColumn]. The cast uses parens and then the cast type specification with the _expression to be converted afterwards. Read More

Push/Pull The two types of subscriptions are push and pull. Read More

makepipe The makepipe utility is used with the readpipe utility to test connectivity only for named pipes connections. This utility has been deprecated in SQL Server 2005. Read More

Article Each set of source data that is replicated is called an article. Read More

External columns represent the meta data of external data sources and output columns are used be data flow source adapters. Output columns are used by all data-flow source adapters and transformations. They are not used by destination... Read More

1433 and a specific port after configuring the named instance to use that specific port Named instances must be configured to use a specific port and that port then opened in the firewall. Read More

png Images stored originally in formats other than jpg are rendered in png format. Read More

A server login connects an account to the server. A database user the link from the server login to a defined database . You need to create a server login using CREATE LOGIN before you can then link it to a database using a... Read More

varchar(max) The XML data type changes the representation of your XML document into an internal, optimized version that has all the content, but may not maintain the original text. The varchar(max) or nvarchar(max) represenations... Read More

With the <!-- and --> markers. Comments in an XML document are denoted just as they are in HTML with the markers. Read More

Like a queue, first in, first out. Messages transferred in Service Broker work in a queue fashion with the first message being sent being the first processed. Read More

The service is installed once for each instances on the server. An instance aware service is one that is installed for each instance on the server. An instance-unaware service is only installed once , no matter how many instances.... Read More

Sends a service broker message using a conversation. SEND is used to send a message on an existing conversation in the Service Broker architecture. Read More

Answer Certificates Explanation Certificates have an expiration data associated with them. This allows you to issue them to users and force a reissue in order to maintain their access to data. Read More

Select * from dbo .MyTable All records from [dbo].[MyTable] The answer is all records from [dbo].[MyTable]. Qualified names can include white space before and after the dots. Restrictions apply only to the names... Read More

CLR code running with UNSAFE permissions can access virtually any resource inside or outside of SQL Server without restriction. This is one reason only a sysadmin can create an assembly as UNSAFE. Read More

sysadmin Only a sysadmin can create assemblies that are specified as unsafe. Read More

Log and data files for your databases as well as the Quorum drive and the backup folders. Anti-virus programs can exist on the same server as SQL Server without an issue if you exclude certain items. The database data and log files... Read More

Port 445 Named pipes communicates across TCP port 445. Read More

The computer account of the Windows installation. The network service account connects to network resources as the computer account for the Windows installation. Read More

1. Use as much as possible WHERE clause filters. Where Clause is the most important part for optimization 2. Select only those fields which really require. 3. Joins are expensive in terms of time. Make sure that use all the keys... Read More

1) Delete keep the lock over each row where Truncate keps the lock on table not on all the row 2) Counter of the Identity column is reset in Truncate where it is not reset in Delete. 3) Trigger is not fired in Truncate where... Read More

 In TRUNCATE we can not rollback.. In DELETE we can rollback Read More

Using joins, we can get the data from two or more tables based on logical condition between the tables. Use following article to know about all types of joins Read More

We can use FOR XML statement at the end of the query to return xml data from the SQL Server. select * from mytable for xml auto There are three mode of returning XML and they are auto, raw and explicit Read More

Unique Key Constraint: The column values should retain uniqueness. It allows null values in the column. It will create non-clustered index by default. Any number of unique constraints can be added to a table. Primary... Read More

Use following code Supported in SQL Server 2000 and 2005 exec sp_renamedb "test", "test1" Supported in SQL Server 2005 and later version ALTER Database "test1" Modify Name="test" Read More

 Implicit Transaction is the auto commit. There is no beginning or ending of the transaction. Explicit Transaction has the beginning, ending and rollback of transactions with the command Begin Transaction Commit... Read More

DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it... Read More

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major... Read More

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and... Read More

you can write following sql statement select category.*, categoryparent.categoryparent from category, categoryparent where category.categoryparentid = categoryparent.autoid I am assuming here that category... Read More

 Primary key are used with Foreign key to enforce referential integrity. Unique constraints allows nulls to be inserted into the field. But there can't be null in Primary key. Read More

Delete statement removes rows of a table one by one & delete triggers on that table fires. But Truncate removes all rows by deallocating the data pages assigned to the table & only these deallocation are recorded in the... Read More

Replication has a number of maintenance jobs that perform scheduled and on-demand maintenance. Read More

What are CODD rules? Is access database a RDBMS? What’s the main difference between ACCESS and SQL SERVER? What’s the difference between MSDE and SQL SERVER 2000? What is SQL SERVER Express 2005 Edition? What is SQL... Read More

Which are namespaces for ADO.NET? Can you give a overview of ADO.NET architecture ? What are the two fundamental objects in ADO.NET ? What is difference between dataset and datareader ? What are major difference between... Read More

What are notification services? What are basic components of Notification services? Can you explain architecture of Notification Services? Which are the two XML files needed for notification services? What is Nscontrols command? ... Read More

What is “Data Warehousing”? What are Data Marts? What are Fact tables and Dimension Tables? What is Snow Flake Schema design in database? What is ETL process in Data warehousing? How can we do ETL process in SQL Server? ... Read More

Can you explain how can we make a simple report in reporting services? How do I specify stored procedures in Reporting Services? What is the architecture for “Reporting Services “? Read More

This feature enables us to execute C# or VB.NET code in the DBMS to take advantage of the .NET functionality. This feature gives more flexibility in writing complex stored procedures, functions, and triggers that can be written in ... Read More

SQL Server 2005 Reporting Services is a key component of SQL Server 2005 that provides customers with an enterprise-capable reporting platform. This comprehensive environment is used for authoring, managing, and delivering reports to... Read More

SQL Server 2005 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality through a combination of server and client technologies, further reinforced through the use of a... Read More

Online Transaction Processing (OLTP) relational databases are optimal for managing changing data. When several users are performing transactions at the same time, OLTP databases are designed to let transactional applications write... Read More

SQL Server 2005 introduces a new “snapshot” isolation level that is intended to enhance concurrency for online transaction processing (OLTP) applications. In prior versions of SQL Server, concurrency was based solely on locking,... Read More

These are collection of objects that are made for programming all aspects of managing Microsoft SQL Server 2005. SMO is a .NET based object model. It comes with SQL Server 2005 as a .Net assembly named Microsoft.SqlServer.Smo.dll. We... Read More

Data Definition Language CREATE, ALTER, DROP, TRUNCATE, REVOKE, NO AUDIT & COMMIT Data Manipulation Language ... Read More

     Join is a query which retrieves related columns or rows from multiple tables. Self Join Joining the... Read More

Retrieving rows in hierarchical order. Example: select empno, ename from emp; Read More

The Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. It enable us to retrieve the data from based on our... Read More

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... Read More

Triggers are precompiled statements similar to Stored Procedure. It will automatically invoke for a particular operation. Triggers are basically used to implement business rules. Read More

There are basically two types of indexes that we use with the SQL ServerClustered - 1. It will format the entire table, inturn physically sort the table. 2. Only one clustered index can be created for a table. 3.... Read More

User Identification: In virtue of Data base server Identification tries to find whether you are registered user in the database or not. It applies to whole database server. Authentication: It applies for special Privileges for a user in... Read More

Trigger are storage procedure that database should take action when relevant event occur<br><br> In other word we can say trigger are stored procedure that fire when the relevant event occur such as insertion , deletion, updation. Read More

XML for Analysis (XMLA) is a Simple Object Access Protocol (SOAP)-based XML protocol, designed specifically for universal data access to any standard multidimensional data source residing on the Web. XMLA also eliminates the need to deploy a client... Read More

Question : How to replace particular string. For example:- suppose their are 1000 of records for email-id. like tarun.it@ hotmail.com..but i want to replace the only hotmail.com for all email-ids not the user name(tarun.it) with yahoo.com.... Read More

When creating a form make sure that the "form" element "onSubmit" attribute is set to a JavaScript function that returns false. <form onSubmit="doAJAXSubmit();return false;" > <input type="text" id="tf1" /> <input type="submit" id=... Read More

The current filter and the number of rows that matched that filter By default, the current filter definition and the number of rows that matched the filter are added to the end of the report. They can be removed and added back as needed. Read More

In the directory stored in the system variable TEMP. Profiler uses the location specified for the TEMP system variable. Read More

Question : I was asked in an interview... "Suppose u have a field with UNIQUE KEY. can u leave two or more cells NULL in SQL Server 2005...If Yes then Why...if NOT then Why.....         Answers: No, it can... Read More

GUIDs are created by the newid() function. If the default of the primary key is set to NewID(),a new GUID is generated for any new row. In addition, the newid() function may be declared within an insert/values list. The newid() function will even... Read More

select * from emp group by eno having sal > 5000 Read More

Changes if any from the subscriber and publisher are tracked and these changes are synchronized between both of them. In case the same data is changed by the publisher and subscriber then this will result in an error which has to be manually... Read More

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... Read More

select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' Read More

This integration feature is used to integrate data from various sources. This is chiefly used for business analysis (data ware housing). This includes GUI tools which help in building data extraction workflows, duplication and merging data, etc. This... Read More

In inner join it returns only the matching rows of joining table but in full outer join it returns matching rows and include all the unmatched row from both table look as following exemple if we are joining two table one have 5 rows and other... Read More

Microsoft Visual studio provides supports data programming with Microsoft Visual studio. This can be used to write and debug code which can be executed by SQL CLR. A data designer is provided which can create, view and design data bases. Read More

T-SQL is used to program and manage SQL server. Key operations such as creating, altering, entering and editing can be performed with the help of T-SQL. SQL Server functionality can be leveraged by sending T-SQL queries to the server which are then... Read More

Extended Stored procedure is a function in a DLL that can be called from T- SQL in a similar manner as we call normal stored procedure.Can accept parameter and return value as well. Read More

OLAP and data mining services are provided for SQL server databases. MOLAP, HOLAP and ROLAP are supported by OLAP engine. Using MDX queries cube data can be accessed. This service includes algorithms, clustering algorithm, time series analysis,... Read More

Select name from sysobjects where type="U" This gives the list of user defined tables in the current database. Read More

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. Read More

Question : What is joins? What is the use? Where it is use?          Answers: join provides relationship between two or more table. If we want to access data from more then one table at a time we use... Read More

Question : You have installed a US English SQL Server 2000 instance with the default options, collation, and sorting. What does this return: create table MyTable ( Mychar varchar(20)) go insert Mytable select 'Apple' ... Read More

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline... Read More

Properties of Sub-Query · A sub-query must be enclosed in the parenthesis. · A sub-query must be put in the right hand of the comparison operator, and · A sub-query cannot contain an ORDER-BY clause. · A query... Read More

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... Read More

Yes we can write using Common Table Expression (CTE). 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... Read More

Using the NOLOCK query optimizer hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The... Read More

A connection is blocked when it requires an object that another connection has a lock on. You can use the system stored procedure sp_lock to retrieve information about the current locks in SQL Server, and you can use the server... Read More

CREATE TABLE X ( X_ID int IDENTITY(1,1), X_DESC text (60000) NOT NULL ) GO   Answer: SQL Server is returning a bogus error message. The real error has to do with your syntax. When specifying text you don't... Read More

In two-tier designs, put the business logic in stored procedures on the server. In n-tier designs, put the business logic in components on MTS. Read More

When the data is first requested by the client from the server (ideally using a stored procedure), have all the data the client wants to "play" with sent to the client. Once the recordset is at the client, then ADO methods can... Read More

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... Read More

Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being... Read More

INSERT NonFatal VALUES (@Column2) IF @@ERROR <>0 BEGIN PRINT 'Error Occured' END Read More

Function is a saved Transact-SQL routine that returns a value. User-defined functions cannot be used to perform a set of actions that modify the global database state. Userdefined functions, like system functions, can be... Read More

The DCM tracks changes. The differential backup reads the extents from this structure. A differential backup uses the Differential Change Map to determine which extents have changed and need to be include in the backup. This... Read More

TIFF TIFF is the correct answer. By rendering as an image there are no concerns about different fonts, problems with page breaks, etc. PDF's are almost as good and more commonly used of course, with the advantage that document... Read More

Yes, but only for simple subscriptions. Notification Services can be called from unmanaged code through a COM interface for simple subscriptions only. Condition based subscriptions are not supported through COM Interop. Read More

1 A credential can be mapped to many logins, but a login can only be mapped to one credential. Read More

It allows the grantee the equivalent of ownership in that they have all permissions and can grant them to others. The Control permission is equivalent to assigning ownership of the securables. All available permissions are granted... Read More

Phantom rows The answer is Phantoms rows. A phantom row refers to the situation where you execute a DML statement that retrieves data, and another process may add new rows the result set. For example: SET ISOLATION LEVEL... Read More

An operation where the row in the heap or clustered index is found from the bookmark in the index. A bookmark ID is stored in an index and points back to the actual row in the heap or clustered index of the table. The lookup operation... Read More

Answer Ciphertext Explanation Ciphertext is larger and can be calculated using the following formula: Size = ( FLOOR (8 + D)/BLOCK) + 1) * (BLOCK + BLOCK + 16) where D is the data size in bytes, BLOCK is the block... Read More

 DESCRIBE is used to see table structure. In SQL server 2005 we can use sp_columns, sp_tables or sp_help. sp_columns will show list of columns and its details in table. ... Read More

Blocking Transformation The Sort component is a blocking transformation meaning that it needs all rows in order to perform its function. Read More