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.
Statements such as USE, SET
<option>, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT
TRANSACTION reset the ROWCOUNT value to 0.
The following example executes an UPDATE
statement and uses @@ROWCOUNT to detect if any rows were changed.
USE AdventureWorks;
GO
UPDATE HumanResources.Employee
SET Title = N'Executive'
WHERE NationalIDNumber = 123456789
IF @@ROWCOUNT = 0
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.
ROLLBACK TRANSACTION decrements
@@TRANCOUNT to 0, except for ROLLBACK TRANSACTION
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;
GO
BEGIN TRANSACTION;
GO
UPDATE Person.Contact
SET LastName = UPPER(LastName)
WHERE LastName = 'Wood';
GO
IF @@TRANCOUNT > 0
BEGIN
PRINT N'A transaction needs to be rolled
back.';
ROLLBACK TRANSACTION;
END
@@ERROR (Transact-SQL):
*Returns
the error number for the last Transact-SQL statement executed.
Return type of @@TRANCOUNT function is a int
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;
GO
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.Syntax
COLLATE {< collation_name > | database_default}< collation_name > :: = { Windows_collation_name } | { SQL_collation_name
}Remarks
The COLLATE clause can be specified at several levels, including the following:- 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.
- 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.
- 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:
SELECT *
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 |
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 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';
GO
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.
ALTER LOGIN Victoria WITH
PASSWORD =
'V1cteAmanti55imE'
OLD_PASSWORD =
'B3r1000d#2-36';
GO
sp_change_users_login (Transact-SQL)
Maps an existing database user to a
SQL Server login.
| Note: |
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.
Value
|
Description
|
Auto_Fix
|
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.
|
Report
|
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.
|
Update_One
|
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.
| Security
Note: |
Use a strong password. For more
information, see Strong
Passwords.
|
0 (success) or 1 (failure)
Column
name
|
Data
type
|
Description
|
UserName
|
sysname
|
Database user name.
|
UserSID
|
varbinary(85)
|
User's security identifier.
|
| Note:
|
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';
GO
--Map database user MB-Sales to login MaryB.
USE AdventureWorks;
GO
EXEC sp_change_users_login 'Update_One', 'MB-Sales', 'MaryB';
GO
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;
GO
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL,
'B3r12-3x$098f6';
GO
“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>
GO
dbcc
opentran
Go
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
sp_replflush
--Reset LogReader to retrieve next transactions
sp_replrestart
--Start LogReader and begin Replicating transactions
Products:
Orders:
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]
AFTER
INSERT
AS
BEGIN
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)
rollback
else
update Products set
stock_available= stock_available- @quantity where
product_id=@product_id
END
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:- Orders:
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.
Products:
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.
- “Insert” made on the table Orders .
- “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).
Orders:
Products:
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]
AFTER
INSERT
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
http://msdn.microsoft.com/en-us/library/ms152529.aspx
No comments:
Post a Comment