· Differences Between SQL Server 7 And 2000
1) The level of locks required by SQL Server 2000 Database Consistency Checker (DBCC) statements is much lower than in SQL Server 7. SQL Server 2000 DBCC statements can now be run concurrently with data modification statements, significantly lowering their impact on users who are working in the database.
2) A computer can run multiple instances of SQL Server 2000. A computer can run only one instance of SQL Server 7.0 or earlier, although in some cases it can also run multiple instances of SQL Server 2000.
· Up to 16 simultaneous instances of SQL Server can run on a single computer.
· In a File-Server database system, the data is stored in a file. Applications using that database system have direct access to the file for operations. In a client-server database server, the data store still in a file, but application do not have any direct access to the database system. A single program called server handles all the operations for the clients (the applications).
· Online Transaction Processing (OLTP) refers to the insertion, deletion and updating records in a database system. Online Analytical Processing (OLAP) refers to the querying and summaries data. OLTP handles by SQL Server 2000 whereas OLAP is handles by SQL Server Analysis Services.
· Primary Key
A primary key is a piece of unique identifying information that let's you find a particular record within a table. When it takes a combination of columns to do this, the table is said to have a composite primary key.
· Natural Key
When you sure that a values in a column is always be unique, you can use that column as a natural key.
· Synthetic Key
When no column in a table can identify the information uniquely, we develop a column of values for defining key. Like adding an ID column in a table. It also known as surrogate key, because that's not ordinarily part of the table's data.
· Candidate Keys
Any set of columns that could be used as a primary key in a table is referred to as a candidate key.
· Clustered And Non-clustered Indexes
In case of clustered index, records are stored in order physically on the disk. This makes querying the data faster, but makes operations on data slower, because existing records need to be rearranged physically. Non-clustered indexes are vice-versa of the clustered indexes.
· SQL Server uses three types of files to store database on the disk:
1) Primary Data Files (.MDF): If data is small in size you can use only single file to store it.
2) Secondary Data Files (.NDF): If data is large in size you can have a single primary file and multiple secondary file to store it.
3) Transaction Log File (.LDF): SQL Server uses this files to record activities on data and can use it for recovery.
· SQL Server extracts the data to be modified from the data file and places it in memory (RAM) so that user can make changes in the memory. For some intervals or at the same time SQL Server takes all the changes from the memory and writes them to the transaction log, after that it writes them to the data files. This is called write-ahead log.
· Pages are the smallest unit of storage in a SQL Server file. Pages are 8192 bytes each and start off with a 96-bytes header.
· An extent is a collection of eight contiguous pages used to keep the database from becoming fragmented. It can be uniform or mixed.
· Process of designing an optimal organization for your data is normalization. Normalization is mainly for preserving the integrity of the data. It recognizes four types of integrity:
1) Entity Integrity: The basic idea of entity integrity is that you must be able to uniquely identify each entity that you store in a database.
2) Domain Integrity: It enforces restrictions on the information that you store in the database. You can apply check, constraints or any business rule to maintain domain integrity.
3) Referential Integrity: The purpose of referential integrity is to make sure that related rows in a pair of tables stay related even when you make changes to the data.
4) User-Defined Integrity: This encompasses all the other business rules that don't fit neatly into one of integrity described above.
· Normal Forms
1) First
2) Second
3) Third
· An identity column in SQL Server can have duplicate values, that is SQL Server does not imposes Unique constraints implicitly on an identity column.
· Declarative Referential Integrity (DRI)
It is a process that allows you to notify SQL Server of the referential integrity between tables and to have the server automatically enforce these relationships. You can use DRI by 'cascading updates' and 'cascading deletes' clause at the time of creating relationships between tables.
· SQL Server Commonly Used Data Types
| BIT | 1, 0 or Null |
| TINYINT | 0 through 255 |
| SMALLINT | -32,768 through 32,767 |
| INT | +/- 2,147,483,648 |
| VARCHAR | Variable length up to 8000 characters |
| DECIMAL | Decimal (5,3) = 12.345 (Precision = 28) |
| DATETIME | |
| TIMESTAMP | 8-byte binary column, a table can have only one timestamp column. |
| UNIQUEIDENTIFIER | A column define with this data type can store single GUID. GUIDs are generated with NWEID function. |
· Functions
CREATE FUNCTION TwoTimes
(@input INT = 0)
RETURNS INT
AS
BEGIN
RETURN (2 * @input)
END
Now you can use this function like:
SELECT TwoTimes(Quantity) FROM Orders
· OSQL
It is a command line tool to execute SQL statements.
C:\> OSQL –d Northwind –Q "SELECT * FROM Orders" –E
-d Name of database to use.
-Q SQL statements to execute.
-E Use windows integrated security.
QSQL arguments are case-sensitive.
· Like Clause
1) SELECT * FROM Orders WHERE Name LIKE 'St%'
2) SELECT * FROM Orders WHERE Name LIKE '%St%'
3) SELECT * FROM Orders WHERE Name LIKE 'St?'
· Joins
1) Inner Joins: It displays only those records, which matches in all the tables used in join. An Inner Join can also be referred to as an EQUI-Join because it returns an equal number of records from each table in the join.
SELECT sales.qty, stores.stor_name FROM sales
JOIN stores ON sales.stor_id = store.stor_id
2) Outer Joins:
a) Right Joins: If you need to see all the records from the table on the right-most side of the join clause whether or no they have matching record in the left table.
SELECT sales.qty, stores.stor_name FROM sales
RIGHT JOIN stores ON sales.stor_id = stores.stor_ID
b) Left Joins: To see all the records in the left most table whether or not they match records in the right-most table.
c) Full Outer Join: If you need to see all the records from both left and right tables.
You can have multiple join clauses in a single query.
· We cannot use a Where clause with Group By clause. We use 'Having' clause.
· IN Clause
SELECT * FROM Orders WHERE Name IN ('John''s','Robert')
· Rollup And Cube
Rollup clause presents summary records at the top of the query result.
SELECT SUM (Qty) FROM Orders GROUP BY Orders.Name
WITH ROLLUP
Cube clause presents more detailed summary records than rollup. [WITH CUBE]
· Compute And Compute By
Compute works same as rollup but it is not a standard ANSI statement and it gives summary at the bottom of result. Compute works in place of Group By clause.
SELECT sales.qty AS sumqty FROM Orders COMPUTE SUM (sales.qty)
Compute By clause gives results in different result sets.
SELECT sales.qty AS sumsty FROM Orders COMPUTE SUM (sales.qty) BYBy sales.title_Id
· Top N
Top N clause gives N% of the total matching records.
SELECT TOP 5 sales.qty FROM Orders.
Using [TOP N WITH TIES] clause gives those records that are tied with each other.
· Full-Text Searching
Full-Text search in used on the text columns of the table where large amount to data is stored. For example if you want to find a pattern of text in the data. First you have to installed the Full-Text search program because it is not a tool built-in SQL Server. It runs as a service. After installing Full-Text search service, you have to apply (indexing) it on a particular column of a particular table using Full-Text Indexing Wizard on a database. Full-Text indexes are maintained by MS Search Services and stored on the disk as files separated from the database. Full-Text indexes are stored in catalogs in the database. Once you have done with it, you can use Full-Text search using special query. You also need to re-index the Full-Text search if your data on the table is changed. There are two clauses to work with Full-Text search:
1) CONTAINS And CONTAINSTABLE:
SELECT Notes FROM Emp WHERE CONTAINS (Notes,'"French"')
2) FREETEXT And FREETESTTABLE: They are less precise than the first one.
SELECT Notes FROM Emp WHERE FREETEXT (Notes,'"selling peace"')
CONTAINSTABLE and FREETEXTTABLE also perform Full-Text search, but they create a brand-new table with two columns: key and rank. Key column tells you the matching record number and rank tells you how near the search is close.
· Linked Server Queries
Queries, that fetch the records from multiple servers. They are also known as distributed or heterogeneous queries. Two types of linked server queries are:
1) Ad Hoc: These types of queries do not link you to a remote server permanently and do not take up space in your database.
SELECT Access.* FROM OpenRowSet 'Provider_Name',
'Data_Source', 'user_name', 'password', object <i.e. table>
OpenRowSet creates a new temporary table from a foreign database than can be searched by standard Select statement.
2) Permanent: These types of queries link you permanently to the server with sp_addlinkedserver.
sp_addlinkedserver <Server_Name>, <type (i.e. SQL Server)>
Once you are linked with the server, you can execute the query like this:
SELECT * FROM Server_Name.Database_Name.DBO.Table_Name
· DELETE Statement
1) DELETE FROM Authors WHERE au_fname = 'Dean'
2) You can use a sub-query result to delete the records.
DELETE FROM Authors FROM (SELECT TOP 10 * FROM Authors) AS T1 WHERE Authors.au_id = T1.au_id
3) If DELETE statement uses a view, that view must be updateable. That view must be contains only one table in it's FROM clause.
· TRUNCATE Statement
1) TRUNCATE TABLE <Table_Name>
2) TRUNCATE TABLE is an un-logged statement.
3) Using TRUNCATE TABLE on a table, which has an identity column, reset it's identity counter. Use DELETE statement if you do not want to reset identity counter.
4) Deletion made via TRUNCATE TABLE will not activate delete triggers on the table.
5) If a table is part of view and the view is indexed, you can't use TRUNCATE TABLE statement.
· UPDATE Statement
1) UPDATE Titles SET Price = 2.00
2) Set column value to its default value
UPDATE Titles SET Price = DEFAULT
3) Updating local variable as well
DECLARE @UpdatePrice DECIMAL
UDDATE Titles SET @UpdatePrice = Price = 2.00
· INSERT Statement
1) INSERT INTO Orders (Prod, Qty) VALUES ('Tee', 25)
2) INSERT INTO Orders DEFAULT VALUES
3) To insert values in an identity column explicitly first SET IDENTITY_INSERT <Table_Name> ON then executes the UPDATE.
· SELECT INTO Statement
SELECT INTO creates a new table with the records from an existing table.
SELECT Name, Age INTO <New_Table_Name> FROM Authors WHERE Age > 25
If you start the new table name with a # sign, SQL Server creates a temporary table.
· System Global Variables
| @@CONNECIONS | Number of connections made to the server. |
| @@DBTS | Last used timestamp value. |
| @@ERROR | Error number of last T-SQL error (0, in case of no error). |
| @@IDENTITY | Last inserted identity value. |
| @@LANGUAGE | Name of the language currently in use. |
| @@MAX_CONNECTIONS | Maximum number of concurrent connections that can be made to this server. |
| @@ROWCOUNT | Number of rows affected by the most recent SQL statement. |
| @@SERVERNAME | Name of local server. |
You can get value of any global variable described above like:
SELECT @@ERROR
· If a transaction is a distributed transaction (one that affects databases on multiple servers), you can't roll back to a save-point.
· ROLLBACK WORK rolls back all nested transactions and sets @@TRANCOUNT to zero
· SAVE TRANSACTION <Savepoint_Name> saves a transaction at a particular point and stores the reference to that location on a <Savepoint_Name> pointer. You can use ROLLBACK TRANSACTION <Savepoint_Name> to rollback up to that location. Transactions above that location are ready to commit.
· A distributed transaction works exactly the same way as local transaction, but when you commit a distributed transaction, SQL Server done it in two phases (Called two-phase-commit). In first phase it informs all the databases to be ready for commit. If every database signal for OK to commit, than only second phase is starts. In second phase SQL Server tells them to commit the transaction. If one of database is failed to commit his end transaction the SQL Server informs all other databases to rollback their transactions. Distributed transactions are managed by a separate service called MS-DTC.
You start a distributed transaction by BEGIN DISTRIBUTED TRANSACTION statement. Local transactions are automatically converted into distributed transaction if you execute a SQL statement on a remote server in the middle of local transaction.
· OPENQUERY Rowset Function
OPENQUERY statement lets you execute any SQL query (which returns rows) on a linked server and get the result of that query on your local server. OPENQUERY is the easiest tool that you can use to perform distributed queries using SQL Server.
OPENQUERY (<Linked_Server>, 'Query')
SELECT Name From OPENQUERY(<Server_Name>, 'SELECT * FROM Northwind.dbo.Customers') AS Customers JOIN Orders ON
Customers.CustomerID = Orders.CustomerID
· OPENROWSET Rowset Function
It works exactly the same way as OPENQUERY but it also linked you to server via OLE DB (You have to be connected first with the server in case of OPENQUERY). You provide sufficient information like provider name, data source, user id, password etc while using OPENROWSET.
· OPENDATASOURCE Rowset Function
It takes a complete connection string as one of its parameter to link the remote server.
· Cursors
Once a cursor has been opened, you can use the @@CURSOR_ROWS global variable to retrieve the number of rows in this cursor. @@CURSOR_ROWS always refers to the most recently opened cursor. Negative value of @@CURSOR_ROWS indicates that cursor is still populating. For example, -57 indicates that cursor has 57 rows so far and still populating. Dynamic cursors always return –1 value for the @@CURSOR_ROWS. Zero indicates that there isn't an open cursor. A positive number indicates that the cursor is fully populated.
A cursor example:
DELCARE @Name VARCHAR (10)
DELCARE Customer_Cursor CURSOR LOCAL SCROLL STATIC
FOR SELECT Name FROM Orders
OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Customer_Cursor INTO @Name
END
CLOSE Customer_Cursor (Cursor can be reopen with OPEN)
DEALLOCATE Customer_Cursor (Cursor has been destroyed now)
@@FETCH_STATUS global variable contains information on the most recent fetch operation. If the value is zero, the fetch was successful unless there was some problem.
· User-Defined Data-types are always based on a system data type but also have some more properties, rules, and default which can be readily available to use more then one time on various fields, once defined.
· The only difference between rules and constraints is that rules can be bound to a user defined data type, and constraints cannot.
· Default constraints are used to fill in fields that the users leave blank. There are two types of defaults:
Object defaults are defined when you create your table and affect only the column on which they are defined. Definition defaults are created separately from tables and designed to bound to a user defined data type.
· There are two major differences between a primary key constraints and unique constraints. The first is that primary keys are used with foreign keys to enforce referential integrity, and the second is that unique constraints allow null values to be inserted in the field.
· If you do not enable cascading referential integrity in a primary-foreign key relationship between tables, you won't able to update or delete any records in the primary key table if there are matching records in the foreign key table. If you enable the cascading referential integrity, records in the foreign key tables would be automatically updated or deleted as primary key table.
· When a column has mostly unique values, it is said to have high selectivity. When a column has several duplicate values, it is said to have low selectivity. Therefore the primary key field must have high selectivity.
· A table that contains no clustered index is called a heap. When a data is requested to retrieve from the heap SQL Server do it using table scan. A table-scan or retrieving data from the heap can be faster then index if the size of the table is very small (about one extent).
· Clustered Indexes
Clustered indexes physically rearrange the data that users insert in your tables. SQL Server used a binary-tree structure to search with clustered indexes. It starts from the root node and continues it search through intermediate page to leaf node. Actual data is stored in the lead node. You can have only one clustered index per table.
· Non-Clustered Indexes
Non-clustered indexes have the same binary tree structures as clustered indexes with two differences. First, in a non-clustered index leaf node does not contains the actual data; it contains pointer to the data that is stored in the data pages. Second, non-clustered index does not physically rearrange the data. You can have up to 249 non-clustered indexes per table.
· Because of the way SQL Server uses clustered indexes to search for ranges of data, clustered indexes are best created on columns with low selectivity. And because of the way SQL Server searches for data on a non-clustered index, these are best created on columns with high selectivity.
· Microsoft described views as a virtual table or stored SELECT query.
· View Syntax
CREATE VIEW <View_Name> AS <Select_Query>
· Operations On Data Using Views
1) If a view contains more then one table, you can only write statement to update only one table in the view.
2) You cannot modify data in a view that uses a aggregate functions. (Like SUM() or AVG())
3) When a view does not contains all the fields from a table and those fields do not accept null values, you cannot insert values in that view.
· Materialization is the process of performing all of the JOINS and calculations necessary to return a result set to the user. If a view is indexed, result set never need to be materialized.
· If you create an index on a view, every time you update a table used in that view, that view's index also updates at same time. It means the system's overhead would be doubled.
· Deterministic functions always return same values with same argument (i.e. DATEADD). Non-deterministic functions return different values when they are invoked with the same arguments (i.e. GETDATE). All the aggregate and string functions in SQL Server are considered deterministic.
· Restrictions About Indexed Views
1) The view cannot reference other views, only tables.
2) The view must be created with SCHEMABINDING option.
3) All the tables used in the view must be in same database and have same DB owner.
4) View can contains only deterministic.
5) Column names must be stated in the SELECT statement. You cannot use a * for referring all the columns.
6) Sub-queries and outer or self-JOIN cannot be used.
· Syntax For Indexing A View
CREATE UNIQUE CLUSTERED INDEX <Index_Name> ON
<View_Name>(<Column_Name)
· Distributed Partitioned Views
DPV make tables on multiple servers look like one table. When you split your large table into multiple tables and put them on different server, the tables would then be referred to as member tables and the databases that contain them are called member databases. All of the servers that participate are called a federation of servers.
· Advantages Of Stored Procedures
1) Stored procedures lessen the network traffic, because client sends short request strings to server as compare to long ad hoc query string.
2) Stored procedures are pre-compiled as compare to ad hoc queries, which should be compiled every time they executes. This improves performance.
3) Stored procedure makes database management easy. For example in case you have to change an ad hoc query. If every client has that ad hoc query, you have to make changes on every client machine. In case of stored procedure you just have to make changes on server only.
4) Stored procedures are more secure then client side query. They reside on server and can be encrypted, so any penetration or adulteration is very hard.
· Stored Procedure Syntax
CREATE PROCEDURE <SP_Name> <Input-Output-Parameters> AS
<Select_Query>
CREATE PROCEDURE My_Sp
@Search_ID INT,
@Found_Name VARCHAR OUTPUT
AS
SELECT @Found_Name = Name FROM Student WHERE ID = @Search_ID
Now you can call this stored procedure as:
DECLARE @Get_Name VARCHAR
EXEC My_Sp 1, @Get_Name OUTPUT
SELECT @Get_Name
· When you asked SQL Server to run a query, it first scan and analysis the query and prepare an execution plan for the query. It stores that plan in a special area of RAM. Now, whenever you run the same or almost similar query again SQL Server does not create the execution plan again. It just fetches the plan from the RAM and executes your query.
· If you make any changes to a stored procedure or in the table schema used in that stored procedure, SQL Server automatically recompile the stored procedure. The only time the stored procedure will not recompiled automatically is if you create a new index; you have to do it manually.
· If you use WITH RECOMPILE clause when creating a stored procedure, that stored procedure recompile every time you executes it. Another way to force the recompilation is you execute the stored procedure using WITH RECOMPILE. Like:
EXEC <Sp_Name> WITH RECOMPILE
· Use WITH ENCRYPTION clause to create an encrypted stored procedure. Once you created a procedure using encryption option you cannot able to modify that procedure again.
· Extended Stored Procedures
Extended stored procedures extend the capabilities of SQL Server so that it can do things that a database server would not ordinarily be capable of doing. Extended stored procedures are just C++ code saved in and executed from a DLL.
· A trigger is a collection of SQL statements that looks and acts a great deal like a stored procedure. The only difference between the two is that a trigger cannot be called with the EXEC command; triggers are activated or fired when a user tries to insert, update or delete data.
· When a user tries to insert a new record into a table, SQL Server copies the new record into a table in the database called trigger table and a special table stored in memory called the inserted table. You can use inserted table when you want to apply some changes to other table base on inserted values.
· Creating An Insert Trigger
The following trigger updates Products table as well with quantity of Orders table being inserted.
CREATE TRIGGER My_Trigger ON [Orders] FOR INSERT
AS
Update P SET P.InStock = (P.InStock – I.Qty)
FROM Products P JOIN Inserted I ON P.ID = I.ID
· Creating An Delete Trigger
With a delete trigger in place SQL Server moves the record being deleted to a logical table in memory called deleted. You can refer to that table if you want to perform the operation on deleted data. The deleted table automatically purged of records after a transaction in successfully completed.
This delete trigger is designed to keep your users from deleting customers who live in Arizona.
CREATE TRIGGER My_Trigger ON [Customers] FOR DELETE
AS
IF (SELECT State FROM Deleted) = 'AZ'
BEGIN
PRINT 'Cannot remove customer from AZ'
ROLLBACK
END
· Creating Update Triggers
Update triggers uses a combination of methods used in insert and delete triggers. It uses both inserted and deleted logical table to perform the operation. First the data to be modified are place into the deleted table and the new data is placed into inserted table. So if want to have some operations on new values, you can use that inserted table.
Here we are creating an update trigger, which restrict the stock that is less then zero:
CREATE TRIGGER My_Trigger ON [Products] FOR UPDATE AS
IF (SELECT InStock FROM Inserted) < 0
BEGIN
PRINT 'Negative values'
ROLLBACK
END
You can design an update trigger, which fires only when a particular column is going to update. For example, if you do not want to update a particular column in a table:
CREATE TRIGGER My_Trigger ON [Info] For UPDATE AS
IF UPDATE(Phone)
BEGIN
PRINT 'Cannot update this column'
ROLLBACK
END
· We can declare the triggers on view also.
· INSTEAD OF Triggers
When you try to insert values through a view, and if that view does not contain all the required fields of the table, your operation would be failed. You can use a instead of trigger for the situation like this. For example, the view used in following trigger does not contain a city column, so if you try to insert values through you will get an error. So defined the following instead of trigger to overcome the problem:
CREATE TRIGGER My_Trigger ON My_View INSTEAD OF INSERT AS
DECLARE
@Name VARCHAR(10),
@AGE INT,
@CITY VARCHAR(10)
SET @CITY = 'Phoenix'
SET @Name = (SELECT Name FROM INSERTED)
SET @AGE = (SELECT Age FROM INSERTED)
INSERT INTO Customers Values (@Name, @Age, @City)
· You can combine more then one type of triggers into one trigger.
· Raising An Error
You can raise your customized error when a trigger is violated. The syntax is:
RAISERROR (<Message>, <Severity>, <state>)
<Message> is your customized message. <Severity> told how severe the problem is. <State> defined the location of the error in a trigger. For example, if the error is raised at the top, you can defined state as 1, if the error is raised at the middle of the trigger you can defined state as 2 and so on.
· Recursive Triggers
Those triggers that update other tables can cause triggers on the other tables to fire. This is called a recursive triggers. All of the triggers together considered one big transaction. A ROLLBACK command anywhere, in any of the triggers, will cancel all of the data input. Triggers can be recursive up to only 16 levels. After 16th level firing a 17th triggers would be like a ROLLBACK command. In SQL Server, recursive triggers are disabled by default.
· For types of backup available in SQL Server:
1) Database – Complete:
It is a full backup. You have to take full backup when first time you backup a database.
2) Database – Differential:
It is designed to backup all the changes made to a database since the last full backup was performed.
3) Transaction Log:
This backup only transaction logs. After backing up, transaction logs are truncated (emptied).
4) File And File-Group:
This backup is performed on the databases scattered on the multiple servers into different files.
· Parallel Striped Backups
To speed up things, you can perform backups to multiple devices at the same time. This type of backup called parallel striped backups.
· Following type of restores available in SQL Server:
1) Standard Restores:
These are normal restores. When a database crashed this type of restore recover it completely.
2) Point-In-Time Restores:
Using this type of restore you can restore your data up to a specified time.
3) Partial Restores:
This type of restore is used to restore a single file-group at a time into a different database.
· Maintaining Indexes
In case of clustered index when an indexed entry page becomes full, SQL Server split that page into two pages to make room for new entries. In one page it keeps half of the entries and in another page it keeps half of the entries. That newly generated page may be anywhere in the page sequence. This is known as fragmentation. In this situation SQL Server cannot go from one page to next page in order. It has to jump from one page to another for retrieving entries.
This affects the index performance.
DBCC (Database Consistency Checker) SHOWCONTIG tells you whether you need to reconstruct an index or not (When it is fragmented or damaged).
You can rebuild indexes using CREATE INDEX statement with the DROP_EXISTING option. Second way to rebuild an index that is being used as a primary key is to use
DBCC DBREINDEX.
· Operators
Several settings need to be configured for SQL Server to be able to contact you when there are problems. Such settings include whom to contact, when they are available, how those people should be contacted, and of what problems should they be alerted. An operator is the object used in SQL Server to configure all of these settings.
· Jobs
A job is a series of tasks that can be automated to run whenever you need them to (i.e. backups). A multi-server job is a job that is created once, on one server, and downloads to other servers over the network, where the job is run.
· Alerts
An alert is fired when an event (usually a problem) occurs on SQL Server (i.e. a full transaction log). When an alert is fired, it notifies the operator defined in the alert.
· Roles
In SQL Server, when several users need permission to access a database, it is much easier to give them all permissions as a group rather than trying to manage each user separately. There are three types of roles in the SQL Server: Fixed, Custom and Application.
· SQL-NS
SQL-NS or SQL-Name-Space is a COM object library for the user-interface portion of SQL Server. You can use this name-space in your application to access all the dialog boxes found in SQL Server.
· Data Transformation Services (DTS)
DTS is a tool for moving or transforming data from a variety of OLE DB data source. For example, you can import data from an Access server to SQL Server or you can export the data from SQL Server to an Oracle server through DTS.
· Locking refers to the ability of the database server to reserve resources such as rows of data or pages of an index for the use of one particular user at a time. Locking solves following problems:
1) Lost Updates:
Lost updates can happen anytime two independent transactions select the same row in a table and then update it based on the data that they originally selected.
2) Uncommitted Dependencies:
Uncommitted dependencies are sometimes called dirty reads. This problem happens when a record is read while it's still being updated, but before the updates are final.
3) Inconsistent Analysis:
It happens when data is being read by one process (server process) while the data is being written by another process.
4) Phantom Reads:
These occur when an application thinks it has a stable set of data, but other applications are inserting rows into the data.
· Concurrency Control Methods
1) Optimistic Locking:
With this method, resources (for example, a row in a table) are locked only while a change is about to be saved. In optimistic locking, it is possible that another user will make a change in the selected resource before you can.
2) Pessimistic Locking:
With this method, resources are locked when they are required and are kept locked throughout a transaction.
· The default isolation level for SQL Server transactions is Read Committed.
· Dynamic Locking
SQL Server locking is dynamic. What this means to you as an application developer is that you almost never have to worry about locking. As part of generating the execution plan for a query, SQL Server will determine the type of locks to place when that query is executed.
· Replication
Replication is designed specifically for the task of copying data and other objects between servers and making certain that those copies stay up-to-date.
Replication has three parts in SQL Server:
1) Publisher: It is the server with the original copy of the data that others need.
2) Distributor: SQL Server need special servers called distributors to collect data from publishers to distribute to subscribers.
3) Subscriber: A subscriber is a server that requires a copy of the data that is stored on the publisher.
Comments