|
|
-
|
|
ROWS/RANGE allows you to change the frame size within a partition. Valid
arguments for ROWS are:
UNBOUNDED PRECEDING
Starts the window at first row of the partition
UNBOUNDED FOLLOWING
......
|
|
-
|
|
xp_servicecontrol is an undocumented extended stored procedure, which can
be used to start/stop or check the state of Windows services. It has the following
syntax:
xp_servicecontrol @Action = N'Action' @ServiceName = N'Service Name'
Arguments:
......
|
|
-
|
|
FIRST_VALUE() and LAST_VALUE() are new analytic function introduced in SQL Server "Denali". As the name suggests FIRST_VALUE() returns first value in an ordered set of values, and LAST_VALUE() returns the last value from an ordered set of values.
For example,
SELECT EmployeeID, FirstName, MiddleNa......
|
|
-
|
|
Finding duplicate rows in a table can be done easily by using ROW_NUMBER() function. You can number each similar rows by using PARTITION BY clause.
For example, consider the below table:
EmployeeID FirstName MiddleName LastName
———– ———- ......
|
|
-
|
|
You can use sys.dm_exec_requests DMV to list all requests. This view also contains a hash map of the SQL text – sql_handle, which can be passed to DMF sys.dm_exec_sql_text to get the query text:
SELECT REQUEST.session_id, REQUEST.start_time, QUERY.text
FROM sys.dm_exec_requests R......
|
|
-
|
|
It’s fairly simple, create a CLUSTERED INDEX on the table. While creating a clustered index you need to specify the partition scheme to be used in ON clause of CREATE INDEX statement.
We still need to create a partition function and a partition scheme, I have posted earlier about how to creat......
|
|
-
|
|
In most cases we use stored procedures to update or delete rows from related tables.
But, you can also cascade the actions using foreign key constraints. This is generally
not used due to the fact that it is transparent, and leads to *mysterious* results.
Let’s see how it ......
|
|
-
|
|
Whenever we need to update a column length, we can do the same by using wither Table designer in Management Studio or by using T-SQL. Changing this using Management Studio adds an overhead of recreating the table, which can cause external fragmentation in the database. Let’s compare both these......
|
|
-
|
|
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......
|
|