|
|
-
|
|
sp_help returns information about database objects and types.
@object_name argument is optional for sp_help. When executed without any argument it returns summary information for objects of all types in the database. These includes all user tables, system tables, stored procedures, functions, exten......
|
|
-
|
|
You can browse available SQL Instances on network by choosing "" from Server Name drop-down list in "Connect to Server" dialog box in Management Studio:
As you can see from the screen shot, all of my instances are visible on the network. If I want to hide this information fro......
|
|
-
|
|
sys.dm_db_persisted_sku_features
lists all features which are utilized by the database. Features specific to Enterprise/Developer
edition are:
- Compression,
- Partitioning,
- TDE and CDC
These features are available only on Enterpr......
|
|
-
|
|
PERCENT_RANK() returns the position of a row within the result set. In contrast
to RANK() function, PERCENT_RANK() ranks rows between 0 and 1, both inclusive.
Computation formula used by PERCENT_RANK():
(RANK() – 1) / (Number of Rows – 1)
......
|
|
-
|
|
CUME_DIST() calculates relative position of a value relative to a group of
values. The value returned by CUME_DIST() is > 0 and <= 1,
which represents percentage of number of rows with value less than (for ascending
order) or equal to current row.</p>
......
|
|
-
|
|
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
———– ———- ......
|
|