Table Difference in SQL Server

The "tablediff" utility is used to compare the data in two tables for non-convergence. This utility can be used from the command prompt or in a batch file to perform the following tasks:

A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.

Perform a fast comparison by only comparing row counts and schema.

Perform column-level comparisons.

Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.

Log results to an output file or into a table in the destination database.

More info on this check out BOL

DataTypes in SQL Server

Use the below query to find out the data type of all the columns of a particular table .

SELECT syscolumns.name AS ColumnName, systypes.name AS Datatype
FROM sysobjects, syscolumns, systypes
WHERE sysobjects.id = syscolumns.id
AND syscolumns.xtype = systypes.xtype
AND sysobjects.name = 'table1'

In the above just replace the table1 with the your desired table.

Setting a connection string in Web.Config

Setting a connection string in Web.Config



connectionString="Data Source=Servername;
Initial Catalog=dbo;uid=sa;pwd=sa;Pooling=true;Min Pool Size=0;Max Pool Size=1000;"/>

NestLevel in SQL Server Stored Procedures

@@NESTLEVEL
Returns the nesting level of the current stored procedure execution

Each time a stored procedure calls another stored procedure, the nesting level is incremented. When the maximum of 32 is exceeded, the transaction is terminated.

Identity in SQL Server

@@IDENTITY
Returns the last-inserted identity value.

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. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY will return the identity value of the first insert. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.

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

@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope. @@IDENTITY is not limited to a specific scope.

INSERT INTO jobs (job_desc,min_lvl,max_lvl)VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'

Error Numbers in SQL Server

@@ERROR
Returns the error number for the last Transact-SQL statement executed.
When SQL Server completes the execution of a Transact-SQL statement, @@ERROR is set to 0 if the statement executed successfully. If an error occurs, an error message is returned.

@@ERROR returns the number of the error message until another Transact-SQL statement is executed. You can view the text associated with an @@ERROR error number in the sysmessages system table.

Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement validated, or save it to a local variable that can be checked later.

USE pubs
GO
UPDATE authors SET au_id = '12345'
WHERE au_id = "54321"
IF @@ERROR = 547
print "A check constraint violation occurred"