Truncate

Posted on by John H1 Comment on Truncate

What are the differences between TRUNCATE and DELETE? You may immediately point out that:

  1. Truncates reset the identity value
  2. Truncates can’t have where clause
  3. Truncates can’t be run on a table which is referenced by other tables
  4. Truncates do not fire triggers.
  5. Truncates can’t be run on a table which has depending objects such as indexed views.
  6. Truncates can’t be run on a table which has published by merge or transactional replication publication
  7. Truncates need db_owner and db_ddladmin permission.
  8. Truncates need locks on the table and schema but do not need locks on rows of the tables

Continue reading “Truncate” →

Generating Random Password by a T-SQL Procedure

Posted on by John H1 Comment on Generating Random Password by a T-SQL Procedure

To generate a complex password, we should not only get random value from the set of the characters but also guarantee the combination of upper case, lower case, numbers and special characters. In the procedure below, the password generated will include all 4 parts.
Continue reading “Generating Random Password by a T-SQL Procedure” →

Rows|Range – Another Layer of Partition within Over Clause in SQL Server 2012

Posted on by John H6 Comments on Rows|Range – Another Layer of Partition within Over Clause in SQL Server 2012

Rows|Range enhances partition functionality of Over clause in SQL Server 2012. In the Over clause before SQL 2012, you can group rows with the same attributes together, which is called partition. You can also order the rows within each of partitions, which is implemented by Order By. The aggregation functions in front of Over clause will be are partition and order based. In SQL Server 2012, a lower level “partition”, which is called windows fram, range of rows, can be defined under the partition. A window frame can be static like partition clause but can also be dynamic – moving window. This provides great power on data analytics, for instance, you no longer need joins for calculating moving average, instead, one SQL with moving window can give you the result.

Continue reading “Rows|Range – Another Layer of Partition within Over Clause in SQL Server 2012” →

SQL Server 2012 Sequence Internal

Posted on by John H4 Comments on SQL Server 2012 Sequence Internal

Sequence is a new feature introduced in SQL Server 2012. Unlike identity values, sequence is an independent object. It can be used in table definition as a default constraint on a column or referenced from a query including OVER clause. There are few DDLs and DMLs supporting sequences.

  • Create Sequence: create a sequence optionally with properties, returning type, starting value, interval, minimum value, maximum value, cycle, cache. If you don’t specify any properties, bigint will be used as returning data type.
  • Alter Sequence: Modify properties of sequence, returning type, starting value, interval, minimum value, maximum value, cycle, cache
  • Drop Sequence: Remove a sequence.
  • Next Value For: Get next value of a sequence.It cannot be called from functions.
  • sp_sequence_get_range: Get range of sequence.
  • sys.sequences: DMV for sequence.

Continue reading “SQL Server 2012 Sequence Internal” →

Merge Statement

Posted on by John HLeave a Comment on Merge Statement

Merge statement was introduced by Microsoft in SQL Server 2005. It allows user to merge one table to another combining different ways of operation, inserting, deleting and updating, on the targeting table based upon the condition specified in the merge statement.
When Matched: It means when the record in the source table and target table matched by key. In this case, you have option of, update target or delete target.
When Not Matched: It means the records in the source table do not exist in the target. The possible operation is to insert record to the target.
When Not Matched By Source: it means the records are in target table but not in the source table. In this case, you can choose to update or remove the record in the target table .

Continue reading “Merge Statement” →