FLWOR stands for ‘For, Let, Where, Order By and Return’ which is an XQuery feature that allows you to perform complex operations on an XML document. The FLWOR operation allows you to iterate over the elements/attributes of an XML document and extract specific information from it or generate a completely new XML document.Continue reading "MSSQL FLWOR operation"
Extended stored procedures can be built to perform functionality not possible with Transact-SQL stored procedures. Extended stored procedures can, however, compromise the integrity of the SQL Server process, while managed code that is verified to be type-safe cannot. Further, memory management, scheduling of threads and fibers, and synchronization services are more deeply integrated between the managed code of …Continue reading "MSSQL Extended Stored Procedures"
The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception …Continue reading "MSSQL CLR Integration and .NET with SQL"
Lets you specify which features will be available to the database and enables you to ensure compatibility with applications written for earlier versions of SQL Server. By default, the Compatibility level drop-down list box is set to SQL Server 2008 (100). The supported compatibility level settings are: 80 (SQL Server 2000), 90 (SQL Server 2005), 100 (SQL …Continue reading "MSSQL Compatibility Level"
PowerShell is a relatively new interactive shell and scripting tool from Microsoft that combines the capabilities of the MS-DOS Command Prompt, batch files, Windows Scripting Host and even the popular Unix shells. Windows PowerShell is an extensible command-line shell and associated scripting language built on top of the .NET Framework v2.0. PowerShell includes an impressive array of commands …Continue reading "MSSQL PowerShell"
DBCC: Microsoft SQL Server Database Console Commands (DBCC) are used for checking database integrity, performing maintenance operations on databases, tables, indexes, and filegroups, and collecting and displaying information during troubleshooting issues. .The first DBCC command to become familiar with is the DBCC HELP command, which returns the syntax and all the options for any DBCC command. The …Continue reading "MSSQL DBCC- Database Console Commands"
When many people attempt to modify data in a database at the same time, a system of controls must be implemented so that modifications made by one person do not adversely affect those of another person. This is called concurrency control. Concurrency control theory has two classifications for the methods of instituting concurrency control: •Pessimistic concurrency control: …Continue reading "MSSQL Concurrency Control"
When a transaction running under snapshot isolation reads data that is then modified by another transaction, an update by the snapshot transaction to the same data causes an update conflict and the transaction terminates and rolls back. This is not an issue with read committed isolation using row versioning.Continue reading "MSSQL Update Conflict"
Data getting changed in current transaction by other transactions is called Phantom Reads. New rows can be added by other transactions, so you get different number of rows by firing same query in current transaction. In REPEATABLE READ isolation levels Shared locks are acquired. This prevents data modification when other transaction is reading the rows and also …Continue reading "MSSQL Phantom Read"
Non-repeatable reads happen when a query returns data that would be different if the query were repeated within the same transaction. Non-repeatable reads can occur when other transactions are modifying data that a transaction is reading. Non-repeatable reads can be prevented by using SERIALIZABLE isolation level, the highest level.Continue reading "MSSQL Non-Repeatable Read"