Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Monday, 1 April 2013

SQL DBA Topics

SQL DBA Topics
@@IDENTITY - Identity Function:
*returns  the last identity value generated in any table in the current session. 

Return type of @@IDENTITY function is a numeric.

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.

After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement. 

If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. 

If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. 

The scope of the @@IDENTITY function is the local server on which it is executed. This function cannot be applied to remote or linked servers. 

@@ROWCOUNT (Transact-SQL):
*Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.
Return type of @@ROWCOUNTfunction is a int

Transact-SQL statements can set the value in @@ROWCOUNT in the following ways:
  • Set @@ROWCOUNT to the number of rows affected or read. Rows may or may not be sent to the client.
  • Preserve @@ROWCOUNT from the previous statement execution.
  • Reset @@ROWCOUNT to 0 but do not return the value to the client.
Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT 'Generic Text'.
Statements that make an assignment in a query or use RETURN in a query set the @@ROWCOUNT value to the number of rows affected or read by the query, for example: SELECT @local_variable = c1 FROM t1.
Data manipulation language (DML) statements set the @@ROWCOUNT value to the number of rows affected by the query and return that value to the client. The DML statements may not send any rows to the client.
DECLARE CURSOR and FETCH set the @@ROWCOUNT value to 1.
EXECUTE statements preserve the previous @@ROWCOUNT.

The following example executes an UPDATE statement and uses @@ROWCOUNT to detect if any rows were changed.
USE AdventureWorks;
UPDATE HumanResources.Employee
SET Title = N'Executive'
WHERE NationalIDNumber = 123456789
PRINT 'Warning: No rows were updated';
@@TRANCOUNT (Transact-SQL):
*Returns the number of active transactions for the current connection.
Return type of @@TRANCOUNT  function is a int

The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1.


savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK

decrement @@TRANCOUNT by 1

This example uses @@TRANCOUNT to test for open transactions that should be committed.
USE AdventureWorks;
UPDATE Person.Contact
    SET LastName = UPPER(LastName)
    WHERE LastName = 'Wood';
    PRINT N'A transaction needs to be rolled back.';
@@ERROR (Transact-SQL):
*Returns the error number for the last Transact-SQL statement executed.
Return type of @@TRANCOUNT  function is a int

Returns 0 if the previous Transact-SQL statement encountered no errors.
Returns an error number if the previous statement encountered an error. If the error was one of the errors in the sys.messages catalog view, then @@ERROR contains the value from the sys.messages.message_id column for that error. You can view the text associated with an @@ERROR error number in sys.messages.
Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement being verified, or save it to a local variable that can be checked later.
The SQL Server 2005 Database Engine introduces the TRY...CATCH construct to handle errors. The TRY...CATCH construct also supports additional system functions (ERROR_LINE, ERROR_MESSAGE, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE) that return more error information than @@ERROR. TRY...CATCH also supports an ERROR_NUMBER function that is not limited to returning the error number in the statement immediately after the statement that generated an error. For more information, see TRY...CATCH (Transact-SQL).

Use @@ERROR to detect a specific error
This example uses @@ERROR to check for a check constraint violation (error #547) in an UPDATE statement.
USE AdventureWorks;
UPDATE HumanResources.Employee
    SET PayFrequency = 4
    WHERE NationalIDNumber = 615389812;
IF @@ERROR = 547
    PRINT N'A check constraint violation occurred.';

Difference between a clustered and a non-clustered index?How many can we have?
A clustered index is the logical order of the data in the table. Since the cluster index stores the actual data in the table there can only be a one clustered index per table. Think of the white pages of a phone book. That data is sorted ASC by last name and each page is numbered. For Non-Clustered indexes think of an index at the back of a text book. The index is stored in the back of the book but points to the data elsewhere in the book. Typically the clustered index however if no clustered index exists then it points to a row identifier and the data may not be in any logical order. A Row ID (RID) is comprised of the file number, page number, and slot number of the row. Also you should know that a table without a clustered index is referred to as HEAP table. As for the number of non clustered indexes it depends on the version of SQL Server. In my opinion neither of these two limits should ever be hit but for educational purposes one should know them.

Non-Clustered Index
SQL 2000/2005 = 249, SQL 2008 = 999
Clustered Index
Only 1 (for all SQL 2000/2005/2008/2012)
 I should also mention that there are other indexes you need to be familiar with as well. You can have covering indexes starting with SQL 2005. Basically these are Indexes with Included Columns. Think of them as composite indexes without taking up as much space
What is a collation?
The COLLATE clause can be applied only for the char, varchar, text, nchar, nvarchar, and ntext data types.
COLLATE {< collation_name > | database_default}
< collation_name > :: =
    { Windows_collation_name } | { SQL_collation_name }

The COLLATE clause can be specified at several levels, including the following:
  1. Creating or altering a database.
You can use the COLLATE clause of the CREATE DATABASE or ALTER DATABASE statement to specify the default collation of the database. You can also specify a collation when you create a database using SQL Server Enterprise Manager. If you do not specify a collation, the database is assigned the default collation of the SQL Server instance.
  1. Creating or altering a table column.
You can specify collations for each character string column using the COLLATE clause of the CREATE TABLE or ALTER TABLE statement. You can also specify a collation when you create a table using SQL Server Enterprise Manager. If you do not specify a collation, the column is assigned the default collation of the database.
You can also use the database_default option in the COLLATE clause to specify that a column in a temporary table use the collation default of the current user database for the connection instead of tempdb.
  1. Casting the collation of an expression.
You can use the COLLATE clause to cast a character expression to a certain collation. Character literals and variables are assigned the default collation of the current database. Column references are assigned the definition collation of the column.  For the collation of an expression, see Collation Precedence.
The COLLATE clause can be applied only for the char, varchar, text, nchar, nvarchar, and ntext data types.
Collations are generally identified by a collation name. The exception is in Setup where you do not specify a collation name for Windows collations, but instead specify the collation designator, and then select check boxes to specify binary sorting or dictionary sorting that is either sensitive or insensitive to either case or accents.
You can execute the system function fn_helpcollations to retrieve a list of all the valid collation names for Windows collations and SQL collations:
FROM ::fn_helpcollations()
SQL Server can support only code pages that are supported by the underlying operating system. When you perform an action that depends on collations, the SQL Server collation used by the referenced object must use a code page supported by the operating system running on the computer. These actions can include:
  • Specifying a default collation for a database when you create or alter the database.
  • Specifying a collation for a column when creating or altering a table.
  • When restoring or attaching a database, the default collation of the database and the collation of any char, varchar, and text columns or parameters in the database must be supported by the operating system.
Code page translations are supported for char and varchar data types, but not for text data type. Data loss during code page translations is not reported.
If the collation specified or the collation used by the referenced object, uses a code page not supported by Windows®, SQL Server issues error. For more information, see the Collations section in the SQL Server Architecture chapter of the SQL Server Books Online.

sp_password (Transact-SQL)

Adds or changes a password for a Microsoft SQL Server login.

Important noteImportant
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER LOGIN instead.


sp_password [ [ @old = ] 'old_password' , ]
     { [ @new =] 'new_password' }
     [ , [ @loginame = ] 'login' ]

[ @old= ] 'old_password'
Is the old password. old_password is sysname, with a default of NULL.
[ @new= ] 'new_password'
Is the new password. new_password is sysname, with no default. old_password must be specified if named parameters are not used.
Security noteSecurity Note
Do not use a NULL password. Use a strong password. For more information, see Strong Passwords.
[ @loginame= ] 'login'
Is the name of the login affected by the password change. login is sysname, with a default of NULL. login must already exist and can be specified only by members of the sysadmin or securityadmin fixed server roles.

0 (success) or 1 (failure)

sp_password calls ALTER LOGIN. This statement supports additional options. For information on changing passwords, see ALTER LOGIN (Transact-SQL).
sp_password cannot be executed within a user-defined transaction.

Requires ALTER ANY LOGIN permission. Also requires CONTROL SERVER permission to reset a password without supplying the old password, or if the login that is being changed has CONTROL SERVER permission.
A principal can change its own password.

A. Changing the password of a login without knowing the old password
The following example shows how to use ALTER LOGIN to change the password for the login Victoria to B3r1000d#2-36. This is the preferred method. The user that is executing this command must have CONTROL SERVER permission.
ALTER LOGIN Victoria WITH PASSWORD = 'B3r1000d#2-36';
B. Changing a password
The following example shows how to use ALTER LOGIN to change the password for the login Victoria from B3r1000d#2-36 to V1cteAmanti55imE. This is the preferred method. User Victoria can execute this command without additional permissions. Other users require ALTER ANY LOGIN permission.
     PASSWORD = 'V1cteAmanti55imE'
     OLD_PASSWORD = 'B3r1000d#2-36';

sp_change_users_login (Transact-SQL)
Maps an existing database user to a SQL Server login.
sp_change_users_login cannot be used with a SQL Server login created from a Windows principal or with a user created by using CREATE USER WITHOUT LOGIN.

sp_change_users_login [ @Action = ] 'action'
    [ , [ @UserNamePattern = ] 'user' ]
    [ , [ @LoginName = ] 'login' ]
        [ , [ @Password = ] 'password' ]

[ @Action= ] 'action'
Describes the action to be performed by the procedure. action is varchar(10). action can have one of the following values.
Links a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name. If a login with the same name does not exist, one will be created. Examine the result from the Auto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.
When you use Auto_Fix, you must specify user and password if the login does not already exist, otherwise you must specify user but password will be ignored. login must be NULL. user must be a valid user in the current database. The login cannot have another user mapped to it.
Lists the users and corresponding security identifiers (SID) in the current database that are not linked to any login. user, login, and password must be NULL or not specified.
Links the specified user in the current database to an existing SQL Server login. user and login must be specified. password must be NULL or not specified.
[ @UserNamePattern= ] 'user'
Is the name of a user in the current database. user is sysname, with a default of NULL.
[ @LoginName= ] 'login'
Is the name of a SQL Server login. login is sysname, with a default of NULL.
[ @Password= ] 'password'
Is the password assigned to a new SQL Server login that is created by specifying Auto_Fix. If a matching login already exists, the user and login are mapped and password is ignored. If a matching login does not exist, sp_change_users_login creates a new SQL Server login and assigns password as the password for the new login. password is sysname, and must not be NULL.,SQL.90).gifSecurity Note:
Use a strong password. For more information, see Strong Passwords.

0 (success) or 1 (failure)

Column name
Data type
Database user name.
User's security identifier.

sp_change_users_login cannot be used to map database users to Windows-level principals, certificates, or asymmetric keys.
Use sp_change_users_login to link a database user in the current database with a SQL Server login. If the login for a user has changed, use sp_change_users_login to link the user to the new login without losing user permissions. The new login cannot be sa, and the usercannot be dbo, guest, or an INFORMATION_SCHEMA user.
sp_change_users_login cannot be executed within a user-defined transaction.

Requires membership in the db_owner fixed database role. Only members of the sysadmin fixed server role can specify the Auto_Fix option.

A. Showing a report of the current user to login mappings
The following example produces a report of the users in the current database that are not linked to any login and their security identifiers (SIDs).
EXEC sp_change_users_login 'Report';
B. Mapping a database user to a new SQL Server login
In the following example, a database user is associated with a new SQL Server login. Database user MB-Sales, which at first is mapped to another login, is remapped to login MaryB.
--Create the new login.
CREATE LOGIN MaryB WITH PASSWORD = '982734snfdHHkjj3';
--Map database user MB-Sales to login MaryB.
USE AdventureWorks;
EXEC sp_change_users_login 'Update_One', 'MB-Sales', 'MaryB';
C. Automatically mapping a user to a login, creating a new login if it is required
The following example shows how to use Auto_Fix to map an existing user to a login of the same name, or to create the SQL Server login Mary that has the password B3r12-3x$098f6 if the login Mary does not exist.
USE AdventureWorks;
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-3x$098f6';

 “Not for Replication” command in Table usage:

 “Not for Replication” is a property which can be set for different objects like Check constraints, Foreign Key constraints, Triggers , Identity columns etc while using SQL Server Replication. Feature used when the DBA would like the transactions to behave differently when the changes are being made by the Replication Agents as compared to changes coming from a normal user transaction.
Let’s take a simple example
A company has various sales stores in different parts of the country, each sale store takes an order from the end user and replicates the request to the main production and dispatch unit of the company.
The main production and dispatch unit Server is acting as the Publisher whereas all the sale stores are acting as Subscribers in Merge Replication. Every Sale Store needs to have the information about the current stock level of a product and should not accept any new orders if the product is out of stock at the main production unit.
The tables participating in the Replication topology are as follows, with Products being the parent table and joined with PK-FK constraints with the Orders table on “Product_id” Column. The Product table also has another column named as “Stock_Available” which keeps track of the current available stock for the particular product.

Using sp_repldone to SKIP a transaction:

The steps in this posting covered advanced topics is SQL Server Transactional Replication.  If you are unsure on how or when to use these steps, DON’T, instead contact Microsoft SQL Server Support or post your problem on the TechNet forum/newsgroup.  These steps will result in data loss, use at your own discretion.
The action taken in steps below will SKIP a pending transaction.  If that transaction is a DELETE, then the DELETE will not be pushed to the Subscriber.  If you then UPDATE the Publisher, when that UPDATE is pushed to the Subscriber it will fail with a “row not found”. At this point you can either SKIPERRORS, or reinitialize the Subscriber by pushing down a new snapshot.
--Steps to have Transactional Replication LogReader
--    "skip" the next pending transaction

--WARNING: Use at your own risk as these steps can lead to
--    Subscribers out-of-sync with Publisher resulting in
--  Distribution Agent failing with "row not found",
--  necessitating a Publication reinitialize.

--Find "next" pending transaction
Use <published database>
dbcc opentran

     Replicated Transaction Information:
        Oldest distributed LSN     : (86:6614:4)
        Oldest non-distributed LSN : (86:6617:1) <-- 0x00000056000019D90001

--Retrieve from the Transaction log the BeginTran and CommitTran LSN values
--For large transactions, TOP 10 value may need to be increased.      
select TOP 10 [Current LSN], [Operation], [Transaction ID] from fn_dblog('0x00000056:000019D9:0001', null)

--Look for LOP_COMMIT_XACT for the "Transaction ID" matching the LOP_BEGIN_XACT
Current LSN             Operation                       Transaction ID
----------------------- ------------------------------- --------------
00000056:000019d9:0001  LOP_BEGIN_XACT                  0000:000200fe<--Begin Transaction
00000056:000019d9:0002  LOP_DELETE_ROWS                 0000:000200fe
00000056:000019d9:0003  LOP_INSERT_ROWS                 0000:000200fe
00000056:000019d9:0004  LOP_COMMIT_XACT                 0000:000200fe<--Matching Commit Tran
00000056:000019dc:0001  LOP_BEGIN_XACT                  0000:000200ff
00000056:000019dc:0002  LOP_MARK_SAVEPOINT              0000:000200ff
00000056:000019dc:0003  LOP_REPL_NOOP                   0000:000200ff
00000056:000019dc:0004  LOP_PREP_XACT                   0000:000200ff

--Tell Replication that BeginTran/CommitTran pair are already REPLICATED
--Note the format change to the LSN values, ":" are not used in sp_repldone
sp_repldone @xactid = 0x00000056000019D90001, @xact_segno = 0x00000056000019D90004

--Verify that the “skipped” transaction is now "Oldest distributed LSN"
dbcc opentran
     Replicated Transaction Information:
              Oldest distributed LSN     : (86:6617:4)  <-- 00000056:000019d9:0004
              Oldest non-distributed LSN : (86:6689:1)
--Release article cache      

--Reset LogReader to retrieve next transactions

--Start LogReader and begin Replicating transactions




In Addition to this there is also an AFTER INSERT Trigger (named as “Update_Stock”) on the Orders Table which checks if we have sufficient stock available for the product_id, if yes, it updates the Products table by subtracting the Quantity ordered from the Available Stock.
Sample Trigger:
create TRIGGER [dbo].[Update_Stock]
   ON  [dbo].[Orders]
declare @quantity as int
declare @product_id as int
declare @stock_available as int
select @product_id= product_id , @quantity = quantity from Inserted
select @stock_available= stock_available from Products where product_id=@product_id
if (@Quantity > @stock_available)
      update Products set stock_available= stock_available- @quantity where product_id=@product_id
Now let’s assume that a new order is to be placed for the product “Baseball Bats” (Product_id=88) at one of the subscriber servers , let’s call it “Subscriber 1”, after the insert the data at the “Subscriber 1” will look like as below:-


This insert (3rd row) will fire the insert trigger and will deduct 20 (100 – 20 =80 )from the “Stock_Available” column in the Products table for Product_id=88.

After this transaction at “Subscriber 1” , the “Subscriber 1” synchronizes with the Publisher. Now Replication detects that there has been in total two transactions made on “Subscriber 1” which needs to be uploaded to the main Publisher Server.
  1. “Insert” made on the table Orders .
  2. “Update” made for the table Products for the column “Stock_Available”. (Replication does not care if the update came from a trigger, for Replication it’s another normal update transaction made on the Products table).
After the Sync completes , we expect the data from “Subscriber 1” to get merged with the main Publisher and both the tables should look identical, but this what you will get on the Publisher after the first sync completes.




Oops, what happened there !!! The “Stock_Available” column at Publisher should have been set to “80” and not “60”.
Let me explain
Products is the parent table and Orders is the child table, now by default Merge processing order will replicate the transactions for parent table first followed by child table. As said earlier the update made by the insert trigger is also considered as a normal user transaction , Products table being the parent table, this update transaction flows to the Publisher first which sets the “Stock_Available” column to “80”. Now the following transaction, Insert on Orders table, is also propagated to the Publisher which further kicks off the “AFTER INSERT” trigger present on the Orders table at the Publisher and this trigger goes back to Products table and updates again the “Stock_Available” column value to Current value- Ordered Quantity (80-20=60).
You guessed it right, if you make another sync followed by the first sync without making any further changes, both the Publisher and Subscriber will have the same but wrong value set for “Stock_Available” column (i.e. 60).  
Why do we need the “AFTER INSERT” trigger at the Publisher? What if orders can be entered directly at the production server (Publisher) in addition to dispatch unit servers (Subscribers)? The trigger at the Publisher would be required to correctly decrement the Products table.
How to avoid this situation
This is where the “Not for Replication” property comes into picture. Our intention is that if the INSERT is coming from the replication Merge Agent, it does not need to fire the trigger , if the INSERT is coming from a normal user connection, the trigger should get fired to update the available stock level. We can achieve this by marking the trigger as “Not for Replication” at all the replicas.
You can do this by changing the trigger definition as below:
ALTER TRIGGER [dbo].[Update_Stock]  
   ON  [dbo].[Orders]
NOT for Replication -- ‘marked as not for replication’
Side Note
In the above scenario if Orders Table was the Parent table and Products the child table, you will *not* run into this issue. I think by now you know why. Try it!
The same “Not for Replication” setting applies for Check constraints, Foreign Key constraints and Identity columns on the Publisher and/or the Subscriber. For example, you may want to set a subscriber’s Check Constraint as “Not for Replication” to prevent Replication Agent from being restricted in what data it can replicated from the Publisher.  You can read more about NFR (“Not for Replication”) from the below link from SQL Books Online:-
Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION