This is a set of examples highlighting basic usage of SQL Server.
The OVER clause determines a windows or a subset of row within a query result set. A window function can be applied to set and compute a value for each row in the set. The OVER clause can be used with:
so someone can compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.
In a very abstract way we can say that OVER behaves like GROUP BY. However OVER is applied per field / column and not to the query as whole as GROUP BY does.
Note#1: In SQL Server 2008 (R2) ORDER BY Clause cannot be used with aggregate window functions (link).
Using PIVOT and UNPIVOT operators you transform a table by shifting the rows (column values) of a table to columns and vise-versa. As part of this transformation aggregation functions can be applied on the table values.
A database snapshot is a read-only, static view of a SQL Server database which is transactionally consistent with the source database as of the moment of the snapshot's creation.
A database snapshot always resides on the same server instance as its source database. As the source database is updated, the database snapshot is updated.
A snapshot differs from a backup since the process of snapshot creation is instantaneous and the snapshot occupies space only as changes in the source database are applied. A backup on the other hand stores a full copy of the data as on the time of backup creation.
Additionally, a snapshot gives an instant read only copy of the database, while a backup needs to be restored to a server in order to be readable (and once restored can be written to as well)
Database snapshots are only available in the Enterprise and Developer editions.
As with other relational database systems, SQL Server exposes metadata about your databases.
This is provided through the ISO Standard INFORMATION_SCHEMA schema, or the SQL Server-specific sys catalog views.
There are also several other FOR XML modes:
FOR XML RAW - Creates one <row> element per row.FOR XML AUTO - Attempts to heuristically autogenerate a hierarchy.FOR XML EXPLICIT - Provides more control over the shape of the XML, but is more cumbersome than FOR XML PATH.Normally you would want to avoid using cursors as they can have negative impacts on performance. However in some special cases you may need to loop through your data record by record and perform some action.
It is necessary to separate a CTE from the previous statement with a semi-colon (;) character.
i.e. ;WITH CommonTableName (...) SELECT ... FROM CommonTableName ...
A CTE's scope is a single batch, and only downstream of its definition. A batch may contain multiple CTEs, and a CTE may reference another CTE defined earlier in the batch, but a CTE may not reference another CTE that is defined later in the batch.
as per https://msdn.microsoft.com/en-us/library/ms187819.aspx, DateTimes are only precise to 3ms.
Rounding of datetime Fractional Second Precision datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.
| User-specified value | System stored value |
|---|---|
| 01/01/98 23:59:59.999 | 1998-01-02 00:00:00.000 |
| ------ | ------ |
| 01/01/98 23:59:59.995 | 1998-01-01 23:59:59.997 |
| 01/01/98 23:59:59.996 | |
| 01/01/98 23:59:59.997 | |
| 01/01/98 23:59:59.998 | |
| ------ | ------ |
| 01/01/98 23:59:59.992 | 1998-01-01 23:59:59.993 |
| 01/01/98 23:59:59.993 | |
| 01/01/98 23:59:59.994 | |
| ------ | ------ |
| 01/01/98 23:59:59.990 | 1998-01-01 23:59:59.990 |
| 01/01/98 23:59:59.991 | |
| ------ | ------ |
If more precision is required, time, datetime2 or datetimeoffset should be used.
If ORDER BY clause is used, limiting applies to the ordered result set.
The OPENJSON function is only available under compatibility level 130. If your database compatibility level is lower than 130, SQL Server will not be able to find and execute OPENJSON function. Currently all Azure SQL databases are set to 120 by default. You can change the compatibility level of a database using the following command:
ALTER DATABASE <Database-Name-Here> SET COMPATIBILITY_LEVEL = 130
Most experts seem to recommend creating a Dates table instead of generating a sequence on the fly. See http://dba.stackexchange.com/questions/86435/filling-in-date-holes-in-grouped-by-date-sql-data
Resource Governor in SQL Server is a feature that allows you to manage resource usage by different applications and users. It kicks in realtime by setting CPU and memory limits. It will help preventing that one heavy process will eat up all system resources while for example smaller tasks are awaiting them.
Only available in Enterprise Editions
The purpose of the ORDER BY clause is to sort the data returned by a query.
It's important to note that the order of rows returned by a query is undefined unless there is an ORDER BY clause.
See MSDN documentation for full details of the ORDER BY clause: https://msdn.microsoft.com/en-us/library/ms188385.aspx
Using a WHILE loop or other iterative process is not normally the most efficient way to process data in SQL Server.
You should prefer to use a set-based query on the data to achieve the same results, where possible
Primary keys are used to uniquely identify a record in a table. A table may only have a single primary key (though the primary key can consist of multiple columns), and a primary key is required for certain types of replication.
Primary keys are often used as (but don't have to be) the clustered index on a table.
Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.
If two or more rows tie for a rank in the same partition, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number two. This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.
The sort order used for the whole query determines the order in which the rows appear in a result. This implies that a row ranked number one does not have to be the first row in the partition.
DENSE_RANK is nondeterministic. For more information, see Deterministic and Nondeterministic Functions.
SQL Server provides other methods to handle nulls, such as IS NULL, IS NOT NULL, ISNULL(), COALESCE() and others.
MSDN Reference: SET TRANSACTION ISOLATION LEVEL
TRY/CATCH is a language construct specific to MS SQL Server's T-SQL.
It allows error handling within T-SQL, similar to that seen in .NET code.
UDTs have following restrictions -
Table valued parameters (TVP for short) are parameters passed to a stored procedure or function that contains data that is table structured. Using table valued parameters requires creating a user defined table type for the parameter being used.
Tabled valued parameters are readonly parameters.
SQL Server 2016 introduces support for system-versioned temporal tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time.
A system-versioned temporal table is a new type of user table in SQL Server 2016, designed to keep a full history of data changes and allow easy point in time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (i.e. database engine). Every temporal table has two explicitly defined columns, each with a datetime2 data type. These columns are referred to as period columns. These period columns are used exclusively by the system to record period of validity for each row whenever a row is modified.
Views are stored queries that can be queried like regular tables. Views are not part of the physical model of the database. Any changes that are applied to the data source of a view, such as a table, will be reflected in the view as well.
Temporary Tables are really very helpful.
The table can be created at runtime and can do all operations which are done in a normal table.
These tables are created in a tempdb database.
Used when ?
We have to do complex join operation.
We do large number of row manipulation in stored procedures.
Can replace the usage of cursor.
Thus increases the performance.
I found this link - it's useful as a reference: "Isolation Levels"
You either need to be in the path where SQLCMD.exe exists or add it to your PATH environment variable.
Basic Syntax:
{GRANT| REVOKE | DENY} {PERMISSION_NAME} [ON {SECURABLE}] TO {PRINCIPAL};
DENY SELECT" means that regardless of any other permissions, SELECT will fail for this principal)GRANT SELECT on a stored procedure.GRANT SELECT TO [aUser]; is perfectly acceptable; it means "for any securable for which the SELECT permission makes sense, GRANT that permission".Creation of a DER certificate will work fine. When a Base64 certificate is used however, SQL server will complain with the cryptic message:
Msg 15468, Level 16, State 6, Line 1
An error occurred during the generation of the certificate.
Import your Base64 certificate to your OS's certificate store to be able to re-export it into DER binary format.
Another important thing to do is having an Encryption Hierarchy so that one protects the other, all the way to OS level. See the article on 'Encryption of database/TDE'
For more information for creation of certificates go to: https://msdn.microsoft.com/en-us/library/ms187798.aspx
For more information for encryption of database/TDE go to: https://msdn.microsoft.com/en-us/library/bb934049.aspx
For more information for encryption of data go to: https://msdn.microsoft.com/en-us/library/ms188061.aspx
Generally speaking, it is best not to use T-SQL Reserved Words as table names, column names, programming object names, alias etc. So the method to escape these keywords should only be applied if you are inheriting a database design that cannot be changed.
For reserved words, usage of the square brackets is not mandatory. When using a tool such as SQL Server Management Studio, the reserved words will be highlighted to bring attention to the fact that they are reserved.
Above example is just to show the syntax for using case statements in SQL Server with day of week example. Although same can output can be achieved by using "SELECT DATENAME(WEEKDAY, GETDATE())" as well.
You can read the various ISOLATION LEVEL on MSDN