"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

March 22, 2010

SQL Tips

SQL Tip #1

PATINDEX - This function Returns the starting position of the first occurrence of a pattern in a specified
Example - Check to See String contains only numbers. Verify only numbers are present.

IF PATINDEX('%[a-z]%','11') > 0
    PRINT 'String'
ELSE
    PRINT 'Numbers'

SQL Tip #2
Query to Check for Available Physical Memory
SELECT
(Physical_memory_in_bytes/1024.0)/1024.0 AS PhysicalMemoryMb
FROM
sys.dm_os_sys_info

SQL Tip #3
ACID Properties short RECAP :)
Atomicity - Transaction is one unit of work. All or None. Either all of its data modifications are performed or none of them are performed
Consistency - Transaction must leave database in consistent state. Maintain data integrity. Governing Data Structures for Indexes/Storage must be in correct state. If a Transaction violates a constraint it must be failed.
Isolation - Keep Transaction Seperate. Concurrency is governed by Isolation levels.
Durability - Incase of System failures changes persisit and can be recovered on abnormal termination

SQL Tip #4
Difference between SET and SELECT
  • You can assign one variable at a time using SET
  • But assigning Multiples variables in one SELECT, SELECT is faster

No comments: