SQL Server tips

Recently, I was working on a project which involved SQL Server. I made a note of some useful commands that help me find useful things out. Here they are:

Check column types for a specific table:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='TOSENV'

Check column constraints

SELECT * FROM CONSTRAINT_COLUMN_USAGE

Check length of text in a varchar column

SELECT LEN(MYCOLUMN) FROM MYTABLE;

List all tables that have a column named EntityID

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'EntityID'

Check all constrains...

SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

Find blocked processes

SELECT * FROM  SYS.SYSPROCESSES WHERE BLOCKED > 0

To see long running open transactions

DBCC OPENTRAN;

Check the lock timeout

SELECT @@LOCK_TIMEOUT

Check current session

SSP_WHO2

Find creation date of databases

SELECT NAME, CRDATE FROM MASTER..SYSDATABASES

Get the database id for a database.

SELECT DB_ID('PLANES')

Get the median value for a column named responsetime in a table allrequests

SELECT( (SELECT MAX(responsetime) FROM (SELECT TOP 50 PERCENT responsetime FROM allrequests ORDER BY responsetime) AS BottomHalf) + (SELECT MIN(responsetime) FROM (SELECT TOP 50 PERCENT responsetime FROM allrequests ORDER BY responsetime DESC) AS TopHalf)) / 2 AS Median

Most CPU intensive queries

SELECT HIGHEST_CPU_QUERIES.PLAN_HANDLE, HIGHEST_CPU_QUERIES.TOTAL_WORKER_TIME, Q.DBID, Q.OBJECTID, Q.NUMBER, Q.ENCRYPTED, Q.[TEXT]FROM (SELECT TOP 50 QS.PLAN_HANDLE, QS.TOTAL_WORKER_TIME FROM SYS.DM_EXEC_QUERY_STATS QS ORDER BY QS.TOTAL_WORKER_TIME DESC) AS HIGHEST_CPU_QUERIES CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) AS QORDER BY HIGHEST_CPU_QUERIES.TOTAL_WORKER_TIME DESC INDEXES AND KEYS

Get me all the tables that have a FK to the table named Person

SELECT OBJECT_NAME ([PARENT_OBJECT_ID]) AS 'REFERENCING TABLE', * FROM SYS.FOREIGN_KEY_COLUMNSWHERE [REFERENCED_OBJECT_ID] = OBJECT_ID ('PERSON'));

Check all objects that have changed in last ten days

SELECT NAME AS OBJECT_NAME ,SCHEMA_NAME(SCHEMA_ID) AS SCHEMA_NAME ,TYPE_DESC ,CREATE_DATE ,MODIFY_DATEFROM SYS.OBJECTS WHERE MODIFY_DATE > GETDATE() - 10 ORDER BY MODIFY_DATE;
Disable indexes

ALTER INDEX [IX_NAME] ON TABLE DISABLE

Enable indexes

ALTER INDEX [IX_NAME] ON TABLE REBUILD USERS CHECK CURRENT USER

Check current user

SELECT SYSTEM_USER;

Check database owner

SELECT NAME, SUSER_SNAME(OWNER_SID) FROM SYS.DATABASES STATS

Check last time Stats was run

SELECT NAME AS STATS_NAME, STATS_DATE(OBJECT_ID, STATS_ID) AS STATISTICS_UPDATE_DATE FROM SYS.STATSWHERE OBJECT_ID = OBJECT_ID('DBO.TASCONTACT');

Run stats for a table named Person in Sales

UPDATE STATISTICS SALES.PERSON

Check the size of various tables

SELECT OBJECT_NAME(P.OBJECT_ID) AS "TABLE", P.INDEX_ID, F.NAME, SUM(TOTAL_PAGES)/128 AS "SIZE IN MB", CONVERT(VARCHAR(10), GETDATE(), 101), COUNT(*) AS PARTITIONSFROM SYS.PARTITIONS P JOIN SYS.ALLOCATION_UNITS A ON P.PARTITION_ID = A.CONTAINER_ID JOIN SYS.FILEGROUPS F ON A.DATA_SPACE_ID = F.DATA_SPACE_IDGROUP BY P.OBJECT_ID, P.INDEX_ID, F.NAMEORDER BY SUM(TOTAL_PAGES)/128 DESC

Find the creation time for all tables.

SELECT NAME AS TABLENAME, CREATE_DATE AS CREATEDDATE FROM SYS.TABLES VERSION

Check version

SELECT @@VERSION;

or for more info

SELECT SERVERPROPERTY('EDITION') AS SQL_Server_Edtition, SERVERPROPERTY('PRODUCTLEVEL') AS SQL_Product_Level, SERVERPROPERTY('PRODUCTVERSION') AS SQL_Version;

The A to Z of DB2

In nearly every software architecture there is a relational database - somewhere.  And in nearly every relational database there is a range of concepts and buzzwords.  Some unique to a particular database vendor but many not.  In this post we run through some concepts / buzzwords for DB2 - alphabetically!

Authority Levels

A DB2 Authority Level is a security level representing a collection of privileges and higher-level database manager maintenance and utility operations.  SYSADM, SYSCTRL, SYSMAINT, SYSMON are instance level authorities and can only be assigned to a group. DBADM, SECADM and LOAD are database level authorities.

SYSADM is the only authority which can:

•  update the DMB CFG file

•  grant SYS* authorities to other groups. Grant DBADM authority to users / groups

•  access data within any database
•  do anything SYSCTRL can do

SYSCTRL:

•  only access data in database if given privilege
•  can create/drop databases, tablespaces
•  do anything SYSMAINT can do

SYSMAINT can:

•  db2start/ db2stop / backup / restore/ runstats

DBADM can:

• do anything for a particular database
• grant load authority to other users

Authentication Types

The following Authentication types are available:

SERVER: authentication takes place on the Server

SERVER_ENCRYPT: authentication takes place on the Server username / password is encrypted on the client before being sent.

CLIENT, KERBEROS, KRB_SERVER_ENCRYPT, DATA_ENCRYPT, DATA_ENCRYPT_CMP, GSSPLUGIN, GSS_SERVER_ENCRYPT.

If a Client uses the SERVER_ENCRYPT and the Server uses SERVER authentication type and error will occur when client tries to connect to the Server.

Common Table Expressions

Common Table Expressions exist only for the life of the SQL statement that created them. They are used for special cases e.g. recursion in a query.  

The syntax is: WITH [tablename] ...

Command Editor

Interactive GUI for SQL commands

Configuration Assistant

The Configuration Assistant enables users to configure clients so that they can access databases stored on remote DB2 servers. The configuration assistant allows users to catalog, uncatalog databases, bind applications, set DB2 registry parameters, changes passwords, test database connections.

Connect Enterprise Edition

Connect Enterprise Edition is an add on product that allows data to be moved between Linux, Unix, Windows, iSeries and zSeries based DB2 servers.

Constraints

Constraints are used to enforce business rules (e.g. attributes in a column cannot be null).  The SQL used to create constraints is stored in the System Catalog.

Control Center

Performs admin for instances / databases / bufferpools / tablespaces/ views / indexes... Catalog / Uncatalog databases. And all sorts of other DB tasks.

Cursor Operations

Update and delete operations can be performed using position operations or search operations.  In a position operation the cursor must be first created, opened and then positioned.  When a cursor is declared with 'WITH HOLD' option, it will remain open across transactions until it is explicitly closed.

Cursor Usage Steps

The steps to use a cursor are: DECLARE CURSOR, OPEN, FETCH, CLOSE.

Database Manager Configuration File

Stores the names of the groups which have been given instance level authorities (SYSADM, SYSCTRL, SYSMAINT, SYSMON)

Decimal

There four ways to define decimal types: DECIMAL(percision, scale), DEC(precision, scale), NUMERIC(precision, scale) and NUM(precision, scale)

DCS directory

The DCS directory stores database information for remote databases on z/OS iSeries.

Declared temporary tables

User defined tables to hold non persistent data.   They are created by the application and destroyed when the application terminates.

Design Advisor

The design advisor makes recommendations for new indexes, deletion of indexes, Materialized Query Tables, Multi Dimensional Clustering

Developer Workbench (Development Center in v8)

Used to create, edit, debug, deploy, test DB2 stored procedures and user defined functions. Also to develop and run XML queries.

Distinct Type

A distinct data type is a user-defined data type that is derived from one of the built in data types in DB2. Example of syntax creation: CREATE DISTINCT TYPE euro AS DECIMAL (9,3) WITH COMPARISON.  Disinct types are strongly typed; they cannot be used as an argument for a built-in data type in a built-in function, even if they are derived from them (and vice versa).  If the WITH COMPARISON syntax is used during creation, it means that comparison functions (<>, <, > , <=, >=, >) and ORDER BY, GROUP BY clauses can be used for the distinct type. Two casting functions are created anytime a distinct type is created.  This is used to convert to / from its base type and has the same name as the distinct type.

Extenders

Extenders are advanced data types that are not part of the built-in datatypes.  There are 6 types of extenders in DB2.

• DB2 AVI extender
• DB2 Text extender
• DB2 Net search extender
• DB2 XML extender
• DB2 Spatial extender
• DB2 Geodetic extender - can treat earth like globe rather than flat map.

Foreign Key Constraints for Delete:

ON DELETE CASCADE: When entity is deleted from parent table, any entity which has a FK to the parent entity will be deleted.

ON DELETE SET NULL: When entity is deleted from parent table, FK will be set to null

ON DELETE RESTRICT: When entity is deleted from parent table, FK values must point to same value

ON DELETE NO ACTION: When entity is deleted from parent table, FK values must point to something valid but can change

Foreign Key Constrains for Update:

ON UPDATE RESTRICT: When entity is updated in parent table, the FK values must have to have the same values

ON UPDATE NO ACTION: When the entity is updated in the parent table, the FKs values can change but must always point at something.

Grant All (table)

GRANT ALL privileges for table means: ALTER, SELECT, INSERT, UPDATE, DELETE, INDEX, REFERENCES privileges.  Note there is no 'ADD' privilege.

Graphic

Graphic is a fixed length double-byte character type.

Group By

Used to specify columns that are to be grouped together and to provide input into aggregate functions such as SUM() and AVG()

Group By Cube 

Used to group in multiple dimensions e.g. SELECT workdept, gender, AVG(salaray) AS avg_salary FROM employee GROUP BY CUBE (workdept, gender);

Having

The having clause is used to by further selection criteria to a GROUP BY clause. It refers to data that has already been grouped by a GROUP BY clause.  It uses same syntax as WHERE clause and can only be used in by the GROUP BY clause.

Health Center

The Health Center tool is used to select instance and database objects that you want to monitor

Identity Column

Identity columns must be a numeric data type with a scale of 0.

Indexes 

The creation of an index provides logical ordering of rows in a table in ascending order of the index.

Isolation levels

Repeatable read isolation level will lock rows scanned in a query.

Read stability isolation level will only lock the rows returned in the result data set.

Cursor stability isolation level will only lock the result set that the cursor is currently pointing to.

The uncommitted read isolation level will not lock any rows during normal read processing (unless another user tries to alter or drop the table being read).

Journal

The DB2 journal is an interactive GUI that tracks historical information about tasks, database actions and operations

License Center

the License Center Allows users to view information about licenses

Like (table creation)

CREATE TABLE ... LIKE ... - creates table which has exact same name, datatype and nullability characteristics.

Locks

Locks can only be applied to table spaces, tables and rows.

Lock conversion

The act of changing a lock to a more restrictive state.  In most cases, lock conversation happens for row level locks, e.g. if an Update(U) lock is held and an Exclusive(X) lock is needed, the update(U)lock will be converted to an Exclusive lock

Lock escalation

Lock escalation is when the size of a lock changes.  For example from Row to Table size. This is usually to free up some space in the Lock list.

Lock list

The specific amount of memory set aside to hold a structure that DB2 uses to manage locks.

Lock state (or mode)

DB2 locks can have various states: Intent None, Intent share, Next Key Share, Share, Intent Exclusive, Share with Intent Exclusive, Update, Next Key Exclusive, Exclusive, Weak Exclusive, Super Exclusive.  DB2 determines the lock state by looking at isolation level and the SQL being executed.

Materialised Query Tables:

User defined tables whose definition is based on the result of a query used for query optimization.

Null

For predicates use IS NULL. In result sets, - means null.  Unique indexes can one null value. Unique constraints can have never have a null value.  Nulls can't be in used with IN clauses.

Operating System Support

DB2 Type / OS	Linux	Windows	Solaris	Mobile OS	AIX	HP-UX	System i
DB2 Everyplace	No	No	No	Yes	No	No	No
DB2 PE	Yes	Yes	No	No	No	No	No
DB2 Express C	Yes	Yes	No	No	No	No	No
DB2 Express	Yes	Yes	Yes	No	No	No	No
DB2 i5 / Os	No	No	No	No	No	No	Yes
DB2 WSE	Yes	Yes	Yes	No	Yes	Yes	No
DB2 ESE	Yes	Yes	Yes	No	Yes	Yes	No

Packages

A package is an object that contains the information needed to process SQL statements associated with a source code file of an application program.

Privilege: Alter (Table)

The alter table privilege allows user to add columns to a table, add / change table comments, create a table pk, unique/check constraint, triggers for table. 

Privilege: Control 

The control privilege that applies to Table, View, Nicknames, Packages and Indexes. It includes every privilege including the privilege to drop the object from the DB.  The owner of a table automatically receives control privilege (and all other privileges).  Only users with SYSADM or DBADM authority are allowed to explictily grant CONTROL privilege on any object.  A user with CONTROL privilege can grant any table privilege except CONTROL privilege.

Privilege: Connect 

(Database)

Connect Privilege applies to a database. It allows users to connect.

Privilege: References (Table)

Reference table privilege allows a user to create and drop foreign key constrains that reference a table in a parent relationship. This can be granted for entire table of just specific columns in 

the table.

Privilege: Usage (sequences)

Allows the PREVIOUS VALUE and NEXT VALUE expression associated with the sequence to be changed.

Privileges: Packages 

The BINDADD privilege at database allows a user to create packages in the database.

Privileges (Explicit / Implicity)

Explicit privileges have to be granted explicitly. Implictly privileges do not. For example, CONTROL is granted to anyone who creates a Table or View.  CREATEIN, ALTERIN, DROPIN is granted to anyone who creates a schema.

Privileges: View / Nickname

There is no Alter, Index or Reference privilege for View. Otherwise they have have the same privileges as Tables. Nickhaves have the same privileges possible as Tables.

Privileges:Index

There is only one Index privilege - it is Control.

Replication Center

Facilitates data replication between a DB2 database and any other relational database

Routine

A routine is a user defined function or stored procedure

Satellite Admin Center

Allows users set up and administer a group of DB2 Servers

Sequences

Sequences, identity columns and triggers can be used to generate values for a column. But, only sequences can be referenced in an INSERT statement.

SET operators

A set operator is is used to combined two or more queries into a single query. Examples: UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL

Spatial Extenders

Spatial extender treats the world as flat map; the DB2 geodetic extender treats the world as a globe.

SQL Performance Monitor 

To analyse database operations performed againsts a DB2 for i5/OS database

Storage

Char = (number of characters * 1) bytes required; 1 and 254 characters

Varchar = (number of characters + 4) bytes requires; 32,672 characters

LONG VARCHAR = (number of characters + 24) bytes required; 32,672 characters (table space agnostic)

GRAPHIC = (number of characters * 2) bytes required; 127 characters

VARGRAPHIC = (number of characters * 2) + 4 bytes required; 16, 336

LONG VARGRAPHIC = (number of characters * 2) + 24 bytes required; 16,350 characters (table space agnostic)

Structured Data Type

A structured data type is a user-defined type that contains one or more attributes, each of which has a name and a data type of its own.  A SDT can also be created as a subtype of another structured type.   SDT are created by the CREATE TYPE sql statement.

Table Locks

Share mode - Other transactions are allowed read data but not change the data that is locked.

Exclusive mode - Other transactions can neither read nor modify the data that is locked.

Task Center

Allows users schedule, run tasks and send notifications about them

Time / Timestamp

Timestamp can store date time. Time can only store time.

Triggers

• A trigger can be activated whenever an insert, update or delete operation is performed (not a select).
• A trigger event can be activated before, after or instead of the trigger event
• Trigger granularity: They can be activated for every row updated (FOR EACH ROW) or just for every statement (FOR EACH STATEMENT)
• To stop trigger events setting off other triggers use the NO CASCADE option
• A trigger event can reference old or new data using the 'REFERENCE OLD AS' or 'REFERENCE NEW AS' syntax
• A trigger can send signals.This can be used to prevent actions, for example: SIGNAL SQLSTATE '75002' SET MESSAGE TEXT 'Deletes not allowed'.
• The SQL used to create Triggers is stored in the system catalog.

Typed tables

User defined tables whose column definitions are based on the attributes of a user defined structured data type.

Universal Developer's Edition

Contains tools to build application supported Linux, UNIX, Windows and DRDA Application Requestor

View Tables

Do not contain real data but instead refer to data in real tables. Only the view definition itself is stored in the database.

Useful for controlling access to data.   The WITH LOCAL CHECK OPTION can be used to enforce data constraints for inserts, updates.

The WITH CASCADED CHECK OPTION can be used to cascade constraints to subsequent views.  Characteristics of View tables are stored in the system catalog not the SQL that created them.

Visual Explain

Gives visual representation of data access plan

XML Columns

XML columns are used to store documents as a hierarchial set of entities. The XML data type does not have a fixed length.

P.S.  Well done Seán O'Brien on another super performance! Well done Ireland beating Italy and making the World Cup Quarter finals!

Seán O'Brien

SQL or NOSQL that is the question?

So what's the deal with NoSQL?

Is NoSQL just a controversial buzzword? Could you imagaine if the term 'Object Orientated' didn't exist and instead architectures based on concepts such as encapsulation, polymorphism and inhertiance were referred to as 'NoProcedural'?  Could you imagine if .net was called 'NoJava'?  Leinster was called 'NoMunster'?

Well controversial name aside,  a good way to appreciate the hype about NoSQL is to consider scalability - the classical non-functional architectural concern. In a classical OLTP architecture, when load increases and your JVM is under pressure, you need to scale.  You have two choices:

• vertical scaling - adding more CPU power to your JVM
• horizontal scaling - adding more JVMs (usally one more boxes)

It's generally never any problem scaling the business tier horizontally. Follow J2EE / JEE specs and unless you've done something crazy your business tier will scale.  Just add more JVMs and load balance between them.  However, while the business tier may be straighforward, the persistence tier ain't so easy.   Let's say you are using a classical relational database (such as MySQL, SQLServer, DB2 or Oracle) for your persistence, you can't just add database machines like you can add JVMs.  Why not?  Imagine trying to do SQL joins when tables are on the same machine and when the tables are on different machines! Imagine trying to do maintain ACID characteristics for your transactions when your database is split across various CPUs?  Now think trying to do all that on 5 machines, 50 , 500, 5000 machines?  The more machines the harder it gets.

The leading relational databases will scale horizontally.  But only by so much.  To get around this an architect usually will consider:

• Scaling vertically - putting the database on the best hardware that can be afforded
• Partitioning out legacy data and thus reduce things like the size of index tables. This will boost performance and put less pressure on the need to scale
• Remove the amount of pressure on the database by caching more in the business tier
• Pay a DBA a lot of money!

But what if you just run out of all possible database optimisations options and you have to scale horizontally? Not just to a few machines but to a few hundred if not thousand. This is where NoSQL architectures become relevant.

With a NoSQL database there is no strict schema.  Everything is effectively collapsed into one very fat table - a bit like an old skool flat file, but where each row stores a huge amout of data.   So, instead of having a table for Users and a table for Activities (representing User's activities), you put all the User information together in one fat row. This means there are no joins across tables.  It also means there is a lot of data redundancy which means more storage space required. In addition, more computational power will be needed for writes. But because data that is used together is located at the very same place - within the same row - it means no complex joins and hence it is easier to scale. The computational requirement for reads is also less.  So reads can go faster.

Another advantage of NoSQL databases is derived from the freedom that comes with not having to be tied to strict schema.  You know that headache where a change to a data model  can cause big problems? Well since there is no strict schema with NoSQL - this problem does not exist.  This makes the architecture more flexible and more extensible.

Right now, it's fair to say NoSQL is only relevant in the minority of architectures. But could this be another case of technical innovation driving business innovation as we have seen with smart phones?  There wasn't a need for smart phones but the technical innovation provided business opportunities. I think the same could happen with NoSQL Architectures.

Take a step back from Computer Science and just think Science.  Science used to be hypotheisis centric, now it is becoming more and more data centric. CERN, genome sequencing, climate change analysis - all involve tonnes and tonnes of data. Surely NoSQL architectures allied with searching technologies such as MapReduce / Hadoop will open up new ways to do Science?

So any disadvantages with NoSQL architectures? Well it's still an immature technology. Indexing, Security models are just not as sophisticated as they are with classical relational databases. And because most of it is coming from the open source community the support is not as good as it is for relational databases.  So don't throw out your SQL just yet!

PS Well done Dublin and winning the All Ireland!

References:
1. http://about.digg.com/blog/looking-future-cassandra
2. http://www.techrepublic.com/blog/10things/10-things-you-should-know-about-nosql-databases/1772
3. http://nosqltapes.com/