|
|
-
|
|
An easiest way to compare two tables is by using UNION, EXCEPT and INTERSECT operators.
These operators takes two row sets as input, the output depends on the operator:
UNION: produces a new row set by joining both row sets, identifying common
rows in input row sets and dupl......
|
|
-
|
|
Sometimes we need to change the name of the database, for example, a database might have been restored using a different name. Whatever the reason may be, this can be easily done via Management Studio or T-SQL.
1. Rename a Database using Management Studio:
Step 1: Renaming Database:
Right Click t......
|
|
-
|
|
sys.dm_os_volume_stats
is a new dynamic management function introduced in SQL Server 2008 R2 SP1 and Denali
CTP3. It returns information about partitions on which database files reside.
You can now check for free space on a particular partition, instead of using
xp_f......
|
|
-
|
|
There are three new server-related DMVs introduced in SQL Server 2008 R2 Service Pack 1 and SQL Server Codename "Denali".
1. sys.dm_server_services:
- For SQL Server 2008 R2 SP1, it contains information about SQL Server and SQL Server Agent services for the current instance.
- For "Denali", it re......
|
|
-
|
|
LAG() function can be used to access data from a previous row in the result
set without using a self-join. And it’s counterpart LEAD() can be used to
access data from a subsequent row in the same result set. These functions are introduced
in “Denali” as T-SQL f......
|
|
-
|
|
PARSE() and TRY_PARSE() are new function introduced in SQL Server Denali.
PARSE() returns the result of an expression, translated to specified data type if the translation is possible, otherwise, it raises an error.
TRY_PARSE() tries to translate the result of an expression to specified data type ......
|
|
-
|
|
SQL Server Denali CTP3 introduces a new date/time function EOMONTH(). EOMONTH() returns the last day of the month for the given date. It also accepts an optional argument which is an integer expression specifying number of months to add/remove from specified date.
Syntax:
EOMONTH( ......
|
|
-
|
|
“Denali” introduces a new function CONCAT() for string concatenation. CONCAT() returns
a string by concatenating two or more strings. CONCAT() requires 2 to 254 arguments.
for example, CONCAT() can be used as below:
SELECT CONCAT('SQL ', 'Server') AS Result
Resu......
|
|
-
|
|
Partitioning was introduced in SQL Server 2005, it allows you to different database
files, which can be located on different disks to improve performance. In earlier
versions this was achieved by creating separate tables on different file groups
of database, and then creating a ......
|
|
-
|
|
Denali introduces a new function IIF(), which is a shorthand way of writing
a CASE expression. IIF() evaluates specified expression and returns
specified true/false values. You might be already familiar with IIF() in
Reporting Services and MS Access. C/C++ also has the same as "......
|
|