Many of the connection-level options configure ANSI (American National Standards Institutes) compatibility or specific connection-performance options. To view current settings of connection-level options, query the sys.dm_exec_connections dynamic management view. -SET ANSI_NULLS: -SET CURSOR_CLOSE_ON_COMMIT: -SET ANSI_NULL_DFLT_ON: -SET IMPLICIT_TRANSACTIONS: -SET ANSI_PADDING: -SET QUOTED_IDENTIFIER: -SET ANSI_WARNINGS: Connection properties can also be checked by means of the SessionProperty() function: Select SESSIONPROPERTY …Continue reading "MSSQL The ANSI settings"
Most database options can be set in Management Studio within the Database Properties page, which you can access by right-clicking a database in the console tree and choosing Properties from the context menu. The database configuration options can be set using T-SQL ALTER DATABASE SET options as: ALTER DATABASE AdventureWorks2008 SET SINGLE_USER; To view database configuration options using …Continue reading "MSSQL Configuring the Database"
This will control server-wide settings, such as how SQL Server interacts with hardware, how it multi-threads within Windows, and whether triggers are permitted to fire other triggers. When configuring the server, keep in mind the goals of configuration: consistency and performance. Graphically, many of the server options may be configured within the Server Properties page, which you …Continue reading "MSSQL Configuring the Server"
Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the …Continue reading "MSSQL NEWSEQUENTIALID()"
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 ROWCOUNT_BIG is bigint.Continue reading "MSSQL @@ROWCOUNT"
All three are used to return the latest identity value. -@@IDENTITY: It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. @@IDENTITY will return the last identity value entered into a table in your current session. While …Continue reading "MSSQL @@IDENTITY vs. SCOPE_IDENTITY() vs. IDENT_CURRENT"
It is a robust, comprehensive yet user-friendly synchronization platform providing synchronization capabilities between two distinct data sources. It also enables developers to create and include sync capabilities in their applications. The overall goal of the Sync Framework is to enable synchronization between two peers such as two SQL Server databases or a Microsoft SQL Server Compact database client …Continue reading "MSSQL Microsoft Sync Framework"
A good server design has no, or very few, single points of failure. One of the most common server component that fails, are disks. So data redundancy becomes essential to recoverability. Redundant Array of Independent/Inexpensive Disks (RAID) is a disk system that provides better fault-tolerance by making use of redundancy of disk(s). RAID is a disk system …Continue reading "MSSQL RAID"
Short for bulk copy program (or bulk copy Porsche — a reference among DBAs to its speed), is a command-line variation of bulk operations. BCP differs from BULK INSERT in that it is command-line executed and can import or export data. It uses many of the same options as BULK INSERT.Continue reading "MSSQL BCP"
The BULK INSERT command can be used within any T-SQL script or stored procedure to import data into SQL Server. The parameters of the command specify the table receiving the data, the location of the source file, and the options.Continue reading "MSSQL Bulk Insert"