SQL_Server_Interview

Differentiate between a Local and a Global temporary table?

- 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. - Global temporary tables (created with a double "##") are visible to all sessions. - Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it.

What do you understand by a view? What does the WITH CHECK OPTION clause for a view do?

- A view is a virtual table that consists of fields from one or more real tables. - It is usually used to join multiple tables and get the data. - The WITH CHECK OPTION for a view prevents any modification to the data that does not confirm to the WHERE clause of the view definition. - This allows the data belonging to the view to be updated through the view. SQL Server's views are updateable, but it is possible that when you insert a new record or update an existing record, the record added or modified doesn't logical belog to the view any longer. For example, consider the following view: CREATE VIEW authors_CA AS ( SELECT * FROM Authors WHERE state='CA' ) Now any insert or update operation that makes a record disappear from the view raises a trappable runtime error.

What do you mean by ACID?

- ACID (Atomicity Consistency Isolation Durability) is a quality sought after in a reliable database. Here's the relevance of each quality: - Atomicity is an all-or-none proposition. - Consistency - it guarantees that your database is never left by a transaction in a half-finished state. - Isolation - it keeps transactions separated from each other until they're finished. - Durability - it ensures that the database keeps a track of pending changes in a way that the server can recover from an abnormal termination.

Differentiate between a primary key and a unique key.

- By default, clustered index on the column are created by the primary key whereas nonclustered index are created by unique key. - Primary key doesn't allow NULLs, but unique key allows one NULL.

What is the function of SQL Server Agent Windows service?

- It is a Windows service which handles the tasks scheduled within the SQL Server environment. These tasks are also called as job and are stored with in SQL server. The jobs may run through a trigger, a predefined schedule or on demand. - This service is very useful in determining why a particular job did not run as intended.

What are the advantages of using Stored Procedures?

- They help in reducing the network traffic and latency which in turn boosts application performance. - They help in promoting code reuse. - They provide better security to data. - It is possible to encapsulate the logic using stored procedures. This allows to change stored procedure code without affecting clients. - It is possible to reuse stored procedure execution plans, which are cached in SQL Server's memory. This reduces server overhead.

Differentiate between DELETE and TRUNCATE.

- Truncate can not be rolled back while Delete can be. - Truncate keeps the lock on table while Delete keeps the lock on each row. - Truncate resets the counter of the Identity column while Delete doesn't do so. - Trigger is not fired in Truncate while it happens in Delete.

When is the UPDATE_STATISTICS command used?

- When the processing of large data is done, this command is used. - Whenever large number of deletions, modification or copy takes place into the tables, the indexes need to be updated to take care of these changes. UPDATE_STATISTICS performs this job

What is a transaction?

A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program. A transaction is the propagation of one or more changes to the database. For example, if you are creating a record or updating a record or deleting a record from the table, then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors. Practically, you will club many SQL queries into a group and you will execute all of them together as a part of a transaction.

Define Triggers.

A trigger is a special type of event driven stored procedure. It gets initiated when Insert, Delete or Update event occurs. It can be used to maintain referential integrity. A trigger can call stored procedure. Executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table. You can specify which trigger fires first or fires last using sp_settriggerorder. Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens. Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks

What is the actions prevented once referential integrity is enforced?

Actions prevented are: Breaking of relationships is prevented once referential integrity on a database is enforced. Can't delete a row from primary table if there are related rows in secondary table. Can't update primary table's primary key if row being modified has related rows in secondary table. Can't insert a new row in secondary table if there are not related rows in primary table. Can't update secondary table's foreign key if there is no related row in primary table.

Explain service broker activation

Activation [purpose] You use the activation feature of Service Broker to specify a stored procedure that will handle messages destined for a particular service. [implementation] When messages arrive for a service, Service Broker checks whether there is a stored procedure running that can process the messages. If there isn't a running message-processing stored procedure, Service Broker starts one. The stored procedure then processes messages until the queue is empty, after which it terminates. Moreover, if Service Broker determines that messages are arriving faster than the stored procedure can process them, it starts additional instances of the stored procedure until enough are running to keep up with the incoming messages (or until the configured maximum number is reached). [benefit] This ensures that the right number of resources for processing incoming messages are always available.

What are the ways to controlling Cursor Behavior?

Answer Cursors behavior can be controlled by dividing them into cursor types: forward-only, static, keyset-driven, and dynamic. SQL server supports keywords SCROLL and INSENSITIVE to define cursor behavior in terms of scrollability and sensitivity. Sql server - What are the ways to controlling Cursor Behavior? - Feb 11, 2010 at 11:55 AM by Shuchi Gauri What are the ways to controlling Cursor Behavior? There are 2 ways to control Cursor behavior: Cursor Types: Data access behavior depends on the type of cursor; forward only, static, keyset-drive and dynamic. Cursor behaviors: Keywords such as SCROLL and INSENSITIVE along with the Cursor declaration define scrollability and sensitivity of the cursor.

What are the capabilities of Cursors?

Answer Cursors can support various functionalities that are listed here. Cursor allow to position at specific rows of the result set. Cursor can retrieve one or more rows in the result set. Cursor allows modification to the rows in the result set. Sql server - What are the capabilities of Cursors? - Feb 11, 2010 at 11:55 AM by Shuchi Gauri What are the capabilities of Cursors? Capabilities of cursors: Cursor reads every row one by one. Cursors can be used to update a set of rows or a single specific row in a resultset Cursors can be positioned to specific rows. Cursors can be parameterized and hence are flexible. Cursors lock row(s) while updating them.

define lock modes

Shared (S) Used for read operations that do not change or update data, such as a SELECT statement. Update (U) Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later. Exclusive (X) Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time. Intent Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX). Schema Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S). Bulk Update (BU) Used when bulk copying data into a table and the TABLOCK hint is specified. Key-range Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

Explain the WITH common_table_expression

Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. A common table expression can include references to itself. This is referred to as a recursive common table expression. Syntax [ WITH <common_table_expression> [ ,...n ] ] <common_table_expression>::= expression_name [ ( column_name [ ,...n ] ) ] AS ( CTE_query_definition )

What are the differences among batches, stored procedures, and triggers?

Answer A batch is a group of one or more SQL statements. SQL Server compiles the statements of a batch into a single executable unit, called an execution plan. The statements in the execution plan are then executed one at a time. A stored procedure is a group of SQL statements that is compiled one time and can then be executed many times. A trigger is a special type of stored procedure that is not called directly. Trigger is fired each time row is affected by Insert, Update or Delete command. Sql server - What are the differences among batches, stored procedures, and triggers? - Feb 11, 2010 at 11:55 AM by Shuchi Gauri What are the differences among batches, stored procedures, and triggers? Batch Stored Procedure Triggers Collection or group of SQL statements. All statements of a batch are compiled into one executional unit called execution plan. All statements are then executed statement by statement. It's a collection or group of SQL statements that's compiled once but used many times. It's a type of Stored procedure that cannot be called directly. Instead it fires when a row is updated, deleted, or inserted.

What are the rules to use the ROWGUIDCOL property to define a globally unique identifier column?

Answer A table can have only one ROWGUIDCOL column A ROWGUIDCOL column must be defined by using the uniqueidentifier data type. To insert a globally unique value, we need to apply DEFAULT definition on the column that uses the NEWID function to generate a globally unique value. To enforce uniqueness in ROWGUIDCOL column, the UNIQUE constraint should be used. Sql server - rules to use the ROWGUIDCOL property to define a globally unique identifier column - Feb 11, 2010 at 11:55 AM by Shuchi Gauri What are the rules to use the ROWGUIDCOL property to define a globally unique identifier column? Only one column can exist per table that is attached with ROWGUIDCOL property. One can then use $ROWGUID instead of column name in select list.

What security features are available for stored procedures?

Answer Database users can have permission to execute a stored procedure without being granted permissions to access the database objects on which the stored procedure operates. Database users can be restricted from reading SQL command in the stored procedure by encrypting it. Sql server - What security features are available for stored procedures? - Feb 11, 2010 at 11:55 AM by Shuchi Gauri What security features are available for stored procedures? Security features for stored procedures: Grants users permissions to execute a stored procedure irrespective of the related tables. Grant users users permission to work with a stored procedure to access a restricted set of data yet no give them permissions to update or select underlying data. Stored procedures can be granted execute permissions rather than setting permissions on data itself. Provide more granular security control through stored procedures rather than complete control on underlying data in tables.

Explain trigger classes i.e. instead of and after trigger.

Answer INSTEAD OF: Cause the trigger to fire instead of executing the triggering event or action. It prevents unnecessary changes to be made. Example: Causes the trigger to fire instead of the update (action) CREATE TRIGGER Employee_update ON Employee INSTEAD OF UPDATE AS { TRIGGER Definition } AFTER: execute following the triggering action, such as an insert, update, or delete. These triggers are fired a little late in the process. Example: Causes the trigger to fire instead of the update (action) CREATE TRIGGER Employee_update ON Employee AFTER UPDATE AS { TRIGGER Definition } What are the instances when triggers are appropriate? Answer When security is the top most priority. i.e. to allow unauthorized access When backups are essential When Maintenance is desired. Triggers can be fired when any error message is logged Keeping the database consistent.

Describe when checkpoints are created in a transaction log

Answer It is created when CHECKPOINT statement is executed. It is created database option is changed using ALTER DATABASE. It is created when SQL Server is stopped by executing a SHUTDOWN statement. SQL Server periodically generates automatic checkpoints in each database to reduce the amount of time the instance would take to recover the database Sql server - Describe when checkpoints are created in a transaction log. - Feb 11, 2010 at 11:55 AM by Shuchi Gauri Describe when checkpoints are created in a transaction log. Activities causing checkpoints are: When a checkpoint is explicitly executed. A logged operation is performed on the database. Database files have been altered using Alter Database command. SQL Server has been stopped explicitly or on its own. SQL Server periodically generates checkpoints. Backup of a database is taken.

What are the guidelines to use bulk copy utility of SQL Server?

Answer While importing data, the destination table must already exist. While exporting to a file, bcp will create the file. Need permissions to bulk copy data for source and destination files and tables. Need INSERT and SELECT permissions to bulk copy data from a data file into a table. Sql server - What are the guidelines to use bulk copy utility of SQL Server? - Feb 11, 2010 at 11:55 AM by Shuchi Gauri What are the guidelines to use bulk copy utility of SQL Server? Bulk copy is an API that allows interacting with SQL Server to export/import data in one of the two data formats. Bulk copy needs sufficient system credentials. Need INSERT permissions on destination table while importing. Need SELECT permissions on source table while exporting. Need SELECT permissions on sysindexes, sysobjects and syscolumns tables. bcp.exe northwind..cust out "c:\cust.txt" -c -T Export all rows in Northwind.Cust table to an ASCII-character formatted text file.

Ways of moving data/databases between servers and databases in SQL Server.

BACKUP/RESTORE, Dettach/attach of databases, Replication, DTS, BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT scripts to generate data

What are the Different SQL JOINs?

Different SQL JOINs Before we continue with examples, we will list the types the different SQL JOINs you can use: INNER JOIN: Returns all rows when there is at least one match in BOTH tables LEFT JOIN: Return all rows from the left table, and the matched rows from the right table RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table FULL JOIN: Return all rows when there is a match in ONE of the tables Example SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Define Distributed Query and Linked Server?

Distributed Query Distributed Query is a query which can retrieve data from multiple data sources including distributed data. SQL Server supports distributed query through the use of OLE DB provider. OLE DB provider exposes data in the row sets which can be used in SQL Statement. SQL Server can use distributed query in the SQL Statement using Linked Server It is the virtual server that is created to access OLE DB data source. It includes all the information needed to access OLE DB data source. Linked server definition contains all the information needed to locate OLE DB data source. You can join remote data and local data using Linked Server. Ad doc computer Name Ad doc computer Name is used with infrequent distributed queries that are not defined with linked server name. Distributed Queries - October 24, 2008 at 18:10 pm by Rajmeet Ghai What is Distributed Queries? Distributed queries can access data from different data sources. These sources can reside on the same server or a different server. This means that distributed queries can query multiple databases. What is a linked server? A linked server allows remote access. They have the ability to issue distributed queries, update, commands, and transactions across different data sources. A linked server has an OLE DB provider and data source. Explain OPENQUERY function and OPENROWSET function. OPENQUERY: - Used to execute the query passed on the linked server. Syntax: OPENQUERY (Linked_server_identifier, query). It can also be refernced from a FROM clause of selecte query. e.g. Select * from OPENQUERY (Oracleserver, 'select fname, FROM Employee); OPENROWSET: - Used to access tables in a linked server. It can be used one time for accessing remote data by using OLE DB. It includes all the information required to access the remote data. Syntax: OPENROWSET ( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' } , { [ catalog. ] [ schema. ] object | 'query' } | BULK 'data_file' , { FORMATFILE = 'format_file_path' [ ] | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB } } ) Sql server - Define Distributed Query and Linked Server - Feb 11, 2010 at 11:55 AM by Shuchi Gauri Define Distributed Query and Linked Server Linked servers allow SQL Server to access data from remote data sources. One can execute queries, perform data modifications, and also execute remote procedural calls using Linked servers. Queries executed through Linked servers are termed as distributed queries. These remote data sources are connected through an OLEDB provider. The types of distributed queries which can be processed is thus based on how the OLEDB provider was designed.

How do we encrypt data between Dialogs?

How do we encrypt data between Dialogs? Using Dialog Security the dialogues can be encrypted at the initiator and then decrypted at the target. Dialogue security can be implemented by implemented as full dialogue security or anonymous dialog security. In full dialogue, both the database that initiates the service and database that calls the service to be implemented needs to have two users. Each service in the conversation is bound by a remotes service. A certificate is associated with each user, so that messages can be encrypted using the recipient's public key and decrypted using the corresponding private key. If anonymous dialog security is used, target service need not explicitly permit access to a remote user. A guest account can be used to generate a session key and encrypt the message.

How does the report manager work in SSRS?

How does the report manager work in SSRS? Report manager is a web application. In SSRS it is accessed by a URL. The interface of this Report manager depends on the permissions of the user. This means to access any functionality or perform any task, the user must be assigned a role. A user with a role of full permissions can entire all the features and menus of the report. To configure the report manager, a URL needs to be defined.

How to implement Service Broker?

How to implement Service Broker? Using Transact SQL statements service broker can be used. An application can be implemented as a program running outside of SQL Server or as a stored procedure. Service broker uses a number of components to perform a task. A program that starts a conversation creates and sends a message to a service that may or may not respond. Any application that intends to use Service broker does not need any special object model or library. Transact-SQL commands to SQL Server can be sent for processing.

Define Identity and uniqueidentifier property of Column

Identity Column Column with identity property contains unique system generated value in the table. Column with identity property is similar to AutoNumber field in MS Access. You can set start and increment value while setting column property to identity. Column with identity property contains unique values within the table. Uniqueidentifier, GUID The column with uniqueidentifier property contains globally unique data. SQL server uses uniqueidentifier property for merge replication. Same like Identity Column, SQL server supports uniqueidentifier column that also contains system generated value. A column or local variable of uniqueidentifier data type can be initialized using NEWID function. Sql server - Define Identity and uniqueidentifier property of Column. - Feb 11, 2010 at 11:55 AM by Shuchi Gauri Define Identity and uniqueidentifier property of Column. Uniqueidentifier: They are also known as GUIDs and are guaranteed to return unique values across space and time. It is natively stored as a 16 byte binary value. On performing an insert operation, Uniqueidentifier does not get automatically generated for the new row, instead the user has to mention a statement like SET @Guid=NEWID() to get the new value. Indexes built on them are bigger and slower since they are a big datatype. Identity: Identity property allows automatic generation of a unique number. The user does not have to code a statement for a newly inserted row to get its identity value. It's natively a 4 byte integer value and hence enables indexes built on them to be smaller and much faster than the UniqueIdentifiers. SQL Server Identifier What is SQL Server Identifier? What are classes of identifier? Explain each class with an example i.e. regular and delimited identifier. What are the Rules for Regular Identifiers?......... What happens when my integer IDENTITY runs out of scope? An integer data type can have a variety of IDENTITY values like int, bigint, tinyint etc. ......... What is index? Define its types. Index can be thought as index of the book that is used for fast retrieval of information. Index uses one or more column index keys and pointers to the record to locate record......... What are the lock types? Shared Lock allows simultaneous access of record by multiple Select statements. Shared Lock blocks record from updating and will remain in queue waiting while record is accessed for reading......

Define Atomicity

In database systems, atomicity (or atomicness; from Greek a-tomos, undividable) is one of the ACID transaction properties. In an atomic transaction, a series of database operations either all occur, or nothing occurs. The series of operations cannot be divided apart and executed partially from each other, which makes the series of operations "indivisable", hence the name. A guarantee of atomicity prevents updates to the database occurring only partially, which can cause greater problems than rejecting the whole series outright. In other words, atomicity means indivisibility and irreducibility.[1]

How do you implement one-to-one in SQL Server?

One to one is implemented using single table by establishing relationship between same type of columns in a table. Implemented using two tables with primary key and foreign key relationships. Implemented using a junction table. - The keys from both the tables form composite primary key of the junction table.

Explain the MERGE function

Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table. example SELECT col, col2 FROM tbl_B WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col);

What are the properties of the Relational tables?

Relational tables have six properties: 1. Values are atomic. 2. Column values are of the same kind. 3. Each row is unique. 4. The sequence of columns is insignificant. 5. The sequence of rows is insignificant. 6. Each column must have a unique name.

Explain the architecture of SQL Reporting service.

Reporting Services runs as a middle-tier server, as part of your existing server architecture. SQL Server 2000 should be installed for the database server, and Internet Information Services 6.0 as a Web server. The report server engine takes in report definitions, locates the corresponding data, and produces the reports. Interaction with the engine can be done through the Web-based Report Manager, which also lets you manage refresh schedules and notifications. End users view the report in a Web browser, and can export it to PDF, XML, or Excel.

Explain the SQL PRIMARY KEY Constraint

SQL PRIMARY KEY Constraint The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain unique values. A primary key column cannot contain NULL values. Most tables should have a primary key, and each table can have only ONE primary key example CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) ) SQL PRIMARY KEY Constraint on ALTER TABLE To create a PRIMARY KEY constraint on the "P_Id" column when the table is already created, use the following SQL: MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persons ADD PRIMARY KEY (P_Id) To DROP a PRIMARY KEY Constraint To drop a PRIMARY KEY constraint, use the following SQL: MySQL: ALTER TABLE Persons DROP PRIMARY KEY SQL Server / Oracle / MS Access: ALTER TABLE Persons DROP CONSTRAINT pk_PersonID

What is SQL Profiler?

SQL Profiler is a workload recording tool to feed into the Database Engine Tuning Advisor

Define SQL Server. Define Database.

SQL Server - Define SQL Server. Define Database - Jan 14, 2010 at 09:00 AM by Nishant Kumar Define SQL Server. Define Database. SQL server is an RDBMS that uses Transact-SQL to interact with the client application. It includes database, database engine and the application that are used to manage data. Data is organizes into a table with rows and columns. Define Database. A database stores data and it is just like a data file. Database systems are more powerful than data files because the data is more highly organized. In a well-designed database, there is no scope for duplicate pieces of data. Related pieces of data are grouped together in a single structure or record.

SQL Server Reporting Services vs Crystal Reports.

SQL Server Reporting Services vs Crystal Reports. Crystal reports are processed by IIS while SSSR have a report server. Caching in Crystal reports is available through cache server. On the other hand, caching in SSSR is available for Report history snapshots. Crystal reports have standards and user defined field labels. SSSR allows only user defined field labels.

What are the lock types?

SQL server supports following locks Shared lock Update lock Exclusive lock Shared lock Shared Lock allows simultaneous access of record by multiple Select statements. Shared Lock blocks record from updating and will remain in queue waiting while record is accessed for reading. If update process is going on then read command will have to wait until updating process finishes. Update locks This lock is used with the resources to be updated. Exclusive locks This kind of lock is used with data modification operations like update, insert or delete. Sql server - What are the lock types? - Feb 11, 2010 at 11:55 AM by Shuchi Gauri What are the lock types? Main lock types: Shared: Applied to read only operations where the data is not modified. E.g.: Select statements. Update: Applied to resources which can be updated. It resolves dead locks in case of multiple sessions are reading, locking or updating resources later. Exclusive: Used for operations involving data modification. E.g.: Insert, Update, and Delete. This ensures that multiple updates are not made to the same data at the same time. Intent: Establishes a lock hierarchy. E.g.: Intent shared Intent exclusive and Shared with intentexclusive. Schema: Used when schema dependent operations are being executed. E.g.: Schema modification and Schema stability. Bulk update: Used while bulk copying of data and Tablock is specified. Explain the use of NOLOCK query optimizer hint. NOLOCK table hint used using WITH clause, is similar to read uncommitted. This option allows dirty reads are allowed............... SQL Server DBA What are the steps to take to improve performance of a poor performing query? What is a deadlock and what is a live lock? How will you go about resolving deadlocks? What is blocking and how would you troubleshoot it? Explain the different types of BACKUPs available in SQL Server. What is database isolation in SQL Server?........... Describe in brief Databases and SQL Server Databases Architecture. A database is a structured collection of data. Database can be thought as simple data file...... Define Distributed Query and Linked Server? Distributed Query is a query which can retrieve data from multiple data sources including distributed data........ Define Identity and uniqueidentifier property of Column. Column with identity property contains unique system generated value in the table. Column with identity property is similar to AutoNumber field in MS Access.... Describe in brief authentication modes in SQL server. This is the default and recommended security mode. In this mode, access to SQL server is controlled by Windows NT.....

What are constraints in SQL Server?

What are constraints in SQL Server? Constraints enforce the data integrity to the database and protect columns of the table from unwanted values. NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY are the types of contraints define in SQL Server

What are cursors in SQL Server?

What are cursors in SQL Server? Cursors allow row-by-row prcessing of the resultsets. Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. Each time you fetch a row from the cursor, it results in a network roundtrip Cursors are also costly because they require more resources and temporary storage

What are partitioned views and distributed partitioned views?

What are partitioned views and distributed partitioned views? Partitioned views allow data in a large table to be split into smaller tables. These small tables are called as member tables. The split is done based on range of data values in one of the columns. In a distributed portioned view, each member table is on a separate member server. This means that the member tables are distributed. To locate these tables easily, the database name on each server should be same.

What are the Reporting Services components?

What are the Reporting Services components? Reporting services components assist in development. These processing components include some tools that are used to create, manage and view reports. A report designer is used to create the reports. a report sever is used to execute and distribute reports. a report manager is used to manage the report server.

What are the essential components of SQL Server Service broker?

What are the essential components of SQL Server Service broker? The components in SQL server Service broker are represented by Server objects used in the messaging. Queue is an object that holds the messages for processing. Dialogues exchanged between two endpoints are grouped by conversation groups. There are some components used at design time called as service definition components used to specify the basic design of the application. Routing and security components are used to define the communications.

What are the restrictions that views have to follow?

What are the restrictions that views have to follow? Answer Since a view is a virtual table - columns of the view cannot be renamed. To change anything in the view, the view must be dropped and create again. The select statement on the view cannot contain ORDER BY or INTO TEMP When a table or view is dropped, any views in the same database are also dropped. It is not possible to create an index on a view It is not possible to use DELETE to update a view that is defined as a join.

What are triggers in SQL Server?

What are triggers in SQL Server? Triggers are special kind of event driven stored procedures. Executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table, Can specify which trigger fires first or fires last using sp_settriggerorder, Triggers can't be invoked on demand, They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens, Triggers are generally used to implement business rules, auditing, Triggers can also be used to extend the referential integrity checks

What do we need Queues?

What do we need Queues? A queue holds the incoming message before they are processed for a service. One queue per service is created. A queue can be created using: CREATE QUEUE [QueueName]

What functions can a view be used to performed?

What functions can a view be used to performed? Functions of View:- Subset data of a table Can join multiple tables values into one They can act as aggregated tables. i.e. a view can be used to store Sum, average of values Views can be nested and can be used for abstraction

What is "Asynchronous" communication?

What is "Asynchronous" communication? In Asynchronous communication, messages can be queued until the service which was down is up and running. Asynchronous communication is loosely coupled. This means that there can be another component waiting to acknowledge the message. In this communication, the sender need not wait for a response but can continue to work.

What is Indexed view? How to create it?

What is Indexed view? How to create it? In an indexed view, the data is already computed and stored. Data can be accessed by a unique index. This index is a clustered index. In order to create an index the syntax is CREATE [UNIQUE], [CLUSTERED | NONCLUSTERED] INDEX index_name ON {view} [WITH <index_option>] [ON filegrp]

Why Use Asynchronous, Queued Messaging?

Why Use Asynchronous, Queued Messaging? [purpose] Queues enable the flexible scheduling of work, which can translate to big improvements in both performance and scalability. [implementation] To see how, go back to the order entry example. Some parts of an order must be processed before the order can be considered complete, such as the order header, available to promise, and order lines. But other parts realistically don't have to be processed before the order is committed; for example, billing, shipping, inventory, and so on. If the "delayable" piece of the order can be processed in a guaranteed but asynchronous manner, the core part of the order can be processed faster. [benefit] Asynchronous messaging can also provide opportunities for increased parallelism. For example, if you need to check the customer's credit and check availability for ordered items, starting both processes simultaneously can improve overall response time. Queuing can also enable systems to distribute processing more evenly, reducing the peak capacity required by a server.

Why Use Transactional Messaging?

Why Use Transactional Messaging? Service Broker supports transactional messaging, which means that messages are sent and received as transactions. If a transaction fails, the message sends and receives are rolled back, and don't take effect until the transaction has processed the messages successfully and committed. Transactional messaging makes programming a messaging application much more straightforward because you can freely scatter sends and receives wherever they make sense and nothing happens until the transaction commits. If the transaction gets rolled back, none of the sends take place, and the messages that were received go back in the queue so they will be received and processed again.

Comment on Transactions

[SUMMARY] - group - finished - failure - Using transactions we can group all SQL commands into a single unit. - The transaction begins with some task and finishes only when all tasks within it are over. - The transaction gets over successfully only when all commands in it are successfully over. Even if one command fails, the whole transaction fails. - The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions. - A group of tasks starts with the begin statement. - In case of any problem, the rollback command is executed to abort the transaction. - If all the tasks run successfully, all commands are executed through commit statement.

Explain query execution plan?

[summary] - a roadmap of methods used by the system to solve a given problem - The optimizer available in SQL Server optimizes the code to be effectively executed. - A query execution plan shows how this optimizer would run the query. - Query execution plan can be viewed by : - Using the Show Execution Plan option available in Query Analyzer, - Displaying Estimated Execution Plan on the query dropdown menu, - Use the SET SHOWPLAN_TEXT ON command before running a query and capturing the execution plan event in a SQL Server Profiler trace.

What are the benefits of User-Defined Functions?

a. Can be used in a number of places without restrictions as compared to stored procedures. b. Code can be made less complex and easier to write. c. Parameters can be passed to the function. d. They can be used to create joins and also be sued in a select, where or case statement. e. Simpler to invoke. What is user-defined function? Explain its types i.e. scalar and Inline table value user-defined function. User defined functions are created and defined by the user. They are created as per users needs. They may or may not accept parameters. They can be used to create joins and simple to invoke as compared to stored procedures Types: Scalar user defined: returns values as one of the scalar data types. Text, timestamp, image data types are not supported. It may or may not accept parameters. Inline table value user defined : it returns a table data type. These functions can pass parameters to the sql's SELECT command. They are similar to views except, they can accept parameters.

What do you mean by an execution plan? Why is it used? How would you view it?

a.) An execution plan can be called as a road map that graphically or textually shows the data retrieval methods which have been chosen by the SQL Server query optimizer, for a stored procedure or ad- hoc query. b.) It is used because it is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure. c.) There exists an option called "Show Execution Plan" in Query Analyzer. If this option is turned on, it will display query execution plan in separate window when the query is run again.

Explain the following.

a.) COLLATION. Collation is a type of sort order. There are mainly three types of sort orders, namely: i.) Dictionary case sensitive ii.)Dictionary - case insensitive iii.)Binary. b.) Stored Procedure - It is a set of T-SQL statements combined together to perform a single task formed by combining many small tasks. - When you actually run a Stored procedure, a set of statements is run.

Explain the following: - Dirty pages - ETL

a.) Dirty pages. These are the buffer pages that contain modifications which have not been written to disk. b.) ETL - Extraction, Transformation, and Loading. - It is the process of copying and cleaning data from heterogeneous sources. - It is an important part of development projects for data warehousing and business intelligence.

You want to implement the following relationships while designing tables. How would you do it? a.) One-to-one b.) One-to-many c.) Many-to-many

a.) One-to-One relationship - can be implemented as a single table and rarely as two tables with primary and foreign key relationships. b.) One-to-Many relationships - by splitting the data into two tables with primary key and foreign key relationships. c.) Many-to-Many - by using a junction table with the keys from both the tables forming the composite primary key of the junction table.

Explain the following actions: Truncate Delete

Truncate Truncate command is used to remove all rows of the column. The removed records are not recorded in the transaction log. It is the fast way to remove all the records from the table. The records once removed can't be rolled back. It can't activate trigger. It resets the identity of the column. Delete Delete command removes records one at a time and logs into the transaction log. It can be used with or without where clause. The records can be rolled back. It activates trigger. It doesn't reset the identity of the column. Sql server - Define Truncate and Delete commands - Feb 11, 2010 at 11:55 AM by Shuchi Gauri Define Truncate and Delete commands. TRUNCATE DELETE This is also a logged operation but in terms of deallocation of data pages. This is a logged operation for every row. Cannot TRUNCATE a table that has foreign key constraints. Any row not violating a constraint can be Deleted. Resets identity column to the default starting value. Does not reset the identity column. Starts where it left from last. Removes all rows from a table. Used delete all or selected rows from a table based on WHERE clause. Cannot be Rolled back. Need to Commit or Rollback DDL command DML command

Syntax for viewing, dropping and disabling triggers

View trigger: A trigger can be viewed by using sp_helptrigger syntax. This returns all the triggers defined in a table. Sp_helptrigger table_name Drop a trigger Syntax: DROP TRIGGER Trigger_name Disable a trigger:- Syntax: DISABLE TRIGGER [schema name] trigger name ON [object, database or ALL server ]

Ways to troubleshoot performance problems in SQL Server.

Ways to troubleshoot performance problems in SQL Server. SET SHOWPLAN_ALL ON SET SHOWPLAN_TEXT ON SET STATISTICS IO ON SQL Server Profiler Windows NT /2000 Performance monitor Graphical execution plan in Query Analyzer

What is database replicaion?

What is database replicaion? The process of copying/moving data between databases on the same or different servers. Snapshot replication, Transactional replication, Merge replication

What is the main purpose of having Conversation Group?

What is the main purpose of having Conversation Group? Any application using service broker communicates by exchanging conversations that is nothing but asynchronous and reliable messages. Conversation group is a group of conversations used to accomplish the same task. These groups help in concurrency. They can also be used to manage the states.

Default constraint

Define default constraint? Default constraint is used to fill column with default value defined during creation of table if nothing is supplied while inserting data. IDENTITY columns and timestamp columns can't be associated with default constraint

Define views.

Define views. Answer A view can be considered as a virtual table. It does not physically exist. It is based on the result set of a SQL statement. A view contains rows and tables just like a real table.

What is denormalization?

Denormalization is the reverse process of normalization. It is controlled introduction of redundancy to the database design. Database design is denormalised to improve the query performance. It is done to reduce number of complex joins in the query.

Describe the functionalities that views support.

Describe the functionalities that views support. Answer Views can subset data in a table They can join multiple tables into one virtual table Views can provide security and decrease complexity They save space because only their definition is stored. They can also be used to create abstraction Materialized views are commonly used in data warehousing. They represent a snapshot of the data from remote sources.

Explain service broker dialogs

Dialogs [purpose] Service Broker implements dialogs, which are bidirectional streams of messages between two endpoints. [implementation] All messages in a dialog are ordered, and dialog messages are always delivered in the order they are sent. The order is maintained across transactions, across input threads, across output threads, and across crashes and restarts. Some message systems ensure message order for the messages sent or received in a single transaction but not across multiple transactions, making Service Broker dialogs unique in this regard. [policy] Each message includes a conversation handle that uniquely identifies the dialog that is associated with it. For example, an order entry application might have dialogs open simultaneously with the shipping application, the inventory application, and the billing application. Because messages from each application have a unique conversation handle, it's easy to tell which application sent each message.

Difference between DELETE and TRUNCATE commands in SQL Server.

Difference between DELETE and TRUNCATE commands in SQL Server. DELETE TABLE is a logged operation, it is a slow process. TRUNCATE TABLE deletes all the rows, but it won't log the deletion, conquently it is fast process. TRUNCATE TABLE can't be rolled back

Difference between UNION ALL and UNION.

Difference between UNION ALL and UNION. UNION statement eliminates duplicate rows whereas UNION ALL statement includes duplicate rows. UNION statement can be used to combine any number of queries whereas UNION ALL statement can be used to combine a maximum of two queries. UNION statement cannot be used with aggregate functions whereas UNION ALL statement can be used with aggregate functions

Difference between a primary key and a unique key

Difference between a primary key and a unique key. Both enforce uniqueness of the column. By default primary key creates a clustered index. Unique creates a nonclustered index by default. Primary key doesn't allow NULLs. Unique key allows NULL

How Service Broker Solves the Hard Problems in Messaging

How Service Broker Solves the Hard Problems in Messaging Messaging applications have been around for a long time, and there are compelling reasons to build them. So why aren't there more of them? The answer is that messaging applications are hard to get right. SQL Server Service Broker, however, solves some of the most difficult messaging application issues: message ordering, coordination, multithreading, and receiver management.

Define Views.

Define Views. View can be created to retrieve data from one or more tables. Query used to create view can include other views of the database. We can also access remote data using distributed query in a view.

What are the advantages of using Stored Procedures?

Answer Stored procedures provide performance benefits through local storage, precompiling the code, and caching. Stored procedures offers security features that includes encryption and privilege limits that restrict users from modifying structure of stored procedure. SQL Server stored procedure - posted on Jan 09, 2009 at 4:53 pm Describe the purposes and advantages stored procedure? Stored procedures manage, control and validate data. Large queries can be avoided. Reduces network traffic since they need not be recompiled. Even though the stored procedure itself may be a complex piece of code, we need not write it over and over again. Hence stored procedures increases reusability of code Permissions can be granted for stored procedures. Hence, increases security. Sql server - What are the advantages of using Stored Procedures? - Feb 11, 2010 at 11:55 AM by Shuchi Gauri What are the advantages of using Stored Procedures? Advantages of using stored procedures are: They are easier to maintain and troubleshoot as they are modular. Stored procedures enable better tuning for performance. Using stored procedures is much easier from a GUI end than building/using complex queries. They can be part of a separate layer which allows separating the concerns. Hence Database layer can be handled by separate developers proficient in database queries. Help in reducing network usage. Provides more scalability to an application. Reusable and hence reduce code.

What are the events recorded in a transaction log?

Answer The start and end of each transaction Every data modification Every extent allocation or deallocation The creation or dropping of a table or index Sql server - What are the events recorded in a transaction log? - Feb 11, 2010 at 11:55 AM by Shuchi Gauri. What are the events recorded in a transaction log? Events recorded in a transaction log: Broker event category includes events produced by Service Broker. Cursors event category includes cursor operations events. CLR event category includes events fired by .Net CLR objects. Database event category includes events of data.log files shrinking or growing on their own. Errors and Warning event category includes SQL Server warnings and errors. Full text event category include events occurred when text searches are started, interrupted, or stopped. Locks event category includes events caused when a lock is acquired, released, or cancelled. Object event category includes events of database objects being created, updated or deleted. OLEDB event category includes events caused by OLEDB calls. Performance event category includes events caused by DML operators. Progress report event category includes Online index operation events. Scans event category includes events notifying table/index scanning. Security audit event category includes audit server activities. Server event category includes server events. Sessions event category includes connecting and disconnecting events of clients to SQL Server. Stored procedures event category includes events of execution of Stored procedures. Transactions event category includes events related to transactions. TSQL event category includes events generated while executing TSQL statements. User configurable event category includes user defined events.

What are the restrictions applicable while creating views?

Answer Views can be created referencing tables and views only in the current database. A view name must not be the same as any table owned by that user. You can build views on other views and on procedures that reference views. Rules or DEFAULT definitions can't be associated with views. Only INSTEAD OF triggers can be associated with views. The query that defines the view cannot include the ORDER BY, COMPUTE, or COMPUTE BY clauses or the INTO keyword. You cannot define full-text index definitions for views. You cannot create temporary views You cannot create views on temporary tables. Views - Dec 1, 2008 at 18:10 pm by Rajmeet Ghai What are the restrictions that views have to follow? Answer Since a view is a virtual table - columns of the view cannot be renamed. To change anything in the view, the view must be dropped and create again. The select statement on the view cannot contain ORDER BY or INTO TEMP When a table or view is dropped, any views in the same database are also dropped. It is not possible to create an index on a view It is not possible to use DELETE to update a view that is defined as a join. Sql server - What are the restrictions applicable while creating views? - Feb 11, 2010 at 11:55 AM by Shuchi Gauri What are the restrictions applicable while creating views? Restrictions applicable while creating views: A view cannot be indexed. A view cannot be Altered or renamed. Its columns cannot be renamed. To alter a view, it must be dropped and re-created. ANSI_NULLS and QUOTED_IDENTIFIER options should be turned on to create a view. All tables referenced in a view must be part of the same database. Any user defined functions referenced in a view must be created with SCHEMABINDING option. Cannot use ROWSET, UNION, TOP, ORDER BY, DISTINCT, COUNT(*), COMPUTE, COMPUTE BY in views.

What are the commands available for Summarizing Data in SQL Server?

Answer We have CUBE or ROLLUP operators to generate summary reports. Both are part of the GROUP BY clause of the SELECT statement. We have COMPUTE or COMPUTE BY operators that are used with the GROUP BY clause. CUBE Operator The CUBE operator generates a result set in the form of multi-dimensional cube. ROLLUP Operator The ROLLUP operator generating reports that contain subtotals and totals. Sql server - What are the commands available for Summarizing Data in SQL Server? - Feb 11, 2010 at 11:55 AM by Shuchi Gauri What are the commands available for Summarizing Data in SQL Server? Commands for summarizing data in SQL Server: Command Description Syntax/Example SUM Sums related values SELECT SUM(Sal) as Tot from Table1; AVG Average value SELECT AVG(Sal) as Avg_Sal from Table1; COUNT Returns number of rows of resultset SELECT COUNT(*) from Table1; MAX Returns max value from a resultset SELECT MAX(Sal) from Table1; MIN Returns min value from a resultset SELECT MIN(Sal) from Table1; GROUP BY Arrange resultset in groups SELECT ZIP,City FROM Emp GROUP BY ZIP ORDER BY Sort resultset SELECT ZIP,City FROM Emp ORDER BY City

What are the ways to code efficient transactions?

Answer We shouldn't allow input from users during a transaction. We shouldn't open transactions while browsing through data. We should keep the transaction as short as possible. We should try to use lower transaction isolation levels. We should access the least amount of data possible while in a transaction. Sql server - What are the ways to code efficient transactions? - Feb 11, 2010 at 11:55 AM by Shuchi Gauri What are the ways to code efficient transactions? Some ways and guidelines to code efficient transactions: Do not ask for an input from a user during a transaction. Get all input needed for a transaction before starting the transaction. Transaction should be atomic Transactions should be as short and small as possible. Rollback a transaction if a user intervenes and re-starts the transaction. Transaction should involve a small amount of data as it needs to lock the number of rows involved. Avoid transactions while browsing through data.

Describe in brief exporting and importing utility

BCP Utility The Bulk Copy is a command utility that transfer SQL data to or from a data file. This utility mostly used to transfer huge data to SQL server from other database. With this, data is first exported from source file to a data file and then imported from the data file to SQL server table. It is also used to transfer data from SQL server table to a data file. You can use 'Bulk Insert' statement to transfer data from data file to SQL server table. DTS Packages It is a set of tools that allows you extract, transform, and consolidate data from disparate sources into single or multiple destinations. You can create custom data movement solution using DTS object model. DTS packages can provide following services: It can import or export data to or from text file or OLE DB data source. It supports data transformation using DTS transformation which means that data can be operated using one or more functions before hitting the destination. You can transfer database objects along with data using DTS package. DTS package also notifies if package steps succeed or failed by sending mails to source and destination. SQL Server DTS - Nov 20, 2008 at 18:00 PM by Rajmeet Ghai Describe how DTS is used to extract, transform and consolidate data. Answer Data Transformation Services is a set of tools available in SQL server that helps to extract, transform and consolidate data. This data can be from different sources into a single or multiple destinations depending on DTS connectivity. To perform such operations DTS offers a set of tools. Depending on the business needs, a DTS package is created. This package contains a list of tasks that define the work to be performed on, transformations to be done on the data objects. Import or Export data: DTS can import data from a text file or an OLE DB data source into a SQL server or vice versa. Transform data: DTS designer interface also allows to select data from a data source connection, map the columns of data to a set of transformations, and send the transformed data to a destination connection. For parameterized queries and mapping purposes, Data driven query task can be used from the DTS designer. Consolidate data: the DTS designer can also be used to transfer indexes, views, logins, triggers and user defined data. Scripts can also be generated for the sane For performing these tasks, a valid connection(s) to its source and destination data and to any additional data sources, such as lookup tables must be established. Sql server - Describe in brief exporting and importing utility - Feb 11, 2010 at 11:55 AM by Shuchi Gauri Describe in brief exporting and importing utility SQL Server has an export/import wizard that allows creation of a Integration services package enabling export import of data from one location to another. To start the wizard: Start menu-> SQL Server Management Studio-> Business Intelligence Development Studio. This wizard can import/export data from any data source that has a managed .Net Framework data provide or its own native OLEDB provider. E.g.: SQL Server, Flat files, MS Access, MS Excel etc.

What is bit datatype?

Bit datatype is used to store boolean information like 1 or 0 (true or false). Bit datatype can represent a Null state

Having clause and Where clause

By Nishant Kumar Both are used for filtering of records based on filtered criteria. Having is used in a 'group by' clause in a query. 'Where clause' has preference over 'having clause' in a query. Sql server - Having clause and Where clause - Feb 11, 2010 at 11:55 AM by Shuchi Gauri Having clause and Where clause Having Clause: This clause is used for specifying a search condition for a group or an aggregate. It can only be used with a SELECT statement. It's often used with GROUP BY clause without which its synonymous to a WHERE clause. E.g.: SELECT Id, Name, Age FROM Customers GROUP BY Age HAVING Age>10 ORDER BYAge; Where Clause: This clause is used to narrow down the dataset being dealt with following a condition. SELECT Id, Name, Age FROM Customers WHERE Age>10 It is strongly recommended to use a Where clause with every Select Statement to avoid a table scan and reduce the number of rows to be returned. It can be used with Select, Update, Delete etc statements. SQL BETWEEN SQL BETWEEN: The BETWEEN operator is used in a WHERE clause to select a range of data between two values. The values can be numbers, text, or dates.............. SQL CONCATENATE SQL CONCATENATE: This clause combine together (concatenate) the results from several different fields............... SQL Server accessing data Explain the full syntax of Select Statement with examples. Explain some of the keywords of Select Statement like Distinct Keyword, Top n Keyword with an example for each of them. Describe the use of Into and From clause with examples..............

Explain how to apply cascading referential integrity in place of triggers.

Cascading referential integrity constraints are automatic updates and deletes on dependant objects. They define a set of actions that SQL server may need to take. The References clause of the CREATE TABLE and ALTER TABLE statements support ON DELETE and ON UPDATE clauses: [ON DELETE {NO ACTION} ]: If an attempt to delete a row is made, with a key referenced by foreign keys in existing rows in other tables, an error is raised and DELETE is rolled back. [ON UPDATE {NO ACTION } ]: If an attempt to update a row is made, with a key referenced by foreign keys in existing rows in other tables, an error is raised and UPDATE is rolled back. [ ON DELETE { CASCADE } ]: If an attempt to delete a row is made, with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted [ ON UPDATE { CASCADE } ]: If an attempt to update a row is made, with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also Updated. NO ACTION is default.

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

Controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server. Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database. Topic link icon Transact-SQL Syntax Conventions Syntax SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE } [ ; ]

Explain Conversation Groups

Conversation Groups [purpose] Service Broker provides a way of grouping all the dialogs that are used for a particular task. This method uses conversation groups. In our previous order entry example, all the dialogs associated with processing a particular order would be grouped into a single conversation group. [implementation] The conversation group is implemented as a conversation group identifier, which is included with all messages in all dialogs contained in the conversation group. When a message is received from any of the dialogs in a conversation group, the conversation group is locked with a lock that is held by the receiving transaction. For the duration of the transaction, only the thread that holds the lock can receive messages from any of the dialogs in the conversation group. [benefit] This makes our order entry application much easier to write because even though we use many threads for scalability, any particular order is only processed on one thread at a time. This means we don't have to make our application resilient to problems that are caused by the simultaneous processing of a single order on multiple threads. [benefit] One of the most common uses for the conversation group identifier is to label the state that is associated with a particular process. If a process involves many messages over time, it probably doesn't make sense to keep an instance of the application running through the whole process. For example, the order entry application will scale better if, between messages, any global state that is associated with processing an order is stored in the database and retrieved when the next message associated with that order is received. The conversation group identifier can be used as the primary key in the state tables to enable quick retrieval of the state associated with each message.

Disadvantages of cursor.

Cursor manipulates records row by row. It requires temporary storage for row manipulation. Thus manipulating row using cursor is costly affair since it consumes extra resource. Fetch record row by row means server roundtrip that consumes network recourses

Explain the four sets of cursor options

Cursor options There are four sets of cursor options: STATIC The STATIC cursor copies the data in the results set into tempdb and DML that occurs in the underlying results fails to reflect in the cursor's data. Subsequent fetch statements are made on the results set in tempdb. This is perfect when the data underlying your cursor is static or your cursor has no real-time requirements. For example, most cursors Microsoft uses are declared as static as the operation being carried out on the data needs to be done on point-in-time requirements. In other words, it does not need to know about new rows -- the data is static. KEYSET The KEYSET cursor is implemented by copying primary key data into tempdb. As the cursor moves through the result set, the cursor will see modifications to the underlying data but it will not see new rows. If data is fetched from a row that no longer exists, nulls will be returned and the @@FETCH_STATUS variable will have a value of -2. The order of the cursor data is also maintained. The KEYSET cursor is the default cursor type. Fetch statements are made on the underlying base tables based on the keyset data cached in tempdb. These cursors take more time to open than DYNAMIC cursors, but they also have fewer resource requirements. DYNAMIC The DYNAMIC cursor is similar to the KEYSET cursor in that the cursor can see data modifications in the underlying base tables, but it can also see newly inserted and deleted rows. It does not preserve order, which can lead to the Halloween problem as illustrated in script 4. Fetch statements are made on the underlying base tables, based on the key data cached in tempdb, but the key data is refreshed with each modification to key data on the base tables. It is the most expensive cursor type to implement. FAST_FORWARD A FAST_FORWARD cursor provides optimal performance but only supports the NEXT argument, which only fetches the next row. Other cursor types will be able to fetch the next row, the prior row (using the PRIOR command), the first row (using the FIRST argument), the last row using the LAST argument, the nth row (using the ABSOLUTE arguments), or leap ahead n rows from the current cursor location (using the RELATIVE argument). The above cursor options control the following: Scope or visibility Is the cursor only visible within a batch (local) or beyond the batch (global)? Cursors are only visible within a connection. Scrollability Can the fetch statement fetch only the next row, fetch in any direction and/or by a specific number of rows? The advantage of a forward-only cursor is that it performs faster than a cursor type that can move in any direction. The two options are FORWARD_ONLY and SCROLL (any number and in any direction). Membership Which rows are members of your cursor? Can the cursor see changes happening in the underlying results set, and can it see newly inserted/deleted rows? Updatability Can you update or delete the rows in the underlying results set? To update the tables underlying your cursor, you must have the following: 1. A primary key on the base tables underlying your cursor to update them. Otherwise you will get the message: Server: Msg 16929, Level 16, State 1, Line 1 The cursor is READ ONLY. The statement has been terminated. 2. A cursor defined as KEYSET, DYNAMIC or FAST_FORWARD. 3. The WHERE CURRENT syntax to update or delete a row. Please refer to this script for an illustration of cursor updatability functions. You retrieve rows from the cursor using fetch statements. You should always check the value of the @@Fetch_Status variable to ensure that it has a value of 0. The @@Fetch_Status variable can have three values: 0 - row successfully returned -1- fetch statement has read beyond the number of rows in the cursor -2 - row no longer exists in your results set The fetch statements are analogous to our window washers moving down the sides of the sky scraper. With the fetch statement, the logical operations are position and then retrieve; twice as many operations as a set statement (i.e., with a set statement it's INSERT, UPDATE or DELETE). Finally, clean up after your cursor using the close MyCursorName statement and then deallocate its resources using the deallocation MyCursorName statement. Note that a quick way to return to the beginning of a FAST_FORWARD cursor is to close and reopen it.

What is transact-SQL? Describe its types?

Data Definition Language (DDL) Data Control Language (DCL) Data Manipulation Language (DML) By Nishant Kumar Types of Transact-SQL SQL Server Provides three types of Transact-SQL statements namely DDL, DCL, and DML. Data Definition Language (DDL) It allows creating, altering and dropping database objects. Data Control Language (DCL) It is used to control access to data in the database. It controls permission on the database objects using grant, revoke or deny statement. Data Manipulation Language (DML) It involves retrieval, insertion, deletion, modification in the database. It includes select, insert, update, and delete command. Sql server - What is transact-SQL? Describe its types? - Feb 11, 2010 at 11:55 AM by Shuchi Gauri What is transact-SQL? Describe its types? T-SQL is used by any application trying to access/use SQL Server. All applications thus interact with an SQL Server instance using T-SQL statements. Types of T-SQL: Data types User defined temporary objects Specialized functions global variables System and extended procedures Cursors

Define data, entity, domain and referential integrity.

Data Integrity Data Integrity validates the data before getting stored in the columns of the table. SQL Server supports four type of data integrity: Entity Integrity Entity Integrity can be enforced through indexes, UNIQUE constraints and PRIMARY KEY constraints. Domain Integrity Domain integrity validates data for a column of the table. It can be enforced using: Foreign key constraints, Check constraints, Default definitions NOT NULL. Referential Integrity FOREIGN KEY and CHECK constraints are used to enforce Referential Integrity. User-Defined Integrity It enables you to create business logic which is not possible to develop using system constraints. You can use stored procedure, trigger and functions to create user-defined integrity. Sql server - Define data, entity, domain and referential integrity. - Feb 11, 2010 at 11:55 AM by Shuchi Gauri Define data, entity, domain and referential integrity. Data: Any raw information that needs to be stored in the database for persistence is termed as data. Entity: Entity refers to a real world object that might depict a table in an SQL Server database. Databases are often constructed in the basis of an Entity-relationship diagram. Domain: A domain represents typically an organization or an organization might have multiple domains representing different groups under the organization. SQL Server administrators usually run SQL Server service using a domain administrator account. Usually it represents the Windows Active directory domain which decides users of what domain would be able to access the SQL Server instance. Referential integrity: An RDBS needs to maintain and have logical relationships between various tables for it to work efficiently. When such relationships exist and are maintained as they should be, it represents referential integrity of the data.

Candidate key, alternate key, composite key

Define candidate key, alternate key, composite key. Candidate key: A column or a set of columns can be called as candidate key if they identify each row of a table uniquely. A table can have multiple candidate keys. One of them is specified as Primary key and rest of them can be called as alternate key. Alternate key: There can be more than one keys which can identify each row of the table uniquely. One of them is defined as primary key and rest of them is called alternate keys of the table. Composite Key:A key formed by combining at least two or more columns.

Describe how DTS is used to extract, transform and consolidate data.

Data Transformation Services is a set of tools available in SQL server that helps to extract, transform and consolidate data. This data can be from different sources into a single or multiple destinations depending on DTS connectivity. To perform such operations DTS offers a set of tools. Depending on the business needs, a DTS package is created. This package contains a list of tasks that define the work to be performed on, transformations to be done on the data objects. Import or Export data: DTS can import data from a text file or an OLE DB data source into a SQL server or vice versa. Transform data: DTS designer interface also allows to select data from a data source connection, map the columns of data to a set of transformations, and send the transformed data to a destination connection. For parameterized queries and mapping purposes, Data driven query task can be used from the DTS designer. Consolidate data : the DTS designer can also be used to transfer indexes, views, logins, triggers and user defined data. Scripts can also be generated for the sane For performing these tasks, a valid connection(s) to its source and destination data and to any additional data sources, such as lookup tables must be established.

Describe in brief Databases and SQL Server Databases Architecture.

Databases A database is a structured collection of data. Database can be thought as simple data file. It is more powerful than data file which stores data in organized way. Database organizes the data into a table. It is made up of several tables with rows and columns. SQL Database Architecture SQL database is divided into logical and physical components. Logical Component Logical components comprises of following: Database objects, Collation, Login, Users, Roles and groups. Physical component Physically components exist as two or more files on disk. Physically files can be of three types. Primary data files This is starting point of the database and contain pointer to point other files. Extension is mainly .mdf . Secondary data files It consists of data other than primary data file. Extension is mainly .ndf . Log files It contains log information which is used to recover database. SQL Server database architecture - Nov 18, 2008 at 15:30 pm by Rajmeet Ghai What are database files and filegroups? Answer Database files are used for mapping the database over some operating system files. Data and log information are separate. SQL server database has three types of database files: Primary: starting point of a database. It also points to other files in database. Extension: .mdf Secondary: All data files except primary data file is a part of secondary files. Extension: .ndf Log files: All log information used to recover database. Extension: .ldf Sql server - Describe in brief Databases and SQL Server Databases Architecture - Feb 11, 2010 at 11:55 AM by Shuchi Gauri Describe in brief Databases and SQL Server Databases Architecture. SQL Server consists of a set of various components which fulfill data storage and data analysis needs for enterprise applications. Database architecture: All the data is stored in databases which is organized into logical components visible to the end users. It's only the administrator who needs to actually deal with the physical storage aspect of the databases, whereas users only deal with database tables. Every SQL Server instance has primarily 4 system database i.e. master, model, tempdb and msdb. All other databases are user created databases as per their needs and requirements. A single SQL Server instance is capable of handling thousands of users working on multiple databases.

Describe in brief Databases and SQL Server Databases Architecture.

Databases A database is a structured collection of data. Database can be thought as simple data file. It is more powerful than data file which stores data in organized way. Database organizes the data into a table. It is made up of several tables with rows and columns. SQL Database Architecture SQL database is divided into logical and physical components. Logical Component Logical components comprises of following: Database objects, Collation, Login, Users, Roles and groups. Physical component Physically components exist as two or more files on disk. Physically files can be of three types. Primary data files This is starting point of the database and contain pointer to point other files. Extension is mainly .mdf . Secondary data files It consists of data other than primary data file. Extension is mainly .ndf . Log files It contains log information which is used to recover database. SQL Server database architecture - Nov 18, 2008 at 15:30 pm by Rajmeet Ghai What are database files and filegroups? Answer Database files are used for mapping the database over some operating system files. Data and log information are separate. SQL server database has three types of database files: Primary: starting point of a database. It also points to other files in database. Extension: .mdf Secondary: All data files except primary data file is a part of secondary files. Extension: .ndf Log files: All log information used to recover database. Extension: .ldf Sql server - Describe in brief Databases and SQL Server Databases Architecture - Feb 11, 2010 at 11:55 AM by Shuchi Gauri Describe in brief Databases and SQL Server Databases Architecture. SQL Server consists of a set of various components which fulfill data storage and data analysis needs for enterprise applications. Database architecture: All the data is stored in databases which is organized into logical components visible to the end users. It's only the administrator who needs to actually deal with the physical storage aspect of the databases, whereas users only deal with database tables. Every SQL Server instance has primarily 4 system database i.e. master, model, tempdb and msdb. All other databases are user created databases as per their needs and requirements. A single SQL Server instance is capable of handling thousands of users working on multiple databases. NEXT>> The answers to following questions will be made available soon. Keep visiting. Describe in brief database architecture. Explain logical database components. What are the database objects? Explain them in brief. Illustrate physical database architecture in brief. What are pages and Extents?

Define Bulk Copying and Distributed Queries of SQL Server.

Define Bulk Copying and Distributed Queries of SQL Server. Bulk copying is used to transfer large amount of data. Distributed Queries in SQL server are used to reference heterogeneous data source such as a Microsoft Access database or Oracle database

Define DTS service in SQL Server.

Define DTS service in SQL Server. DTS, Data Transformation Services is used to import and export data between heterogeneous data source. A DTS package can not only transfer data from a table to another, but can also specify query as source of data

Define SQL Server Agent.

Define SQL Server Agent. SQL server agent is important service of the server where instances of SQL server are running. The agent is responsible to run tasks that are scheduled to occur at specific times or intervals. The agent is also responsible to run replication task set by the administrators

Describe in brief SQL Server monitoring way

Execution Plan SQL Server caches the plan of execution of query or stored procedure which it uses in subsequent call. This is a very important feature with regards to performance enhancement. You can view execution plan of data retrieval graphically or textually. SQL Profiler SQL Profiler is a tool that stores events from the server. SQL Profiler saves the events in the trace file. The trace file can be analyzed later to monitor the performance of the queries. Trace file is useful in diagnosing slow running queries. You can set SQL Profiler to trace only those events in which you are interested. Monitoring too many events will end up in large trace file. SQL Server What is an execution plan? - May 19, 2009 at 18:00 AM by Rajmeet Ghai What is an execution plan? An Execution plan is most useful when a SQL query runs slowly. An execution plan gives details of the SQL Server query optimizer. It consists of set of steps that describes the information of the relational database. Since one query can be executed in several ways, execution plan provides a mechanism to manually analyze the performance. What is SQL Profiler? SQL Profiler is used to monitor events of SQL server. The profiler has a user friendly GUI that allows selected events to be traced and monitored. It is most useful when queries are performing slowly, debugging stored procedures, reviewing of all activities that took place on the server. Sql server - Describe in brief SQL Server monitoring ways. - Feb 11, 2010 at 11:55 AM by Shuchi Gauri Describe in brief SQL Server monitoring ways. Ways to monitor SQL Server: Applications manager provides a web based interface to visualize, manage and monitor SQL Server data farms. It provides granular information about performance enabling administrators to understand the usage and hence also plan the capacity of the database and the server. SQL Server monitoring can connect to the database source and monitor various system table column values and be aware of any alarms. Some of the attributes to monitor are: Memory usage, SQL Statistics, Buffer manager statistics, Latch details, Connection statistics, Access method details, Cache details, Database details, Lock details and Scheduled jobs. One can also use Database query monitoring to monitor bottleneck queries. Database administrators may also use Windows Performance monitor. This allows them to monitor the overall performance of the servers. How to integrate Performance Monitor and SQL Profiler. In order to Integrate Performance Monitor and SQL Profiler, server performance needs to be captured simultaneously using both Performance Monitor's Counter Logs and SQL Server Profiler.......... SQL Profiler What are the tasks performed by SQL Profiler? How can you use the SQL Profiler to ensure database security? How can you reduce the amount of data collected by a trace? What is SQL Profiler? When do you use SQL Profiler?............ What is the purpose of SQL Profiler in SQL server? SQL Profiler captures SQL Server events from a server. The events are saved in a trace file that can be used to analyze and diagnose problem............. Explain the difference between RECOMPILE query hint and WITH RECOMPILE option. When a stored procedure is created using WITH RECOMPILE option, the execution plan for the procedure is not reused. It is created each time during execution............ Describe in brief SQL Server monitoring ways. SQL Server caches the plan of execution of query or stored procedure which it uses in subsequent call. This is a very important feature with regards to performance enhancement. You can view execution plan of data retrieval graphically or textually................

Explain Indexed views and partitioned view with their syntax.

Explain Indexed views and partitioned view with their syntax. Answer Indexed view: An index view has a unique clustered index created on it. They exist as rows on the disk. Because they are saved on the disk, the response time to a query is fast at the cost of space consumption. They are more commonly used in scenarios when data modification is less. Syntax: Create Index CREATE [UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON table_name The view is created using the CREATE VIEW synatx Partitioned view: Partitioned view joins the horizontally portioned data. This data may belong to one ore more servers. It makes the data appear as one table. A portioned view can either be local or distributed. A local portioned view resides on the same instance of the SQL server while the distributed may reside on a different server. Syntax: The view is then created by UNIONing all the tables and an updateable partitioned View results Server 1 : CREATE TABLE Customer1 (CustomerID INTEGER PRIMARY KEY CHECK (CustomerID BETWEEN 1 AND 32999), ... -- Additional column definitions) Similar tables created for Server 2 and 3 Partitioned view for server 1 CREATE VIEW Customers AS SELECT * FROM CompanyDatabase.TableOwner.Customer1 UNION ALL SELECT * FROM Server2.CompanyDatabase.TableOwner.Customer2 UNION ALL SELECT * FROM Server3.CompanyDatabase.TableOwner.Customer3

Explain SQL Server Service Manager.

Explain SQL Server Service Manager. SQL Server Service Manager is used to start, stop, and pause SQL Server services that exist as the separate components on the server. The service components are as follows: SQL Server service SQL Server Agent service Microsoft Search service MSDTC service MSSQLServerOLAPService service

Explain the components of SQL server service broker.

Explain the components of SQL server service broker. Below are the Service Broker infrastructure components: Endpoint Message Type Contract Route Queue Service Remote Binding Service

Explian different types of BACKUPs avaialabe in SQL Server?

Explian different types of BACKUPs avaialabe in SQL Server? Full database backup. Differential database backup. Transaction log backup. Filegroup backup

Differentiate between a HAVING CLAUSE and a WHERE CLAUSE.

HAVING CLAUSE - HAVING CLAUSE is used only with the SELECT statement. - It is generally used in a GROUP BY clause in a query. - If GROUP BY is not used, HAVING works like a WHERE clause. WHERE Clause - It is applied to each row before they become a part of the GROUP BY function in a query.

What is index? Define its types.

Index Index can be thought as index of the book that is used for fast retrieval of information. Index uses one or more column index keys and pointers to the record to locate record. Index is used to speed up query performance. Both exist as B-tree structure. Kind of the indexes are clustered and non-clustered. Clustered index Clustered index exists as sorted row on disk. Clustered index re-orders the table record. Clustered index contains record in the leaf level of the B-tree. There can be only one Clustered index possible in a table. Non-clustered Non-clustered index is the index in which logical order doesn't match with physical order of stored data on disk. Non-clustered index contains index key to the table records in the leaf level. There can be one or more Non-clustered indexes in a table. Sql server - What is index? Define its types - Feb 11, 2010 at 11:55 AM by Shuchi Gauri What is index? Define its types. Index is created on a column in tables. It helps in providing fast access to data based on the values stored in the column. E.g.: If an index is created on primary key of a table and then search for a row based on primary key value then SQL Server first finds that value in the index and then uses the index to locate the row in the table instead of performing a complete table scan. Types of indexes: Clustered: Stores the actual row at the leaf level of the index. Nonclustered: Stores only the values from the indexed column and row locators that point to the actual row at leaf level instead of the actual row itself. Composite: Index containing more than one column (max 16). Unique: Ensures the uniqueness of each value in the indexed column. SQL Server Clustered and non clustered Index Clustered index exists as sorted row on disk, Clustered index re-orders the table record.......... SQL Server index tuning & database partitioning What is "Index Tuning Wizard"? List out what Index Tuning Wizard can do. Explain how partitioning an important part of database optimization is.......... Indexing XML data in SQL Server 2005 Explain the concepts of indexing XML data in SQL Server 2005. Provide basic syntax for creating index on XML data type column. What is content indexing/full text indexing? Explain the reason to index XML data type column. What are the guidelines to be adhered when creating a XML index?.......... What is transact-SQL? Describe its types? SQL Server Provides three types of Transact-SQL statements namely DDL, DCL, and DML.... Describe in brief SQL Server monitoring ways. SQL Profiler is a tool that stores events from the server. SQL Profiler saves the events in the trace file......

Explain the different types of indexes

Index Index can be thought as index of the book that is used for fast retrieval of information. Index uses one or more column index keys and pointers to the record, to locate record. Index is used to speed up query performance. Kind of the indexes are clustered and non-clustered. Both exist as B-tree structure. Clustered index exists as sorted row on disk. Clustered index re-orders the table record. Clustered index contains record in the leaf level of the B-tree. There can be only one Clustered index possible in a table. Non-clustered is the index in which logical order doesn't match with physical order of stored data on disk. Non-clustered index contains index key to the table records in the leaf level. There can be one or more Non-clustered indexes in a table. Unique index is the index that is applied to any column of unique value. A unique index can also be applied to a group of columns.

Determine how to use the inserted and deleted pseudo tables.

Inserted and deleted pseudo tables contain the New and Old values of the data that initiating the Trigger. These tables can be used for database maintenance and dynamic modification to data. These tables can be examined by the trigger itself. The tables themselves cannot be altered.

Explain the storage models of OLAP.

MOLAP Multidimensional Online Analytical processing In MOLAP data is stored in form of multidimensional cubes and not in relational databases. Advantage Excellent query performance as the cubes have all calculations pre-generated during creation of the cube. Disadvantages It can handle only a limited amount of data. Since all calculations have been pre-generated, the cube cannot be created from a large amount of data. It requires huge investment as cube technology is proprietary and the knowledge base may not exist in the organization. ROLAP Relational Online Analytical processing The data is stored in relational databases. Advantages It can handle a large amount of data and It provides all the functionalities of the relational database. Disadvantages It is slow. The limitations of the SQL apply to the ROLAP too. HOLAP Hybrid Online Analytical processing HOLAP is a combination of the above two models. It combines the advantages in the following manner: For summarized information it makes use of the cube. For drill down operations, it uses ROLAP. Data mining - storage models of OLAP - Jan 07, 2009 at 20:50 PM by Vidya Sagar Explain the storage models of OLAP. There are three storage models in OLAP: --Relational Online Analytical processing model --Multidimensional Online Analytical Processing model --Hybrid Online Analytical Processing model. Relational Online Analytical processing model: The data is stored in relational databases and provides an appearance of traditional OLAP's slicing and dicing functionality. The advantage of using ROLAP is large amount of data can be handled and leverage the relational database functionalities. Multidimensional Online Analytical processing model: A traditional mode in OLAP analysis. Data in this model is stored in the form of multidimensional cubes. The advantage is that it provides an excellent query performance and building cubes for fast data retrieval. The calculations are pre-generated at the time of creating cube and easily applied while querying data. Hybrid Online Analytical processing model. HOLAP combines the strengths of both ROLAP and MOLAP. HOLAP leverages the cube technology and ROLAP model is used for drilling down into details.

Describe in brief system database.

Master Database Master database is system database. It contains information about server's configuration. It is a very important database and important to backup Master database. Without Master database, server can't be started. MSDB Database It stores information related to database backups, DTS packages, Replication, SQL Agent information, SQL Server jobs. TEMPDB Database It stores temporary objects like temporary tables and temporary stored procedure. Model Database It is a template database used in the creation of new database. SQL Server - master, msdb, model, tempdb and resource databases - May 19, 2009 at 10:00 PM by Rajmeet Ghai What are the basic functions for master, msdb, model, tempdb and resource databases? Master database is used to store information of all the databases on the SQL server. The server cannot start if the database is not configured properly. The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping. The tempdb is used to store temporary objects such as global and local temporary tables and stored procedures. The model is essentially a template database used in the creation of any new user database created in the instance. The resoure Database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata. Sql server - Describe in brief system database. - Feb 11, 2010 at 11:55 AM by Shuchi Gauri Describe in brief system database. The system database contains information/metadata for all database present on an SQL Server instance. The system database stores information regarding logins, configuration settings, connected servers etc. It also holds various extended stored procedures to access external processes and applications. Major system databases : Master: Core system database to mange Sql Server instance. Resource: Responsible for physically storing all system objects. TempDB: This is a temporary database used to store temporary, tables, cursors, indexes, variables etc. Model: This acts as a template database for all user created databases. MSDB: Database to manage SQL Server agent configurations. Distribution: Database primarily used for SQL Server replication. ReportServer: Main database for reporting services to store metadata and other object definitions. ReportServerTempDB: Acts as a temporary storage for reporting services.

Message Ordering

Message Ordering In traditional reliable messaging applications, it's easy to get messages delivered out of order. For example, application A sends messages 1, 2, and 3. Application B receives and acknowledges 1 and 3, but experiences an error with 2, so application A resends it. However, now 2 is received after 3. Traditionally, programmers dealt with this problem by writing the application so that order didn't matter, or by temporarily caching 3 until 2 arrived so the messages could be processed in order. In contrast, Service Broker handles this transparently, so all messages in a dialog are received in the order sent, with no gaps in the message sequence. A related problem is duplicate delivery. In the previous example, if application B received message 2, but the acknowledgement message back to application A was lost, application A would resend 2 and application B would now receive 2 twice. Again, Service Broker ensures that messages are never delivered twice, even if the power is lost in the middle of a transaction.

Multithreading

Multithreading One of the most difficult issues in a messaging application is making a multithreaded reader work correctly. When multiple threads are receiving from the same queue, it's always possible for messages to get processed out of order, even if they are received in order. For example, if a message containing an order header is received by thread A and a message containing an order line is later received by thread B, it's possible that the order line transaction will attempt to commit first and fail a referential constraint because the order doesn't exist yet. Although the order line message will roll back until the order header exists, it is still a waste of resources.

Define Normalization and De- Normalization.

Normalization It is the process of organizing data into related table. To normalize database, we divide database into tables and establish relationships between the tables. It reduces redundancy. It is done to improve performance of query. Steps of normalization: First Normal form Entities of the table must have unique identifier or entity key. Second Normal Form All the attributes of the table must depend on the entity key for that entity. Third Normal Form All attributes that are not part of the key must not depend on any other non-key attributes. De-normalization The process of adding redundant data to get rid of complex join, in order to optimize database performance. This is done to speed up database access by moving from higher to lower form of normalization. Sql server - Define Normalization and De- Normalization - Feb 11, 2010 at 11:55 AM by Shuchi Gauri Define Normalization and De- Normalization. Normalization is the process of reducing data redundancy and maintains data integrity. This is performed by creating relationships among tables through primary and foreign keys. Normalization procedure includes 1NF, 2NF, 3NF, BCNF, and then the data is normalized. Denomalization on the contrary is the process of adding redundant data to speed up complex queries involving multiple table JOINS. One might just go to a lower form of Normalization to achieve Denormalization and better performance. Data is included in one table from another in order to eliminate the second table which reduces the number of JOINS in a query and thus achieves performance.

Define Primary and Unique key.What is index? Define its types.

Primary Key The column or columns of the table whose value uniquely identifies each row in the table is called primary key. You can define column as primary key using primary key constraint while you create table. When you define a column as primary key, a unique index is created which restricts duplicate data and fast access to data. A column defined as primary key doesn't allow null value. By default, clustered index in created with the column having primary key. Unique key Unique key also ensures data uniqueness like primary key. A column with unique key defined allows null value. By default, it creates non-clustered index. SQL Server Primary key and Unique key - Feb 3, 2009 at 8:10 am by Nishant Kumar Explain the difference between a primary key and a unique key. Both are defined to ensure unique row. Primary key creates a clustered index on the column by default. Unique creates a non-clustered index by default. Primary key doesn't allow NULLs, but unique key allows one NULL only. Sql server - Define Primary and Unique key - Feb 11, 2010 at 11:55 AM by Shuchi Gauri Define Primary and Unique key. Primary key: Enforces uniqueness of the column they are attached to. It creates a clustered index on the column and does not allow null values. Every table can have only one primary key. Unique Key: Enforces uniqueness of the column they are attached to. It creates a non clustered index on the column and allows only one NULL value. SQL Server Data integrity What are ways to ensure integrity of data? Explain the types of Data integrity. What are integrity constraints? Explain their types. i.e. column and table constraints. Explain the classes of constraints. i.e. Primary key, Unique, Foreign Key and Check Constraints.............. Define temporary and extended stored procedure. Temporary Stored Procedure is stored in TempDB database. It is volatile and is deleted once connection gets terminated or server is restarted...... What is the actions prevented once referential integrity is enforced? We can't add records to a related table if there is no associated record in the primary table........... Describe in brief SQL Server monitoring ways. SQL Profiler is a tool that stores events from the server. SQL Profiler saves the events in the trace file...... SQL Server Transactions Architecture What operations do SQL Server transaction logs support? Explain the purpose of check points in a transaction log. What is write-ahead transaction log?.............

Name some transaction properties

Properties of Transactions: Transactions have the following four standard properties, usually referred to by the acronym ACID: Atomicity: ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state. Consistency: ensures that the database properly changes states upon a successfully committed transaction. Isolation: enables transactions to operate independently of and transparent to each other. Durability: ensures that the result or effect of a committed transaction persists in case of a system failure.

Explain Queues

Queues [purpose] Service Broker uses queues to provide loose coupling between the message sender and the message receiver. The sender can use the SEND command to put a message in a queue and then continue on with the application, relying on Service Broker to ensure that the message reaches its destination. [implementation] Queues permit a lot of scheduling flexibility. For example, the sender can send out multiple messages for multiple receivers to process in parallel. The receivers might not process the messages until long after they were sent, but because incoming messages are queued, the receivers can process them at their own rate and the sender doesn't have to wait for the receivers to finish before continuing.

Define @@Error and raiseerror.

Raiseerror is used to produce an error which is user defined or used to invoke an existing error present in sys.messages. They are most commonly used in procedures when any condition fails to meet. Example: SELECT COUNT(*) INTO :rows FROM student WHERE studentid = : studentid; IF :rows <> 0 THEN RAISE ERROR 1 MESSAGE 'Student id exists in the "Student" table.'; ENDIF; @@error is used to hold the number of an error. When a T-SQL statement is executed, @@error value is set to 0 by the SQL server. If an error occurs, the number of that error is assigned as a value. Example: the value of @@error can be checked for "0" value to be safe. Define @@Error and raiseerror. By Nishant Kumar @@Error It is system variable that returns error code of the SQL statement. If no error, it returns zero. @@Error is reset after each SQL statement. Raiseerror Raiseerror command reports error to client application. Sql server - Define @@Error and raiseerror - Feb 11, 2010 at 11:55 AM by Shuchi Gauri Define @@Error and raiseerror. SQL Server provides @@Error variable that depicts the status of the last completed statement in a given set of statements. If the statement was executed successfully the variable holds 0 value else it holds the number of the error message that occurred. Raiseerror is used to send messages to applications using the same format as a system error or warning generated by SQL Server engine. It can also return a user defined message. RAISEERROR is often used to help in troubleshooting, check values of data, returns variable value based messages, cause an execution to jump to a CATCH from TRY. Describe in brief exporting and importing utility? The Bulk Copy is a command utility that transfer SQL data to or from a data file. This utility mostly used to transfer huge data to SQL server from other database.... Define Distributed Query and Linked Server? Distributed Query is a query which can retrieve data from multiple data sources including distributed data........ Define temporary and extended stored procedure. Temporary Stored Procedure is stored in TempDB database. It is volatile and is deleted once connection gets terminated or server is restarted...... Define Primary and Unique key. The column or columns of the table whose value uniquely identifies each row in the table is called primary key. You can define column as primary key using primary key constraint while you create table..... Restricting query result means return of required rows instead of all rows of the table. This helps in reducing network traffic...... Having clause and Where clause Both are used for filtering of records based on filtered criteria..... Describe in brief authentication modes in SQL server. This is the default and recommended security mode. In this mode, access to SQL server is controlled by Windows NT.....

Reasons of poor performance of query

Reasons of poor performance of query. No indexes Excess recompilations of stored procedures. Procedures and triggers without SET NOCOUNT ON. Poorly written query with unnecessarily complicated joins, Highly normalized database design. Excess usage of cursors and temporary tables

What is recursion? Is it possible for a stored procedure to call itself or recursive stored procedure? How many levels of SP nesting is possible?

Recursion is method of problem solving where the solution is arrived at by repetitively applying the logic and solution to the subsets of the problem. Transact-SQL supports recursion. So, yes it is possible for a stored procedure to call itself. Stored procedures and managed code references can be nested up to 32 levels.

List out the difference between CUBE operator and ROLLUP operator

Sql server - Difference between CUBE operator and ROLLUP operator - Feb 27, 2010 at 18:50 am by Rajmeet Ghai Difference between CUBE operator and ROLLUP operator CUBE operator is used in the GROUP BY clause of a SELECT statement to return a result set of multidimensional (multiple columns) nature. Example: A table product has the following records:- Apparel Brand Quantity Shirt Gucci 124 Jeans Lee 223 Shirt Gucci 101 Jeans Lee 210 CUBE can be used to return a result set that contains the Quantity subtotal for all possible combinations of Apparel and Brand: SELECT Apparel, Brand, SUM(Quantity) AS QtySum FROM product GROUP BY Apparel, Brand WITH CUBE The query above will return: Apparel Brand Quantity Shirt Gucci 101.00 Shirt Lee 210.00 Shirt (null) 311.00 Jeans Gucci 124.00 Jeans Lee 223.00 Jeans (null) 347.00 (null) (null) 658.00 (null) Gucci 225.00 (null) Lee 433.00 ROLLUP:- Calculates multiple levels of subtotals of a group of columns. Example: SELECT Apparel,Brand,sum(Quantity) FROM Product GROUP BY ROLLUP (Apparel,Brand); The query above will return a sum of all quantities of the different brands. List out the difference between CUBE operator and ROLLUP operator.

SQL Profiler

Sql server - What is SQL Profiler? Explain its purpose. - Feb 27, 2010 at 19:00 PM by Shuchi Gauri What is SQL Profiler? Explain its purpose. SQL Profiler: It's a GUI interface to SQL Server Trace and allows monitoring an instance of the database engine and even Analysis services. It enables capturing sand saving data about every event into a file or table for performing analysis later. It helps understand and read all the trace information helping in identifying performance and bottleneck resolution. Sql server - SQL Profiler - Jan 14, 2010 at 09:00 AM by Nishant Kumar Define SQL Profiler. SQL Profiler is used to diagnose a problem by capturing events in a trace file. It is useful to step through queries to find the reason of slow performance. You can monitor SQL server performance in order to tune workload. SQL profiler also supports auditing of actions on the server Sql server - SQL Profiler - March 06, 2009 at 11:30 AM by Rajmeet Ghai What is SQL Profiler? Explain its purpose. A SQL server profile is used to capture server events of the SQL server. It helps you find out what is exactly going on in the SQL server. It is used to monitor and analyze SQL server events. It has a GUI for such activities. SQL profiler is used when: When some queries are performing slowly To trace and monitor events of the SQL server When indexes need to be fie tuned When security is a concern, it can be used to audit and review security activities It can be used when troubleshooting to find root cause of an issue

What is SQL Query Analyzer?

Sql server - What is SQL Query Analyzer? - Jan 14, 2010 at 09:00 AM by Nishant Kumar What is SQL Query Analyzer? SQL Query Analyzer is a tool that are used for various purposes such as Creating and executing queries. Creating database objects. Executing stored procedure. Analyzing query performance. Inserting, updating, or deleting rows in a table

What is a transaction and what are ACID properties?

Sql server - What is a transaction and what are ACID properties? - Jan 14, 2010 at 09:00 AM by Nishant Kumar What is a transaction and what are ACID properties? Transaction encapsulates SQL commands and work as a single unit. All the SQL statements defined in the transaction should work successfully. Every transaction follow tranaction properties ACID i.eAtomicity, Consistency, Isolation, Durability to qualify as valid transaction

What is lock escalation?

Sql server - What is lock escalation? - Jan 14, 2010 at 09:00 AM by Nishant Kumar What is lock escalation? Lock escalation is the process of converting low level locks (row locks, page locks) into higher level locks (table locks). Each lock is a memory structure. Too many locks mean more memory being occupied by locks. SQL Server escalates the many fine-grain locks to fewer coarse-grain locks

Different isolation levels

Sql server - different isolation levels - Jan 14, 2010 at 09:00 AM by Nishant Kumar Explain different isolation levels defined in SQL Sever. Isolation levels determine the degree of isolation of data during concurrent access. Read Uncommitted, Read Committed, Repeatable Read, Serializable are the different isolation levels defined in SQL Server. The default SQL Server isolation level is Read Committed. SET TRANSACTION ISOLATION LEVEL allows to define the isolation level at the connection level

Steps to secure an SQL Server.

Steps to secure an SQL Server. Use NT authentication. Use server database and application roles to control access to the data. Secure the physical database files using NTFS permissions. Use an ungues sable SA password. Restrict physical access to the SQL Server. Rename the Administrator account on the SQL Server computer. Disable the Guest account. Enable auditing. Use multiprotocol encryption. Set up SSL. Set up firewalls. Isolate SQL Server from the web server etc

Define Stuff Function and Replace Function.

Stuff Function It replaces existing character. Syntax STUFF(string_expression, start, length, replacement_characters) Replace Function It replaces existing character of all occurrences. REPLACE(string_expression, search_string, replacement_string) Sql server - Define Stuff Function and Replace Function - Feb 11, 2010 at 11:55 AM by Shuchi Gauri Define Stuff Function and Replace Function. Both STUFF and REPLACE are used to replace characters in a string. select replace('abcdef','ab','xx') results in xxcdef select replace('defdefdef','def','abc') results in abcabcabc We cannot replace a specific occurrence of "def" using REPLACE. select stuff('defdefdef',4, 3,'abc') results in defabcdef where 4 is the character to begin replace from and 3 is the number of characters to replace.

Define database objects.

Table SQL Server database stores information in a two dimensional objects of rows and columns called table. Data types Data types specify the type of data that can be stored in a column. Data types are used to apply data integrity to the column. SQL Server supports many data type like character, varchar, integer, binary, decimal, money etc. You can also create you own data type (User defined datatype) using system data type. Function Microsoft SQL server allows you to create functions. These functions are known as User Defined Functions. It represents business logic using one or more transact SQL statements. It can accept parameter(s) and can return scalar data value or a table data type. It can be used in the SQL statement which is added advantage over stored procedure. Index Index can be thought as index of the book that is used for fast retrieval of information. Index uses one or more column index keys and pointers to the record, to locate record. Index is used to speed up query performance. Kind of the indexes are clustered and non-clustered. Both exist as B-tree structure. Clustered index exists as sorted row on disk. Clustered index re-orders the table record. Clustered index contains record in the leaf level of the B-tree. There can be only one Clustered index possible in a table. Non-clustered is the index in which logical order doesn't match with physical order of stored data on disk. Non-clustered index contains index key to the table records in the leaf level. There can be one or more Non-clustered indexes in a table. Unique index is the index that is applied to any column of unique value. A unique index can also be applied to a group of columns. Constraint Using Constraint, SQL Server enforces the integrity to the database. It defines the rules that restrict unwanted data in the column. Constraints can be table constraints or column constraints. Primary Key Constraint It is defined for one column or more columns that ensure data within these columns uniquely identify each row of the table. Primary key column doesn't allow null value. It doesn't allow duplicate data in the table. Foreign Key Constraint Foreign Key is to create link between two tables. It creates parent-child relationships. It ensures that data from parent can be deleted only when there is no corresponding data in the child. Unique Key Constraint Unique key constraint is also like primary key except it allows null value to the column with unique key constraints. Check Key Constraint It enforces domain integrity by restricting unwanted data to the column. Rule Rule is older version of check function. You can apply only one rule to the column. You should first create rule using 'Create Rule' statement and then bind the rule to the column using sp_bindrule system stored procedure. Default It ensures default value to the column if you do not specify a value to the column while inserting a row. Stored Procedures A stored procedure is a compiled set of Transact-SQL statements. The business logic can be encapsulated using stored procedure. It improves network traffic by running set of Transact-SQL statements at one go. Trigger A trigger is a special type of event driven stored procedure. It gets initiated when Insert, Delete or Update event occurs. It can be used to maintain referential integrity. A trigger can call stored procedure. View View can be created to retrieve data from one or more tables. Query used to create view can include other views of the database. We can also access remote data using distributed query in a view. Sql server - Define database object - Feb 11, 2010 at 11:55 AM by Shuchi Gauri Define database object. Database object depicts the properties of a single SQL Server instance.

Explain different types of Locks in SQL Server.

There are 3 kinds of locks in SQL Server i.) Shared locks - they are used for operations which do not allow any change or update of data. For e.g. SELECT. ii.) Update locks - they are used when SQL Server wants to modify a page. The update page lock is then promoted to an exclusive page lock before actually making the changes. iii.) Exclusive locks - they are used for the data modification operations. For e.g. UPDATE, INSERT, or DELETE.

Define temporary and extended stored procedure.

Temporary Stored Procedure Temporary Stored Procedure is stored in TempDB database. It is volatile and is deleted once connection gets terminated or server is restarted. Temporary stored procedures are prefixed with pound sign #. One pound sign means that it is temporary within the session. Two pound signs ## means it is a global temporary procedure. Global temporary procedure can be called by any connection to the SQL server during its lifetime. Extended Stored Procedure It is basically created to expand features of stored procedure. It uses external program and compiles as DLL. Mostly xp_ prefix is used as naming convention for extended stored procedure. SQL Server Stored procedure - Nov 20, 2008 at 18:00 PM by Rajmeet Ghai Define stored procedure. Answer Stored procedure is a set of SQL commands that have been complied and stored on the database sever. They can be used in the code as and when required since hey stored. They need not be complied over and over again. They can be invoked by CALL procedure (..) or EXECUTE procedure(..) What are the purposes and advantages stored procedure? Answer Purposes and advantages of stored procedures: Manage, control and validate data It can also be used for access mechanisms Large queries can be avoided Reduces network traffic since they need not be recompiled Even though the stored procedure itself may be a complex piece of code, we need not write it over and over again. Hence stored procedures increases reusability of code Permissions can be granted for stored procedures. Hence, increases security. Determine when to use stored procedure to complete SQL Server tasks. Answer If a large piece of code needs to be performed repeatedly, stored procedures are ideal When hundreds of lines of SQL code need to be sent; it is better to use stored procedure through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network. When security is required. Sql server - Define temporary and extended stored procedure - Feb 11, 2010 at 11:55 AM by Shuchi Gauri Define temporary and extended stored procedure. Extended stored procedure: Extended stored procedures allow a user to plug in custom functionality into SQL Server. They work with API's and can be used to execute other Dlls. Example: sp_MSgetversion: Used to get version of the SQL Server instance. EXEC master..sp_MSgetversion Temporary stored procedure: They are created with a prefix of # in their name. Temporary stored procedures are temporary within the current session. One might use them for applying small business rules which are not redundant across sessions but are redundant within a single session. They are dropped automatically when the user session ends.

Explain the IN Operator

The IN operator allows you to specify multiple values in a WHERE clause. SQL IN Syntax SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...); Example SELECT * FROM Customers WHERE City IN ('Paris','London');

what is the SAVEPOINT Command

The SAVEPOINT Command: A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction. The syntax for SAVEPOINT command is as follows: SAVEPOINT SAVEPOINT_NAME; This command serves only in the creation of a SAVEPOINT among transactional statements. The ROLLBACK command is used to undo a group of transactions. The syntax for rolling back to a SAVEPOINT is as follows: ROLLBACK TO SAVEPOINT_NAME;

The SQL UNION Operator

The SQL UNION Operator The UNION operator is used to combine the result-set of two or more SELECT statements. Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order. SQL UNION Syntax SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2; Note: The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION. SQL UNION ALL Syntax SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;

Define transaction and transaction isolation levels.

Transaction A transaction is a set of operations that works as a single unit. The transactions can be categorized into explicit, autocommit, and implicit transactions. Every transaction should follow four properties called the ACID properties i.e. atomicity, consistency, isolation, and durability. Atomicity Transaction ensures either modification is committed or not committed. Consistency The data should be in consistent state when transaction process is completed. This means that all related tables are updated. Isolation SQL server supports concurrency when mean that data can be access or shared by many users. A transaction works in isolation and doesn't allow other transaction to work concurrently on the same piece of data. Durability Data is permanent once transaction is completed and it can be recovered even if system fails. There are four transaction isolation levels: Read uncommitted Read committed Repeatable read Serializable Read uncommitted isolation levels This is the lowest isolation level which can also be called as dirty read. Using this, you can read uncommitted data which can be rolled back at any point. With this level, SQL server uses share lock while reading data. Read committed isolation levels With this level, uncommitted data can't be read. This is default isolation level and uses shared lock while reading data. Repeatable read isolation levels It locks all the data that is used in the query. Serializable isolation levels It locks data set until the transaction will be completed. SQL Server Isolation Levels - May 18, 2009 at 10:00 AM by Rajmeet Ghai What is Isolation Levels? Isolation keeps the transactions of multiple users isolated from each other. Transaction isolation level controls the degree of locking which occurs when selecting data. This is necessary to avoid situations like: Lost updates- when two transactions read the same data. Dirty read- Occurs when a transaction reads data that has not been updated. Non repeatable reads- occur when different results are read multiple times. Phantoms- Occurs when row data matches the first time but does not match subsequent times. Sql server - Define transaction and transaction isolation levels - Feb 11, 2010 at 11:55 AM by Shuchi Gauri Define transaction and transaction isolation levels. A transaction may be defined as a collection of SQL statements, which collectively form a single unit of work. Transaction isolation levels are used to control locking behavior of all SQL Server SELECT statements issued by a connection. READ COMMITTED: Shared locks are held while any data is being read. READ UNCOMMITTED: Specifies isolation level 0 locking. There are thus no shared locks or exclusive locks. Lease restrictive of all the isolation levels. REPEATABLE READ: Locks are applied on all data being used by a query. However, new phantom rows can be inserted into the data set by another user and are included in later reads within the current transaction. SERIALIZABLE: Issues a range lock on data set, preventing other users to update or insert data into dataset until the transaction is complete. E.g.: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO BEGIN TRANSACTION Select * from Customers ... ... COMMIT TRANSACTION

Name some transaction control commands

Transaction Control: There are following commands used to control transactions: COMMIT: to save the changes. ROLLBACK: to rollback the changes. SAVEPOINT: creates points within groups of transactions in which to ROLLBACK SET TRANSACTION: Places a name on a transaction. Transactional control commands are only used with the DML commands INSERT, UPDATE and DELETE only. They can not be used while creating tables or dropping them because these operations are automatically commited in the database.

Describe triggers features and limitations.

Trigger features:- 1. Can execute a batch of SQL code for an insert, update or delete command is executed 2. Business rules can be enforced on modification of data Trigger Limitations:- 1. Does not accept arguments or parameters 2. Cannot perform commit or rollback 3. Can cause table errors if poorly written

What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?

Triggers are constructs in PL/SQL that need to be just created and associated with a table. Once they are created, when the table associated with it gets updated due to an UPDATE, INSERT or a DELETE, the triggers get implicitly fired depending upon the instructions passed to them. A table can have up to 12 triggers defined on it. Triggers can't be invoked on demand. They get triggered when the associated INSERT, DELETE or UPDATE is performed.

Explain Integrity

Two important steps in planning tables are to identify valid values for a column and to decide how to enforce the integrity of the data in the column. Data integrity falls into these categories: Entity integrity Domain integrity Referential integrity User-defined integrity Entity Integrity Entity integrity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier column(s) or the primary key of a table (through indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties). Domain Integrity Domain integrity is the validity of entries for a given column. You can enforce domain integrity by restricting the type (through data types), the format (through CHECK constraints and rules), or the range of possible values (through FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions, and rules). Referential Integrity Referential integrity preserves the defined relationships between tables when records are entered or deleted. In Microsoft® SQL Server™ 2000, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys (through FOREIGN KEY and CHECK constraints). Referential integrity ensures that key values are consistent across tables. Such consistency requires that there be no references to nonexistent values and that if a key value changes, all references to it change consistently throughout the database. User-Defined Integrity User-defined integrity allows you to define specific business rules that do not fall into one of the other integrity categories. All of the integrity categories support user-defined integrity (all column- and table-level constraints in CREATE TABLE, stored procedures, and triggers).

Explain the Types of SQL Keys

Types of SQL Keys We have following types of keys in SQL which are used to fetch records from tables and to make relationship among tables or views. Super Key Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table.Example : Primary key, Unique key, Alternate key are subset of Super Keys. Candidate Key A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key. Example: In below diagram ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as Primary Key. Primary Key Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It can not accept null, duplicate values. Only one Candidate Key can be Primary Key. Alternate key A Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key. Example: In below diagram RollNo and EnrollNo becomes Alternate Keys when we define ID as Primary Key. Composite/Compound Key Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key. Unique Key Uniquekey is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it can not have duplicate values. For more help refer the article Difference between primary key and unique key. Foreign Key Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values. For more help refer the article Difference between primary key and foreign key. Example : We can have a DeptID column in the Employee table which is pointing to DeptID column in a department table where it a primary key.

What is transact-SQL? Describe its types?

Types of Transact-SQL SQL Server Provides three types of Transact-SQL statements namely DDL, DCL, and DML. Data Definition Language (DDL) It allows creating, altering and dropping database objects. Data Control Language (DCL) It is used to control access to data in the database. It controls permission on the database objects using grant, revoke or deny statement. Data Manipulation Language (DML) It involves retrieval, insertion, deletion, modification in the database. It includes select, insert, update, and delete command. Sql server - What is transact-SQL? Describe its types? - Feb 11, 2010 at 11:55 AM by Shuchi Gauri What is transact-SQL? Describe its types? T-SQL is used by any application trying to access/use SQL Server. All applications thus interact with an SQL Server instance using T-SQL statements. Types of T-SQL: Data types User defined temporary objects Specialized functions global variables System and extended procedures Cursors

Explain the different types of TRIGGERS

Types of Triggers In Sql Server we can create four types of triggers Data Definition Language (DDL) triggers, Data Manipulation Language (DML) triggers, CLR triggers and Logon triggers. DDL Triggers In SQL Server we can create triggers on DDL statements (like CREATE, ALTER, and DROP) and certain system defined stored procedures that perform DDL-like operations. Example : If you are going to execute the CREATE LOGIN statement or the sp_addlogin stored procedure to create login user, then both these can execute/fire a DDL trigger that you can create on CREATE_LOGIN event of Sql Server. We can use only FOR/AFTER clause in DDL triggers not INSTEAD OF clause means we can make only After Trigger on DDL statements. DDL trigger can be used to observe and control actions performed on the server, and to audit these operations. DDL triggers can be used to manage administrator tasks such as auditing and regulating database operations. DML Triggers In SQL Server we can create triggers on DML statements (like INSERT, UPDATE, and DELETE) and stored procedures that perform DML-like operations. DML Triggers are of two types After Trigger (using FOR/AFTER CLAUSE) This type of trigger fires after SQL Server finish the execution of the action successfully that fired it. Example : If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire only after the row passes all the constraints, like as primary key constraint, and some rules. If the record/row insertion fails, SQL Server will not fire the After Trigger. Instead of Trigger (using INSTEAD OF CLAUSE) This type of trigger fires before SQL Server starts the execution of the action that fired it. This is differ from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table. Example : If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire before the row passes all the constraints, such as primary key constraint and some rules. If the record/row insertion fails, SQL Server will fire the Instead of Trigger. CLR Triggers CLR triggers are special type of triggers that based on the CLR (Common Language Runtime) in .net framework. CLR integration of triggers has been introduced with SQL Server 2008 and allows for triggers to be coded in one of .NET languages like C#, Visual Basic and F#. We coded the objects(like trigger) in the CLR that have heavy computations or need references to objects outside the SQL Server. We can write code for both DDL and DML triggers, using a supported CLR language like C#, Visual basic and F#. I will discuss CLR trigger later. Logon Triggers Logon triggers are special type of trigger that fire when LOGON event of Sql Server is raised. This event is raised when a user session is being established with Sql Server that is made after the authentication phase finishes, but before the user session is actually established. Hence, all messages that we define in the trigger such as error messages, will be redirected to the SQL Server error log. Logon triggers do not fire if authentication fails. We can use these triggers to audit and control server sessions, such as to track login activity or limit the number of sessions for a specific login. /////////////////////////////////// Synatx for Logon Trigger CREATE TRIGGER trigger_name ON ALL SERVER [WITH ENCRYPTION] {FOR|AFTER} LOGON AS sql_statement [1...n ] Syntax for Trigger CREATE TRIGGER trigger_name ON {table|view} [WITH ENCRYPTION|EXECUTE AS] {FOR|AFTER|INSTEAD OF} {[CREATE|ALTER|DROP|INSERT|UPDATE|DELETE ]} [NOT FOR REPLICATION] AS sql_statement [1...n ] trigger_name This is the name of the trigger. It should conform to the rules for identifiers in Sql Server. table|view This is the table/view on which the trigger is to be created. ENCRYPTION This option is optional. If this option is specified, original text of the CREATE TRIGGER statement will be encrypted. EXECUTE AS This option is optional. This option specifies, the security context under which the trigger is executed. FOR/AFTER FOR/AFTER specifies that the trigger is After Trigger. AFTER is the default, if FOR is the only keyword specified.AFTER triggers cannot be defined on views. INSTEAD OF INSTEAD OF specifies that the trigger is Instead Of Trigger. CREATE|ALTER|DROP|INSERT|UPDATE|DELETE These keywords specify on which action the trigger should be fired. One of these keywords or any combination of these keywords in any order can be used. NOT FOR REPLICATION Indicates that the trigger should not be executed when a replication process modifies the table involved in the trigger. AS After this we specifies the actions and condition that the trigger perform. sql_statement These are the trigger conditions and actions. The trigger actions specified in the T-SQL statements. Note The name of a trigger should follow the rules for identifiers. DML trigger can be composed by any T-SQL statements, except CREATE DATABASE, ALTER DATABASE, DROP DATABASE, LOAD DATABASE, LOAD LOG, RECONFIGURE, RESTORE DATABASE, and RESTORE LOG statements. You cannot create triggers against system tables or dynamic management views. Moreover, the TRUNCATE TABLE statement does not fire a trigger because this operation does not log individual row deletions. If you use the DATABASE option, the scope of your DDL trigger will be the current database. If you use the ALL SERVER option, the scope of your DDL triggers to the current server. AFTER triggers cannot be defined on views. AFTER is the default, if FOR is the only keyword specified. Summary In this article I try to explain types of Sql Server triggers. I hope after reading this article your sql triggers concepts will be strong. I will dicuss all these triggers with example in my next post. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

User Defined Datatypes

User defined data types are most commonly used when consistency among several tables storing the same type of data is desired. User defined data types can also be used when several tables must store the same type of data in a column .Consistency in terms of same data type, length, and null ability is required. The user defined data type in SQL is based on system data type. When a user defined data type is created it must have a name, a system data type, whether or nor it can accept NULL values. Syntax: Sp_addtype is used to create a user defined data type in transact SQL. Example: To create a user defined data type for postal code. Sp_addtype postal_Code, 'varchar(10)' , 'NOT NULL'

What is LOG Shipping?

What is LOG Shipping? Log shipping defines the process for automatically taking backup of the database and transaction files on a SQL Server and then restoring them on a standby/backup server. This keeps the two SQL Server instances in sync with each other. In case production server fails, users simply need to be pointed to the standby/backup server. Log shipping primarily consists of 3 operations: Backup transaction logs of the Production server. Copy these logs on the standby/backup server. Restore the log on standby/backup server. Sql server - What is LOG Shipping? - Jan 14, 2010 at 09:00 AM by Nishant Kumar What is LOG Shipping? This process synchronizes two SQL servers and thus provides ready server in case one fails. It automatically backup transaction log file throughout the day and restore them on the standby server. Sql server - What is LOG Shipping? - March 04, 2009 at 15:30 PM by Rajmeet Ghai Define Log shipping. Log shipping is the process of shipping or automatically sending the transaction log which is already backed up at the primary server, to the secondary server. Once the log is copied to the secondary server instance, it is restored. The log can be shipped from one primary server instance to multiple secondary server instances. Log shipping increases data availability because if the primary database becomes unavailable, any of the secondary databases can be brought online manually.

What is RAID?

What is RAID? RAID stands for Redundant Array of Inexpensive Disks. It provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance

What is Replication in SQL Server?

What is Replication in SQL Server? Replication allows creating copies of data in separate databases along with keeping all databases synchronized by replicating modification in one copy to all the copies. Data can be effectively distributed over network using replication

What is Reporting Services?

What is Reporting Services? SQL Server's Reporting services offer a variety of interactive and printed reports managed by a web interface. Reporting services is a server based environment. How does the report manager work in SSRS? Report manager is a web application. In SSRS it is accessed by a URL. The interface of this Report manager depends on the permissions of the user. This means to access any functionality or perform any task, the user must be assigned a role. A user with a role of full permissions can entire all the features and menus of the report. To configure the report manager, a URL needs to be defined. What are the Reporting Services components? Reporting services components assist in development. These processing components include some tools that are used to create, manage and view reports. A report designer is used to create the reports. a report sever is used to execute and distribute reports. a report manager is used to manage the report server. SQL Server Reporting Services vs Crystal Reports. Crystal reports are processed by IIS while SSSR have a report server. Caching in Crystal reports is available through cache server. On the other hand, caching in SSSR is available for Report history snapshots. Crystal reports have standards and user defined field labels. SSSR allows only user defined field labels.

What is SQL Server Service broker?

What is SQL Server Service broker? SQL service broker provides a mechanism to queue provide reliable messaging for SQL server. Using SQL server, the service broker assists in passing messages between applications. It also rejects messages with invalid format and performs retries. Service broker helps to make scalable and secure database applications.

What is Service Broker?

What is Service Broker? Catalog views serve as a repository to store static metadara like server side and database specific objects. This includes logins, tables, stored procedures etc. It is always better to use catalogue views to view system data rather than using system tables.

What is a deadlock and what is a live lock?

What is a deadlock and what is a live lock? 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 terminated. SQL Server detects deadlocks and terminates one user's process. A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering.A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely

What is a join and explain types of joins.

What is a join and explain types of joins. Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table. Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS

What is a self join in SQL Server?

What is a self join in SQL Server? Two instances of the same table will be joined in the query

What is an extended stored procedure in SQL Server?

What is an extended stored procedure in SQL Server? An extended stored procedure compiles as DLL and are created to expand capabilties of user defined stored procedure. It uses xp_ prefix as naming convention

What is an index?

What is an index? Indexes of SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker. Indexes are of two types. Clustered indexes and non-clustered indexes. Rows in the table are stored in the order of the clustered index key. There can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures. Leaf level nodes having the index key and it's row locater Disadvantages of the Indexes are Use of intexes slow down Data modification operations (such as INSERT, UPDATE, DELETE). Every time data changes in the table, all the indexes need to be updated. Indexes need disk space, the more indexes you have, more disk space is used

What is blocking?

What is blocking? When one connection from an application holds a lock and a second connection requires a conflicting lock type

What is built-in function? Explain its type i.e. Rowset, Aggregate and scalar.

What is built-in function? Explain its type i.e. Rowset, Aggregate and scalar. A built in function of sql is used for performing calculations. These are standard functions provided by sql. Aggregate functions: these functions perform calculation on a column and return a single value. Example: AVG(), SUM(), MIN(), MAX() Scalar functions : these functions perform calculation on an input value and return a single value. Example: ROUND(), MID(), LCASE(), UCASE()