MSSQL The ANSI settings

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"

MSSQL Configuring the Database

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"

MSSQL Configuring the Server

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"

MSSQL @@IDENTITY vs. SCOPE_IDENTITY() vs. IDENT_CURRENT

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"

MSSQL Microsoft Sync Framework

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"

MSSQL RAID

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"