T-SQL – Page 2 – John Huang's Blog

Change Default DML Behavior

April 11, 2012 by John H

DML, Data Manipulation Language, is used to add data to table and modify existing rows in tables. There are 3 commands

Insert : insert records to a table
Delete: remove records to a table
Update: modify records in a table

In SQL Server, you are allowed to change the default behaviors of those 3 commands. For instance, while inserting a record, the new record can be applied to a table as an update(can be a delete as well).

Generate MD5 Value from Big Data

January 16, 2012 by John H

How do you generate MD5 hash in SQL Server? You might immediately tell that it can be generated MD5 by calling HASHBYTES built-in function. That’s true, however, it only accepts generating MD5 hashes from variables which has less than 8000 bytes. How would you generate MD5 hash from big variables?

Truncate

December 23, 2011 by John H

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

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

Generating Random Password by a T-SQL Procedure

December 19, 2011 by John H

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.

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

March 1, 2012December 7, 2011 by John H

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.

SQL Server 2012 Sequence Internal

November 20, 2011November 18, 2011 by John H

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.

Merge Statement

October 21, 2011 by John H

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 .