Relational Database Management System (RDBMS) :-
RDBMS
are database management system that maintain data records and indices
in tables. relationship may be created and maintained across and among
the data and tables.
Normalization :-
The process of organizing data to minimize redundancy. a set of rule
that have been established to help in the design of the table that are
meant to be connected through relationship.
Candidate Key :-
A table may have more than one combination of columns that could
uniquely identify the row in the table; each combination is called as
candidate key.
Send email from database :-
USE [SUJIT]
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'mathew@xyz.com;
saadhya@xyz.com; anirudh@pqr.com'
@body = 'A warm wish for your
future endeavor',
@subject = 'This mail was sent using
Database Mail' ;
GO
Cross
join vs Full outer join :-
Cross Join : No join conditions are specified. Results in pairs of rows. Results in Cartesian product of two tables.
Full Outer Join: A combination of both left and right outer joins. Results in every row from both of the tables , at least once. Assigns NULL for unmatched fields.
Purposes of OPENXML clause sql server stored procedure :-
OPENXML parses the XML data in SQL Server in an efficient manner. It’s primary ability is to insert XML data to the RDB. It is also possible to query the data by using OpenXML. The path of the XML element needs to be specified by using ‘xpath’.
The following is a procedure for retrieving xml data:
Cross Join : No join conditions are specified. Results in pairs of rows. Results in Cartesian product of two tables.
Full Outer Join: A combination of both left and right outer joins. Results in every row from both of the tables , at least once. Assigns NULL for unmatched fields.
Purposes of OPENXML clause sql server stored procedure :-
OPENXML parses the XML data in SQL Server in an efficient manner. It’s primary ability is to insert XML data to the RDB. It is also possible to query the data by using OpenXML. The path of the XML element needs to be specified by using ‘xpath’.
The following is a procedure for retrieving xml data:
DECLARE @index int
DECLARE @xmlString
varchar(8000)
SET @xmlString
='<Persons>
<Person id="15201">
<Name>Sujit</Name>
<PhoneNo>7668768676</PhoneNo>
</Person>
<Person id="15202">
<Name>Sandra</Name>
<PhoneNo>9342673212</PhoneNo>
</Person>
</Persons>'
EXEC sp_xml_preparedocument @index
OUTPUT,
@xmlString
SELECT * FROM OPENXML (@index, 'Persons/Person') WITH (id varchar(10), Name varchar(100) 'Name' , PhoneNo varchar(50) 'PhoneNo')
EXEC sp_xml_removedocument @index
Output: The above code snippet results the following:
15201 Sujit 7668768676
15202 Sandra 9342673212
Order in which the SQL query is executed :-
The query goes to the shared pool that has information like parse tree and execution plan for the corresponding statement.
Then validates the SQL statement and validates the source(table).
Acquire locks.
Checks all the privileges.
Execute the query.
Fetch the values for SELECT statement
Displays the fetched values.
the sequence is:
SELECT .........
FROM ..........
WHERE ..........
GROUP BY ...........
HAVING .............
Store pdf file in sql server :-
Create a column as type ‘blob’ in a table.
Keyword WITH ENCRYPTION :-
It is a way to convert the original text of the stored procedure into encrypted form.
Example,
CREATE PROCEDURE salary_sum
WITH ENCRYPTION
AS
SELECT sum(salary)
FROM employee
WHERE emp_dept LIKE Develop
SQL service broker :-
A service broker allows you to exchange messages between applications using SQL server as the transport mechanism. Message is a piece of information that needs to be shared. A service broker can also reject unexpected messages in disorganized format.
User defined datatypes :-
User defined data types are based on system data types. They should be used when multiple tables need to store the same type of data in a column and you need to ensure that all these columns are exactly the same including length, and nullability.
Lock escalation :-
Lock escalation are used to convert row locks and page locks into table locks thereby “escalating” the smaller or finer locks. This increases the system performance as each lock is nothing but a memory structure. Too many locks would mean more consumption of memory.
Blocking :-
Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type.
Public Role in SQL Server :-
Every database has a public role which holds all the default permissions for the users in a database. This role cannot be dropped.
XPath :-
XPath is an expression to select an xml node in an XML document. It allows the navigation on the XML document to the straight to the element where we need to reach and access the attributes.
GO Command :- GO command indicates the end of the SQL statements.
NULL value :- NULL value means that no entry has been made into the column. It states that the corresponding value is either unknown or undefined.
DBCC Commands :- DBCC (Database consistency checker) act as Database console commands for SQL Server to check database consistency. commands includes dbcc checktable('opr_bookingdetails'), dbcc checkdb, DBCC CHECKALLOC, DBCC CHECKFILEGROUP, DBCC CHECKCATALOG, DBCC CHECKIDENT, DBCC CHECKCONSTRAINTS
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.
Local and a Global temporary table :-
Local
Only available to the current Db connection for current user and are cleared when connection is closed.
Global
Available to any connection once created. They are cleared when the last connection is closed.
STUFF vs REPLACE function :- Both STUFF and REPLACE are used to replace characters in a string.
select STUFF('Pune',1,1,'X-') result is X-une
select replace('Pune','ne','ma') result is Puma
Guidelines to use bulk copy utility of SQL Server :-
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.
Advantages of using Stored Procedures :-
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.
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.
Code efficient transactions :-
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.
Restrictions applicable while creating views :-
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.
The query that defines the view cannot include the ORDER BY, COMPUTE, or COMPUTE BY clauses or the INTO keyword.
You cannot create temporary views
You cannot create views on temporary tables.
Events recorded in a transaction log :-
The start and end of each transaction
Every data modification
Every extent allocation or deallocation
The creation or dropping of a table or index
When checkpoints are created in a transaction log :-
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.
Truncate vs Delete commands :-
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.
Problem Occur if do not lock properly :-
Isolation keeps the transactions of multiple users isolated from each other. Transaction isolation level controls the degree of locking which occurs when selecting data.
• Lost updates- when two transactions modify the same data at the same time. and the transaction the completes first is lost.
Isolation keeps the transactions of multiple users isolated from each other. Transaction isolation level controls the degree of locking which occurs when selecting data.
• Lost updates- when two transactions modify the same data at the same time. and the transaction the completes first is lost.
• Non repeatable reads- it occurs if a transaction is able to read the same row multiple times and get a different result each time.
• Dirty read- It happens if you run a report while transaction are modifying the data that you are reporting.• Phantoms read- If a transaction being able to read a row on the first read, but not being able able to modify the same row due to another transaction deleting rows from the same table.
Lock types :-
Shared lock : It allow transaction to read data with select statements. other transaction are allowed to read the data at the same time; however, no transaction are allowed to modify data until the shared lock are released.
Update lock : It acquired just prior to modifying the data. if the transaction modifies a row, then the update lock is escalated to an exclusive lock; otherwise it is converted to a shared lock.
Exclusive lock : It completely lock the resources from any type of access including reads.
Bulk update lock : It used when performing a bulk-copy of the data into a table.
Four transaction isolation levels :-
Read committed : In this shared lock is held for the duration of transaction, meaning that no other transaction can change the data at the same time.
Read uncommitted : no shared lock and no exclusive locks are honored.
Repeatable read : It disallow dirty and non-repeatable read.
Serializable : It holds shared lock on the range of data. it doesn't allow the insertion of new row in the range that is locked.
Example,
Repeatable read : It disallow dirty and non-repeatable read.
Serializable : It holds shared lock on the range of data. it doesn't allow the insertion of new row in the range that is locked.
Example,
SET TRANSACTION ISOLATION
LEVEL REPEATABLE
READ
select * from Employee
Identity vs 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.
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 exp. SET @Guid=NEWID().
Example,
CREATE TABLE #MyUniqueTable
(UniqueColumn UNIQUEIDENTIFIER DEFAULT NEWID(),
Characters VARCHAR(10) )
GO
INSERT INTO #MyUniqueTable(Characters) VALUES ('abc')
INSERT INTO #MyUniqueTable VALUES (NEWID(), 'def')
select * from #MyUniqueTable
GO
Result is,
UniqueColumn Characters
D1BFC48E-F911-4965-AB4A-19EDCA0591D9 abc
134A7CEE-6081-469F-A1BA-95236237E835 def
D1BFC48E-F911-4965-AB4A-19EDCA0591D9 abc
134A7CEE-6081-469F-A1BA-95236237E835 def
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 monitoring ways :-
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.
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.
SQL Server Optimization :-
1. Restricting query result means return of required rows instead of all rows of the table. This helps in reducing network traffic.
2. Use stored procedure instead of heavy duty queries.
Stored procedure exists as compiled objects on disk. Moreover, SQL server also keeps optimized execution plan for stored procedure. This really saves time and speeds up execution.
3. Avoid SQL cursor for optimal performance .
4. Use constraints instead of trigger.
5. Use table variable instead of temporary tables. When you create a temporary table (#TABLE) it physically creates the table in tempdb. It is an added overhead to create table. A table variable doesn’t need to be created in the database.
6. Avoid using Having clause.
7. Include SET NOCOUNT ON in the stored procedure. prevents stored procedure to send messages indicating number of rows affected.
Having clause vs Where clause :-
• 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.
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.
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 CONCATENATE statement :- This clause combine together (concatenate) the results from several different fields.
SELECT CONCAT(first_name, last_name) FROM employee WHERE salary > 1000
Authentication Modes in SQL Server :-
a. Windows Authentication Mode (Windows Authentication): uses user’s Windows account
b. Mixed Mode (Windows Authentication and SQL Server Authentication): uses either windows or SQL server
@@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.
@@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.
Transact-SQL :-
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.
SQL Server Data Integrity :-
Data Integrity ensures that data is accurate, correct, and valid.
Entity Integrity : No duplicate rows
Domain Integrity : Valid entry for column
Referential Integrity : Row can not delete, which is used by other records.
User-Defined Integrity : Enforce some specific business rules.
BCP
Utility and DTS Package :-
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.
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.
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.
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 }
} )
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.
Stored Procedure :-
Stored procedure is a set of SQL commands that have been complied and stored on the database server. They can be used in the code as and when required since they stored.
Primary and Unique key :-
Primary Key,
• The column or columns of the table whose value uniquely identifies each row in the table is called primary key.
• By default, clustered index in created with the column having primary key.
• A column defined as primary key doesn’t allow null value.
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 Profiler :-
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 fine-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
Constraints :-
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 constraints define in SQL Server.
RAID :-
RAID stands for Redundant Array of Inexpensive Disks. it is a disk system that comprises multiple disk drives (an array) to provide higher performance, reliability, storage capacity, and lower cost.
Reasons of poor performance of query :-
No indexes
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
Troubleshoot performance problems in SQL Server :-
SET SHOWPLAN_ALL ON
SET SHOWPLAN_TEXT ON
SET STATISTICS IO ON
SQL Server Profiler
Graphical execution plan in Query Analyzer
Deadlock vs Livelock:-
Deadlock,
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.
Livelock,
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering.
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
Types of BACKUPs avaialabe in SQL Server :-
Full database backup.
Differential database backup.
Transaction log backup.
Filegroup backup
Database replicaion :-
The process of copying/moving data between databases on the same or different servers.
Snapshot replication,
Transactional replication,
Merge replication
Cursors :-
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
Join :-
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.
Extended stored procedure :-
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.
Triggers :-
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.
Extent :-
An extent is 8 continuous pages to hold server object.
types,
Uniform extents - This type of extent contains data from one table.
Mixed extents - This type of extent contains data from two to eight different tables
Collation :-
Collation is the order that SQL Server uses for sorting or comparing textual data. There are three types of sort order Dictionary case sensitive, Dictonary - case insensitive and Binary.
Users names and passwords stored in SQL Server :-
master db in the sysxlogins table
Move data along with schema of the server object :-
We can copy data along schema using DTS package.
Sub-query :-
Sub-query is a query within a Query.
sp_grantlogin, sp_denylogin and sp_revokelogin :-
All these three are the system stored procedure used to manage windows account authentication. sp_grantlogin allows to add and grant access to windows account. sp_denylogin denies user to access server without deleting windows account from SQL server. sp_revokelogin delete windows account from SQL server.
SQL query to retrieve all tables of the database :-
Select name from sysObjects where xtype='u'
UPDATE_STATISTICS command :-
UPDATE_STATISTICS updates the indexes on the tables when there is large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account.
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.
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.
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.
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 }
} )
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.
Stored Procedure :-
Stored procedure is a set of SQL commands that have been complied and stored on the database server. They can be used in the code as and when required since they stored.
Primary and Unique key :-
Primary Key,
• The column or columns of the table whose value uniquely identifies each row in the table is called primary key.
• By default, clustered index in created with the column having primary key.
• A column defined as primary key doesn’t allow null value.
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 Profiler :-
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 fine-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
Constraints :-
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 constraints define in SQL Server.
RAID :-
RAID stands for Redundant Array of Inexpensive Disks. it is a disk system that comprises multiple disk drives (an array) to provide higher performance, reliability, storage capacity, and lower cost.
Reasons of poor performance of query :-
No indexes
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
Troubleshoot performance problems in SQL Server :-
SET SHOWPLAN_ALL ON
SET SHOWPLAN_TEXT ON
SET STATISTICS IO ON
SQL Server Profiler
Graphical execution plan in Query Analyzer
Deadlock vs Livelock:-
Deadlock,
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.
Livelock,
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering.
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
Types of BACKUPs avaialabe in SQL Server :-
Full database backup.
Differential database backup.
Transaction log backup.
Filegroup backup
Database replicaion :-
The process of copying/moving data between databases on the same or different servers.
Snapshot replication,
Transactional replication,
Merge replication
Cursors :-
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
Join :-
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.
Extended stored procedure :-
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.
Triggers :-
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.
Extent :-
An extent is 8 continuous pages to hold server object.
types,
Uniform extents - This type of extent contains data from one table.
Mixed extents - This type of extent contains data from two to eight different tables
Collation :-
Collation is the order that SQL Server uses for sorting or comparing textual data. There are three types of sort order Dictionary case sensitive, Dictonary - case insensitive and Binary.
Users names and passwords stored in SQL Server :-
master db in the sysxlogins table
Move data along with schema of the server object :-
We can copy data along schema using DTS package.
Sub-query :-
Sub-query is a query within a Query.
sp_grantlogin, sp_denylogin and sp_revokelogin :-
All these three are the system stored procedure used to manage windows account authentication. sp_grantlogin allows to add and grant access to windows account. sp_denylogin denies user to access server without deleting windows account from SQL server. sp_revokelogin delete windows account from SQL server.
SQL query to retrieve all tables of the database :-
Select name from sysObjects where xtype='u'
UPDATE_STATISTICS command :-
UPDATE_STATISTICS updates the indexes on the tables when there is large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account.
XML support SQL server extends :-
SQL server can return XML document using FOR XML clause. XML documents can be added to SQL Server database and you can use the OPENXML clause to display the data from the document as a relational result set. SQL Server 2000 supports XPath queries.
Concepts of faster differential backups :-
Differential backups are a faster mechanism of taking backups. The back up taken is only of the recently made changes in the data from the last differential backup taken.
Concepts of Parallel Database consistency check (DBCC) :-
DBCC CHECKDB verifies the allocation and structural integrity of all the database objects. It performs physical consistency check on indexed views. It is safe as it identifies and corrects maximum errors. DBCC CHECKDB does not enforce any locks on any tables; however, it does lock the schemas that prevent metadata changes.
It performs the following operations on every table:
• Verifies index and data pages are linked correctly.
• Verifies indexes are as per their sort order.
• Verifies consistency of pointers.
• Verifies that every page has reasonable amount of data.
Example: To checks the data page integrity of the authors table.
Indexed view :-
Indexed views are views which have a unique clustered index. This improves data access performance on complex queries.
Distributed partitioned views :-
For performance gains, one might need to distribute database work over multiple servers. This is achieved by horizontally partitioning large tables over multiple servers. I.e. splitting a table with many rows into many tables with few rows. For data that is spread across multiple servers, different types of statements are needed for retrieving data.
Example,
Full-text indexing :-
A full text index has an index of type FULL TEXT. Full text indexing can only done for CHAR, VARCHAR, or TEXT columns.
Types of Database files :-
• Data file: This stores all the data. It has an extension .mdf
• Log file: This stores all the transaction log information. It has an extension .ldf
• Additional data files: Any additional data is stored in these files. It has an extension .ndf
Make views updatable in SQL Server 2000 :-
INSTEAD OF Triggers: These can be created on a view to make a view updateable. The INSTEAD OF trigger is executed instead of the data modification statement on which trigger is defined. This trigger enables the user to provide set of actions that must take place to process data modification statement.
Example: Causes the trigger to fire instead of the update (action)
CREATE TRIGGER Employee_update ON Employee INSTEAD OF
UPDATE AS
{
TRIGGER Definition
}
Partitioned views: It’s a view defined by a UNION ALL of member tables structured in same way but stored separately as multiple tables in either same SQL Server or multiples ones (federated database servers).
Partitioning :-
Partitioning distributes database tables over different database which may reside on a different server. Vertical portioning helps to fit more rows on a database page to improve data access performance. Partitioning helps to do multiple tasks simultaneously. It increases performance. It helps to manage data.
How OPENQUERY function is used to excess external data :-
OPENQUERY is used to execute the specified query on the linked sever. OPENQUERY can be referenced in from the FROM clause just like a table name.
Example:
SELECT * FROM OPENQUERY (OracleSvr, 'SELECT name, id FROM joe.titles')
Drop an assembly from SQL SERVER :-
The DROP ASSEMBLY statement allows removing an assembly along with its associated files from the database.
Syntax:
DROP ASSEMBLY assemblyname[,…n]
[WITH NO DEPENDENTS]
[;]
One cannot use ALTER ASSEMBLY when a SQLCLR method signature that’s currently being used/executed has changed. In such a scenarios we need to drop the Assembly and then re-create it using CREATE ASSEMBLY for updating changes.
To see assemblies loaded in SQL SERVER :-
Select * from sys.assemblies
.NET Appdomain allocated in SQL SERVER 2005 :-
.NET Appdomain allocation depends on the following:
• For every database a different Appdomain is allocated
• For every user a different Appdomain is allocated
Syntax for creating a new assembly in SQL Server 2005 :-
Assemblies can be created using CREATE ASSEMBLY command. It takes the path of the DLL as the parameter.
Example,
CREATE ASSEMBLY SystemWeb
from 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'
with permission_set = unsafe
CLR Integration vs. Extended Stored Procedures :-
CLR Integration provides a more convenient and robust solution to Extended stored procedures for implementing server side logic. Using Common Language Runtime integration allows the results constructed by the function to be queried in SELECT statements by embedding them in the FROM clause. CLR code objects can be written in .NET languages which are safe and fast. CLR code can work with data types like XML, varchar binary.
CLR Integration,
Not supported by older versions of SQL Server
Can be written using any .NET compliant language.
Slower performance
Extended Stored Procedures,
Supported by older versions of SQL Server
Can be only written using c/c++
Faster performance
Configure CLR for SQL Server :-
CLR integration is off by default in SQL Server. We need to set it on using the following statement:
Sp_configure ‘clr enabled’,1
GO
RECONFIGURE
GO
Create functions in SQL Server using .NET :-
Steps to create functions in SQL Server using .NET:
• Create a .NET class using a .NET compliant language such as c#
• Compile the class into a DLL
• Register the DLL in SQL Server using CREATE ASSEMBLY
• Create a function in SQL Server to access the .NET function.
E.g.:
Create .NET Class
Register DLL in SQL Server
CREATE ASSEMBLY asmHelloWorld FROM 'C:\HelloWorld.dll'
Access this function within an SQL Server Function
Calling this Function
SQL Server Identifier :-
Everything in a SQL server can have an identifier. Be it server, view, trigger, column etc. any database object name is an identifier. Identifiers may or may not be required for all objects.
Example:
RDBMS :-
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables.
Normalization :-
The process of organizing the data to minimize redundancy. A set of rules that have been established to help in the design of table are meant to be connected through relationship.
Normal Forms,
1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF: Eliminate Columns Not Dependent On Key
If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key
BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many relationships.
Stored Procedure :-
It is a group of SQL statement that have been previously created and stored in the server database. It accepts input parameter so that a single procedure can be user over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. e.g. sp_helpdb, sp_renamedb, sp_depends etc.
Advantages,
Stored procedure can reduced network traffic and latency, boosting application performance.
Stored procedures help promote code reuse.
Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
Stored procedures provide better security to your data.
User Defined Function :-
User defined function allow defining its own T-SQL function that can accept 0 or more parameters and return a single value or a table data type.
Types,
Scalar User Defined Function: it returns one of the scalar data types.
Table Value User defined function: it specifies a select statement after the return keyword. This select statement is executed and all its matching rows are returned as the value of the function.
Trigger :-
A trigger is a SQL procedure that initiates an action when an event (Insert, Update or Delete) occurs. A trigger can’t be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table. Trigger is event driven and is attached to a specific table.
Types,
After Trigger: Triggers that run after an update, insert, or delete can be used in several ways:
Triggers can update, insert, or delete data in the same or other tables. This is useful to maintain relationships between data or to keep audit trail information.
Triggers can check data against values of data in the rest of the table or in other tables. This is useful when you cannot use RI constraints or check constraints because of references to data from other rows from this or other tables.
Triggers can use user-defined functions to activate non-database operations. This is useful, for example, for issuing alerts or updating information outside the database.
Working with INSERT Triggers:
Working with DELETE Triggers:CREATE TRIGGER DelhiDel ON [Customers]
Working with UPDATE Triggers:
Instead of Triggers: Instead Of Triggers fire instead of the operation that fires the trigger, so if you define an Instead Of trigger on a table for the Delete operation, they try to delete rows, they will not actually get deleted (unless you issue another delete instruction from within the trigger) as in this simple example:
View :-
It’s a virtual table consists of columns from one or more tables. It is a query stored in an object. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table.
Cursor :-
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
In order to work with a cursor we need to perform some steps in the following order:
1. Declare cursor
2. Open cursor
3. Fetch row from the cursor
4. Process fetched row
5. Close cursor
6. Deallocate cursor
Example,
Constraints :-
It’s a property assign to a column or set of column in a table that prevent inconsistent data value from being placed in the column(s). It enforces data integrity.
Different Constraints are,
Primary key: it is a unique identifier for a row within a database table. It can’t allow null value.
Foreign key: a key in one table points to a primary key in another table.
Unique key: this constraints guarantee that the values in a column or set of columns are unique. It accepts a null value.
Check: it uses to limit the values that can be placed in a column.
Not Null: it enforces that the column will not accept null values.
NOLOCK :-
When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it.
Difference between Stored Procedure & Function :-
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
Denormalization :-
A technique to move higher to lower normal forms of database modelling in order to speedup database performance.
SQL Server agent :-
SQL Server Agent is a Microsoft Windows service that allows you to automate some administrative tasks. SQL Server Agent runs jobs, monitors SQL Server, and processes alerts. The SQL Server Agent service must be running before local or multiserver administrative jobs can run automatically.
How can you get @@error and @@rowcount at the same time :-
If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR
@@error variable shows the error number of the last
T-SQL error
@@rowcount variable gives the number of rows affected by
the most recent SQL statements.
RaiseError :-
RaiseError is used to raise the error in sql statement.
Difference between a local and a global variable :-
A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.
Command use to rename the database :-
sp_renamedb ‘oldname’ , ‘newname’
If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode.
sp_configure command :-
Use sp_configure to display or change server-level settings. To change database-level settings, use ALTER DATABASE. To change settings that affect only the current user session, use the SET statement.
Replication :-
Replication is the process of copying/moving data between databases on the same or different servers. replication types,
1. Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values (low latency) are not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.
2. Transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers.
3. Merge replication is the process of distributing data from Publisher to Subscribers, allowing the Publisher and Subscribers to make updates while connected or disconnected, and then merging the updates between sites when they are connected.
Properties of relational tables :-
Relational tables have six properties:
Values are atomic.
Column values are of the same kind.
Each row is unique.
The sequence of columns is insignificant.
The sequence of rows is insignificant.
Each column must have a unique name.
Scheduled Jobs or scheduled tasks :-
Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. Back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.
Know which index a table is using :-
Copy the tables, schema and views from one sql server to another :-
We have to write some DTS packages for it.
Virtual table does a trigger use :-
Inserted and Deleted.
OLTP :-
In OLTP – online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.
Temporary Table vs Table Variable :-
Temporary Tables,
A temporary table is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions. The data in this #temp table (in fact, the table itself) is visible only to the current scope (usually a stored procedure, or a set of nested stored procedures). The table gets cleared up automatically when the current procedure goes out of scope, but you should manually clean up the data when you're done with it:
A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this. Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop or clear the data (which can be a good thing or a bad thing; remember "release early"?). The tempdb transaction log is less impacted than with #temp tables; table variable log activity is truncated immediately, while #temp table log activity persists until the log hits a checkpoint, is manually truncated, or when the server restarts.
Use of COALESCE :-
Coalesce returns the first non-null expression among its arguments. Lets say we have to return a non-null from more than one column, then we can use COALESCE function. SELECT COALESCE(hourly_wage, salary, commission) AS 'Total Salary' FROM wages
In this case,
If hourly_wage is not null and other two columns are null then hourly_wage will be returned.
If hourly_wage, commission are null and salary is not null then salary will be returned.
If commission is non-null and other two columns are null then commission will be returned.
Copy the structure of a table without copying the data :-
Set vs Select :-
Set,
• ANSI standard for variable assignment. Can only assign one variable at a time. Example, SET @Index = 1
• When assigning from a query that returns more than one value, SET will fail with an error.
SET = (SELECT [CustomerID] FROM [dbo].[Customers])
Select,
• Non-ANSI standard when assigning variables. Can assign values to more than one variable at a time.
Example, SELECT @Index = 1, @LoopCount = 10, @InitialValue = 5
• When assigning from a query that returns more than one value, SELECT will assign the last value returned by the query and hide the fact that the query returned more than one row.
SELECT @CustomerID = [CustomerID] FROM [dbo].[Customers]
No error generated
IN Vs Exists :-
IN,
Returns true if a specified value matches any value in a subquery or a list.
Exists,
Returns true if a subquery contains any rows.
EXISTS will be faster because once the engine has found a hit, it will quit looking as the condition has proved true. With IN it will collect all the results from the subquery before further processing. The execution plans are typically going to be identical in these cases, but until you see how the optimizer factors in all the other aspects of indexes etc., you really will never know.
TCP/IP port does SQL Server run on :-
SQL Server runs on port 1433 but we can also change it for better security.
Can we use Truncate command on a table which is referenced by FOREIGN KEY :-
No, We cannot use Truncate command on a table with Foreign Key because of referential integrity.
3 ways you can get an accurate count of the number of records in a table :-
SQL server can return XML document using FOR XML clause. XML documents can be added to SQL Server database and you can use the OPENXML clause to display the data from the document as a relational result set. SQL Server 2000 supports XPath queries.
Concepts of faster differential backups :-
Differential backups are a faster mechanism of taking backups. The back up taken is only of the recently made changes in the data from the last differential backup taken.
Concepts of Parallel Database consistency check (DBCC) :-
DBCC CHECKDB verifies the allocation and structural integrity of all the database objects. It performs physical consistency check on indexed views. It is safe as it identifies and corrects maximum errors. DBCC CHECKDB does not enforce any locks on any tables; however, it does lock the schemas that prevent metadata changes.
It performs the following operations on every table:
• Verifies index and data pages are linked correctly.
• Verifies indexes are as per their sort order.
• Verifies consistency of pointers.
• Verifies that every page has reasonable amount of data.
Example: To checks the data page integrity of the authors table.
dbcc checktable('Department')
Go
Indexed view :-
Indexed views are views which have a unique clustered index. This improves data access performance on complex queries.
Distributed partitioned views :-
For performance gains, one might need to distribute database work over multiple servers. This is achieved by horizontally partitioning large tables over multiple servers. I.e. splitting a table with many rows into many tables with few rows. For data that is spread across multiple servers, different types of statements are needed for retrieving data.
Example,
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
Full-text indexing :-
A full text index has an index of type FULL TEXT. Full text indexing can only done for CHAR, VARCHAR, or TEXT columns.
Types of Database files :-
• Data file: This stores all the data. It has an extension .mdf
• Log file: This stores all the transaction log information. It has an extension .ldf
• Additional data files: Any additional data is stored in these files. It has an extension .ndf
Make views updatable in SQL Server 2000 :-
INSTEAD OF Triggers: These can be created on a view to make a view updateable. The INSTEAD OF trigger is executed instead of the data modification statement on which trigger is defined. This trigger enables the user to provide set of actions that must take place to process data modification statement.
Example: Causes the trigger to fire instead of the update (action)
CREATE TRIGGER Employee_update ON Employee INSTEAD OF
UPDATE AS
{
TRIGGER Definition
}
Partitioned views: It’s a view defined by a UNION ALL of member tables structured in same way but stored separately as multiple tables in either same SQL Server or multiples ones (federated database servers).
Partitioning :-
Partitioning distributes database tables over different database which may reside on a different server. Vertical portioning helps to fit more rows on a database page to improve data access performance. Partitioning helps to do multiple tasks simultaneously. It increases performance. It helps to manage data.
How OPENQUERY function is used to excess external data :-
OPENQUERY is used to execute the specified query on the linked sever. OPENQUERY can be referenced in from the FROM clause just like a table name.
Example:
SELECT * FROM OPENQUERY (OracleSvr, 'SELECT name, id FROM joe.titles')
Drop an assembly from SQL SERVER :-
The DROP ASSEMBLY statement allows removing an assembly along with its associated files from the database.
Syntax:
DROP ASSEMBLY assemblyname[,…n]
[WITH NO DEPENDENTS]
[;]
One cannot use ALTER ASSEMBLY when a SQLCLR method signature that’s currently being used/executed has changed. In such a scenarios we need to drop the Assembly and then re-create it using CREATE ASSEMBLY for updating changes.
To see assemblies loaded in SQL SERVER :-
Select * from sys.assemblies
.NET Appdomain allocated in SQL SERVER 2005 :-
.NET Appdomain allocation depends on the following:
• For every database a different Appdomain is allocated
• For every user a different Appdomain is allocated
Syntax for creating a new assembly in SQL Server 2005 :-
Assemblies can be created using CREATE ASSEMBLY command. It takes the path of the DLL as the parameter.
Example,
CREATE ASSEMBLY SystemWeb
from 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'
with permission_set = unsafe
CLR Integration vs. Extended Stored Procedures :-
CLR Integration provides a more convenient and robust solution to Extended stored procedures for implementing server side logic. Using Common Language Runtime integration allows the results constructed by the function to be queried in SELECT statements by embedding them in the FROM clause. CLR code objects can be written in .NET languages which are safe and fast. CLR code can work with data types like XML, varchar binary.
CLR Integration,
Not supported by older versions of SQL Server
Can be written using any .NET compliant language.
Slower performance
Extended Stored Procedures,
Supported by older versions of SQL Server
Can be only written using c/c++
Faster performance
Configure CLR for SQL Server :-
CLR integration is off by default in SQL Server. We need to set it on using the following statement:
Sp_configure ‘clr enabled’,1
GO
RECONFIGURE
GO
Create functions in SQL Server using .NET :-
Steps to create functions in SQL Server using .NET:
• Create a .NET class using a .NET compliant language such as c#
• Compile the class into a DLL
• Register the DLL in SQL Server using CREATE ASSEMBLY
• Create a function in SQL Server to access the .NET function.
E.g.:
Create .NET Class
public class HelloWorld
{
public string
GetMessage(string name)
{
return "Hello "
+ name;
}
}
Now compile this into HelloWorld.dllRegister DLL in SQL Server
CREATE ASSEMBLY asmHelloWorld FROM 'C:\HelloWorld.dll'
Access this function within an SQL Server Function
CREATE FUNCTION dbo.myHelloWorld
(
@name as nvarchar(200)
)
RETURNS nvarchar(200)
AS EXTERNAL NAME asmHelloWorld.[HelloWorld.HelloWorld].GetMessage
Calling this Function
SELECT dbo.myHelloWorld('John')
SQL Server Identifier :-
Everything in a SQL server can have an identifier. Be it server, view, trigger, column etc. any database object name is an identifier. Identifiers may or may not be required for all objects.
Example:
CREATE TABLE table_name
(ID INT PRIMARY KEY,
Note varchar(20));
Here, ID and Note are two identifiers for the columns. Constraints like Primary key need not have an identifier.RDBMS :-
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables.
Normalization :-
The process of organizing the data to minimize redundancy. A set of rules that have been established to help in the design of table are meant to be connected through relationship.
Normal Forms,
1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF: Eliminate Columns Not Dependent On Key
If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key
BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many relationships.
Stored Procedure :-
It is a group of SQL statement that have been previously created and stored in the server database. It accepts input parameter so that a single procedure can be user over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. e.g. sp_helpdb, sp_renamedb, sp_depends etc.
Advantages,
Stored procedure can reduced network traffic and latency, boosting application performance.
Stored procedures help promote code reuse.
Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
Stored procedures provide better security to your data.
User Defined Function :-
User defined function allow defining its own T-SQL function that can accept 0 or more parameters and return a single value or a table data type.
Types,
Scalar User Defined Function: it returns one of the scalar data types.
Table Value User defined function: it specifies a select statement after the return keyword. This select statement is executed and all its matching rows are returned as the value of the function.
Trigger :-
A trigger is a SQL procedure that initiates an action when an event (Insert, Update or Delete) occurs. A trigger can’t be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table. Trigger is event driven and is attached to a specific table.
Types,
After Trigger: Triggers that run after an update, insert, or delete can be used in several ways:
Triggers can update, insert, or delete data in the same or other tables. This is useful to maintain relationships between data or to keep audit trail information.
Triggers can check data against values of data in the rest of the table or in other tables. This is useful when you cannot use RI constraints or check constraints because of references to data from other rows from this or other tables.
Triggers can use user-defined functions to activate non-database operations. This is useful, for example, for issuing alerts or updating information outside the database.
Working with INSERT Triggers:
CREATE TRIGGER invUpdate ON
[Orders]
FOR INSERT
AS
UPDATE p SET p.instock=[p.instock - i.qty]
FROM products p
JOIN inserted I ON
p.prodid = i.prodid
Working with DELETE Triggers:CREATE TRIGGER DelhiDel ON [Customers]
FOR DELETE
AS
IF (SELECT state FROM deleted) = 'Delhi'
BEGIN
PRINT 'Can not remove customers from Delhi'
PRINT 'Transaction has been canceled'
ROLLBACK
END
Working with UPDATE Triggers:
CREATE TRIGGER CheckStock ON
[Products]
FOR UPDATE
AS
IF (SELECT InStock FROM inserted) < 0
BEGIN
PRINT 'Cannot oversell Products'
PRINT 'Transaction has been cancelled'
ROLLBACK
END
Instead of Triggers: Instead Of Triggers fire instead of the operation that fires the trigger, so if you define an Instead Of trigger on a table for the Delete operation, they try to delete rows, they will not actually get deleted (unless you issue another delete instruction from within the trigger) as in this simple example:
CREATE TABLE Sujit (Name varchar(32))
GO
CREATE TRIGGER tr_Sujit ON
Sujit
INSTEAD OF DELETE
AS
PRINT 'Sorry - you cannot delete this data'
GO
INSERT Sujit
SELECT 'Cannot' union
SELECT 'Delete' union
SELECT 'Me'
GO
DELETE Sujit
GO
SELECT * FROM Sujit
GO
DROP TABLE Sujit
View :-
It’s a virtual table consists of columns from one or more tables. It is a query stored in an object. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table.
Cursor :-
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
In order to work with a cursor we need to perform some steps in the following order:
1. Declare cursor
2. Open cursor
3. Fetch row from the cursor
4. Process fetched row
5. Close cursor
6. Deallocate cursor
Example,
DECLARE @EmpCode char(10)
DECLARE cur1 CURSOR READ_ONLY
FOR
SELECT EmpCode FROM Employee
OPEN cur1
FETCH NEXT FROM cur1
INTO @EmpCode
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @EmpCode
FETCH NEXT FROM cur1
INTO @EmpCode
END
CLOSE cur1
DEALLOCATE cur1
Constraints :-
It’s a property assign to a column or set of column in a table that prevent inconsistent data value from being placed in the column(s). It enforces data integrity.
Different Constraints are,
Primary key: it is a unique identifier for a row within a database table. It can’t allow null value.
Foreign key: a key in one table points to a primary key in another table.
Unique key: this constraints guarantee that the values in a column or set of columns are unique. It accepts a null value.
Check: it uses to limit the values that can be placed in a column.
Not Null: it enforces that the column will not accept null values.
NOLOCK :-
When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it.
Difference between Stored Procedure & Function :-
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
Denormalization :-
A technique to move higher to lower normal forms of database modelling in order to speedup database performance.
SQL Server agent :-
SQL Server Agent is a Microsoft Windows service that allows you to automate some administrative tasks. SQL Server Agent runs jobs, monitors SQL Server, and processes alerts. The SQL Server Agent service must be running before local or multiserver administrative jobs can run automatically.
How can you get @@error and @@rowcount at the same time :-
If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR
@@error variable shows the error number of the last
T-SQL error
@@rowcount variable gives the number of rows affected by
the most recent SQL statements.
RaiseError :-
RaiseError is used to raise the error in sql statement.
BEGIN TRY
DECLARE @MyInt INT;
-- Following
statement will create Devide by Zero Error
SET @MyInt = 1/0;
END TRY
BEGIN CATCH
DECLARE
@ErrorMessage NVARCHAR(4000);
SELECT
@ErrorMessage = ERROR_MESSAGE();
RAISERROR (@ErrorMessage, 16, 1);
END CATCH;
GO
Difference between a local and a global variable :-
A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.
Command use to rename the database :-
sp_renamedb ‘oldname’ , ‘newname’
If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode.
sp_configure command :-
Use sp_configure to display or change server-level settings. To change database-level settings, use ALTER DATABASE. To change settings that affect only the current user session, use the SET statement.
Replication :-
Replication is the process of copying/moving data between databases on the same or different servers. replication types,
1. Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values (low latency) are not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.
2. Transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers.
3. Merge replication is the process of distributing data from Publisher to Subscribers, allowing the Publisher and Subscribers to make updates while connected or disconnected, and then merging the updates between sites when they are connected.
Properties of relational tables :-
Relational tables have six properties:
Values are atomic.
Column values are of the same kind.
Each row is unique.
The sequence of columns is insignificant.
The sequence of rows is insignificant.
Each column must have a unique name.
Scheduled Jobs or scheduled tasks :-
Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. Back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.
Know which index a table is using :-
SELECT table_name,index_name FROM
user_constraints
Copy the tables, schema and views from one sql server to another :-
We have to write some DTS packages for it.
Virtual table does a trigger use :-
Inserted and Deleted.
OLTP :-
In OLTP – online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.
Temporary Table vs Table Variable :-
Temporary Tables,
CREATE TABLE #people
(
id INT,
name VARCHAR(32)
)
A temporary table is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions. The data in this #temp table (in fact, the table itself) is visible only to the current scope (usually a stored procedure, or a set of nested stored procedures). The table gets cleared up automatically when the current procedure goes out of scope, but you should manually clean up the data when you're done with it:
DROP TABLE #people
Table Variables,
DECLARE @people TABLE
(
id INT,
name VARCHAR(32)
)
A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this. Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop or clear the data (which can be a good thing or a bad thing; remember "release early"?). The tempdb transaction log is less impacted than with #temp tables; table variable log activity is truncated immediately, while #temp table log activity persists until the log hits a checkpoint, is manually truncated, or when the server restarts.
Use of COALESCE :-
Coalesce returns the first non-null expression among its arguments. Lets say we have to return a non-null from more than one column, then we can use COALESCE function. SELECT COALESCE(hourly_wage, salary, commission) AS 'Total Salary' FROM wages
In this case,
If hourly_wage is not null and other two columns are null then hourly_wage will be returned.
If hourly_wage, commission are null and salary is not null then salary will be returned.
If commission is non-null and other two columns are null then commission will be returned.
Copy the structure of a table without copying the data :-
select * into Sujit from Employee where 1=2
Set vs Select :-
Set,
• ANSI standard for variable assignment. Can only assign one variable at a time. Example, SET @Index = 1
• When assigning from a query that returns more than one value, SET will fail with an error.
SET = (SELECT [CustomerID] FROM [dbo].[Customers])
Select,
• Non-ANSI standard when assigning variables. Can assign values to more than one variable at a time.
Example, SELECT @Index = 1, @LoopCount = 10, @InitialValue = 5
• When assigning from a query that returns more than one value, SELECT will assign the last value returned by the query and hide the fact that the query returned more than one row.
SELECT @CustomerID = [CustomerID] FROM [dbo].[Customers]
No error generated
IN Vs Exists :-
IN,
Returns true if a specified value matches any value in a subquery or a list.
Exists,
Returns true if a subquery contains any rows.
EXISTS will be faster because once the engine has found a hit, it will quit looking as the condition has proved true. With IN it will collect all the results from the subquery before further processing. The execution plans are typically going to be identical in these cases, but until you see how the optimizer factors in all the other aspects of indexes etc., you really will never know.
TCP/IP port does SQL Server run on :-
SQL Server runs on port 1433 but we can also change it for better security.
Can we use Truncate command on a table which is referenced by FOREIGN KEY :-
No, We cannot use Truncate command on a table with Foreign Key because of referential integrity.
3 ways you can get an accurate count of the number of records in a table :-
SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID('opr_bookingdetails') AND indid < 2
No comments:
Post a Comment