|
|
-
|
|
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
......
|
|
-
|
|
SSMS allows you copy query results with column headers. However, if you copy results
by choosing "Copy" or Ctrl + C, by default it does not copy headers.
Management Studio 2008 allows you to copy data with headers by choosing "Copy with
Headers" from Edit o......
|
|
-
|
|
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 ......
|
|
-
|
|
Management Studio provides a shortcut for selecting or editing top rows of a table. You can use this by right-clicking table in Object Explorer, and choosing appropriate option:
Select Top 100 Rows: This opens a new query for returning top 1000 rows from the table and executes the same.
Edit T......
|
|
-
|
|
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......
|
|