Tag: SQL Server

SQL Server Length Function Equivalent

Advertisements

The purpose of the Length function in SQL

The SQL LENGTH function returns the number of characters in a string. The LENGTH function is available in many Database Management Systems (DBMS).

The LENGTH Function Syntax

  • LENGTH(string)

LENGTH Function Notes

  • If the input string is empty, the LENGTH returns 0.
  • If the input string is NULL, the LENGTH returns NULL.

Length Function Across Databases

When working as a technical consultant, one has to work with customer’s databases and as you move from one database to another you will find that the function commands may vary–assuming the database has an equivalent function.

Working with VQL and SQL Server got me thing about the LENGTH() function, so, here is a quick references list, which does include the SQL Server.  

IBM DB2

  • LENGTH( )

IBM Informix

  • CHAR_LENGTH() Or CHARACTER_LENGTH()

MariaDB

  • LENGTH( )

Microsoft SQL Server

  • LEN( )

MySQL

  • CHAR_LENGTH() Or CHARACTER_LENGTH()

Netezza

  • LENGTH( )

Oracle

  • LENGTH( )

PostgreSQL

  • CHAR_LENGTH() Or CHARACTER_LENGTH()

SOQL (SalesForce)

  • SOQL has no LENGTH function

VQL (Denodo)

  • LEN( )

Technology – Denodo Data Virtualization Project Roles

Advertisements

A Denodo virtualization project typically classifies the project duties of the primary implementation team into four Primary roles.

Denodo Data Virtualization Project Roles

  • Data Virtualization Architect
  • Denodo Platform Administrator
  • Data Virtualization Developer
  • Denodo Platform Java Programmer
  • Data Virtualization Internal Support Team

Role To Project Team Member Alignment

While the denodo project is grouped into security permissions and a set of duties, it is import to note that the assignment of the roles can be very dynamic as to their assignment among project team members.  Which team member who performs a given role can change the lifecycle of a denodo project.  One team member may hold more than one role at any given time or acquire or lose roles based on the needs of the project.

Denodo virtualization Project Roles Duties

Data Virtualization Architect

The knowledge, responsibilities, and duties of a denodo data virtualization architect, include:

  • A Deep understanding of denodo security features and data governance
  • Define and document5 best practices for users, roles, and security permissions.
  • Have a strong understanding of enterprise data/information assets
  • Defines data virtualization architecture and deployments
  • Guides the definition and documentation of the virtual data model, including, delivery modes, data sources, data combination, and transformations

Denodo Platform Administrator

The knowledge, responsibilities, and duties of a Denodo Platform Administrator, Include:

  • Denodo Platform Installation and maintenance, such as,
    • Installs denodo platform servers
    • Defines denodo platform update and upgrade policies
    • Creates, edits, and removes environments, clusters, and servs
    • Manages denodo licenses
    • Defines denodo platform backup policies
    • Defines procedures for artifact promotion between environments
  • Denodo platform configuration and management, such as,
    • Configures denodo platform server ports
    • Platform memory configuration and Java Virtual Machine (VM) options
    • Set the maximum number of concurrent requests
    • Set up database configuration
      • Specific cache server
      • Authentication configuration for users connecting to denodo platform (e.g., LDAP)
      • Secures (SSL) communications connections of denodo components
      • Provides connectivity credentials details for clients tools/applications (JDBC, ODBC,,,etc.)
      • Configuration of resources.
    • Setup Version Control System (VCS) configuration for denodo
    • Creates new Virtual Databases
    • Create Users, roles, and assigns privileges/roles.
    • Execute diagnostics and monitoring operations, analyzes logs and identifies potentials issues
    • Manages load balances variables

Data Virtualization Developer

The Data Virtualization Developer role is divided into the following sub-roles:

  • Data Engineer
  • Business Developer
  • Application Developer

the knowledge, responsibilities, and duties of a Denodo Data Virtualization Developer, by sub-role, Include:

Data Engineer

The denodo data engineer’s duties include:

  • Implements the virtual data model construction view by
    • Importing data sources and creating base views, and
    • Creating derived views applying combinations and transformations to the datasets
  • Writes documentation, defines testing to eliminate development errors before code promotion to other environments

Business Developer

The denodo business developer’s duties include:

  • Creates business vies for a specific business area from derived and/or interface views
  • Implements data services delivery
  • Writes documentation

Application Developer

The denodo application developer’s duties include:

  • Creates reporting vies from business views for reports and or datasets frequently consumed by users
  • Writes documentation

Denodo Platform Java Programmer

The Denodo Platform Java Programmer role is an optional, specialized, role, which:

  • Creates custom denodo components, such as data sources, stored procedures, and VDP/iTPilot functions.
  • Implements custom filters in data routines
  • Tests and debugs any custom components using Denodo4e

Data Virtualization Internal Support Team

The denodo data virtualization internal support team’s duties include

  • Access to and knowledge of the use and trouble of developed solutions
  • Tools and procedures to manage and support project users and developers

denodo Virtualization – Useful Links

Advertisements
  • denodo Virtualization

Here are some denodo Virtualization references, which may be useful.

Reference Name Link
denodo Home Page https://www.denodo.com/en/about-us/our-company
denodo Platform 7.0 Documentation https://community.denodo.com/docs/html/browse/7.0/
denodo Knowledge Base and Best Practices https://community.denodo.com/kb/
denodo Tutorials https://community.denodo.com/tutorials/
denodo Express 7.0 Download https://community.denodo.com/express/download
Denodo Virtual Data Port (VDP) https://community.denodo.com/kb/download/pdf/VDP%20Naming%20Conventions?category=Operation
JDBC / ODBC drivers for Denodo https://community.denodo.com/drivers/
Denodo Governance Bridge – User Manual https://community.denodo.com/docs/html/document/denodoconnects/7.0/Denodo%20Governance%20Bridge%20-%20User%20Manual
Virtual DataPort VQL Guidehttps://community.denodo.com/docs/html/browse/7.0/vdp/vql/introduction/introduction
Denodo Model Bridge – User Manualhttps://community.denodo.com/docs/html/document/denodoconnects/7.0/Denodo%20Model%20Bridge%20-%20User%20Manual
Denodo Connects Manualshttps://community.denodo.com/docs/html/browse/7.0/denodoconnects/index
Denodo Infosphere Governance Bridge – User Manualhttps://community.denodo.com/docs/html/document/denodoconnects/7.0/Denodo%20Governance%20Bridge%20-%20User%20Manual

PostgreSQL – Useful Links

Advertisements
  • PostgreSQL

PostgreSQL – Useful Links

Here are some PostgreSQL references, which may be useful.

Reference Type Link
PostgreSQL Home page & Download https://www.postgresql.org/
PostgreSQL Online Documentation https://www.postgresql.org/docs/manuals/
Citus – Scalability and Parallelism Extension https://github.com/citusdata
Free PostgreSQL Training https://www.enterprisedb.com/free-postgres-training
PostgreSQL Wiki https://wiki.postgresql.org/wiki/Main_Page

The Business Industries Using PostgreSQL

Advertisements
  • PostgreSQL

PostgreSQL is an open-source database, which was released in 1996. So, PostgreSQL has been around a long time.   So, among the many companies and industries which know they are using PostgreSQL, many others are using PostgreSQL and don’t know it because it is embedded as the foundation in some other application’s software architecture.  

I hadn’t paid much attaint to PostgreSQL even though it as been on the list leading databases used by business for years.  Mostly I have been focused on the databases my customer were using (Oracle, DB2, Microsoft SQL Server, and MySQL/MariaDB).  However, during a recent meeting I was surprised to learn that io had been using and administering PostrgresSQL embedded as part of another software vendors application, which made me take the time to pay attention to PostgreSQL. Especially, who is using PostgreSQL and what opportunities that may provide for evolving my career? 

The Industries Using PostgreSQL

According to enlyft, the major using the PostgreSQL are Computer Software and Information Technology And services companies.  

  PostgreSQL Consumers Information

Here is the  link to enlyft page, which provides additional information companies and industries using PostgreSQL:

SQL Server – how to know when a stored procedure ran last

Advertisements

Microsoft SQL Server 2017

This week I needed to know if a stored procedure was running when expected during our batch.  So, here is a quick couple of SQL to answer the question:

When a Stored Procedure was run last

This version of the SQL gives the date for the last time the Stored Procure was run:

select distinct   top 1     s.last_execution_time

from  sys.dm_exec_query_stats s

cross apply sys.dm_exec_query_plan (s.plan_handle) p

where  object_name(p.objectid, db_id(‘<<DATABASE_NAME>>’)) = ‘<<STORED_PROCEDURE_NAME>>’

Order by s.last_execution_time desc

Get a list of when Stored Procedure has been run

This version of the SQL provides a list of dates of when Stored Procure has been run:

select distinct   s.last_execution_time

from  sys.dm_exec_query_stats s

cross apply sys.dm_exec_query_plan (s.plan_handle) p

where object_name(p.objectid, db_id(‘<<DATABASE_NAME>>’)) = ‘<<STORED_PROCEDURE_NAME>>’

Order by s.last_execution_time desc

 

 

Oracle TO_CHAR to SQL Server CONVERT Equivalents to change Date to String

Advertisements
  • Transact SQL (T-SQL)

When it comes to SQL I tend to lean on the SQL I have used the most over the years, which is Oracle.  Today was no exception, I found myself trying to use the TO_CHAR command in SQL Server to format a date, which of course does not work. So, after a little thought, here are some examples of how you can the SQL Server Convert Command the achieve the equivalent result.

  • Example SQL Server Date Conversion SQL

Example SQL Server Date Conversion SQL Code

This SQL of examples runs, as is, no from table required.

Select
CONVERT(VARCHAR(10), GETDATE(), 20) as
‘YYYY-MM-DD’
,CONVERT(VARCHAR(19), GETDATE(), 20) as ‘YYYY-MM-DD HH24:MI:SS’
,CONVERT(VARCHAR(8), GETDATE(), 112) as YYYYMMDD
,CONVERT(VARCHAR(6), GETDATE(), 112) as YYYYMM
,CONVERT(VARCHAR(12), DATEPART(YEAR, GETDATE()))+ RIGHT(‘0’+CAST(MONTH(GETDATE()) AS VARCHAR(2)),2)
as
YYYYMM_Method_2
,CONVERT(VARCHAR(4), GETDATE(), 12) as YYMM
,CONVERT(VARCHAR(4), GETDATE(), 112) as YYYY
,CONVERT(VARCHAR(4), DATEPART(YEAR, GETDATE())) as YYYY_Method_2
,CONVERT(VARCHAR(4), YEAR(GETDATE())) as YYYY_Method_3
,RIGHT(‘0’+CAST(MONTH(GETDATE()) AS VARCHAR(2)),2) as Two_Digit_Month
,SUBSTRING(ltrim(CONVERT(VARCHAR(4), GETDATE(), 12)),3,2) as Two_Digit_Month_2
,CONVERT(VARCHAR(10), GETDATE(), 111) as ‘YYYY/MM/DD’
,CONVERT(VARCHAR(5), GETDATE(), 8) as ‘HH24:MI’

Map TO_CHAR formats to SQL Server

You can map an Oracle TO_CHAR formats to SQL Server alternative commands as follows:

TO_CHAR
String

VARCHAR
Length

SQL
Server Convert Style

YYYY-MM-DD

VARCHAR(10)

20,
21, 120, 121, 126 and 127

YYYY-MM-DD
HH24:MI:SS

VARCHAR(19)

20,
21, 120 and 121

YYYYMMDD

VARCHAR(8)

112

YYYYMM

VARCHAR(6)

112

YYMM

VARCHAR(4)

12

YYYY

VARCHAR(4)

112

MM

VARCHAR(2)

12

YYYY/MM/DD

VARCHAR(10)

111

HH24:MI

VARCHAR(5)

8,
108, 14 and 114

HH24:MI:SS

VARCHAR(8)

8,
108, 14 and 114

Translating the formats commands

Here are some example of translating the formats commands.

Format

SQL
Server

YYYY-MM-DD

CONVERT(VARCHAR(10),
GETDATE(), 20)

YYYY-MM-DD
HH24:MI:SS

CONVERT(VARCHAR(19),
GETDATE(), 20)

YYYYMMDD

CONVERT(VARCHAR(8),
GETDATE(), 112)

YYYYMM

CONVERT(VARCHAR(6),
GETDATE(), 112)

YYMM

CONVERT(VARCHAR(4),
GETDATE(), 12)

YYYY

CONVERT(VARCHAR(4),
GETDATE(), 112)

YYYY

CONVERT(VARCHAR(4),
DATEPART(YEAR, GETDATE()))

YYYY

CONVERT(VARCHAR(4),
YEAR(GETDATE()))

MM

RIGHT(‘0’+CAST(MONTH(GETDATE())
AS VARCHAR(2)),2)

MM

SUBSTRING(ltrim(CONVERT(VARCHAR(4),
GETDATE(), 12)),3,2)

YYYY/MM/DD

CONVERT(VARCHAR(10),
GETDATE(), 111)

HH24:MI

CONVERT(VARCHAR(5),
GETDATE(), 8)

HH24:MI:SS

CONVERT(VARCHAR(8),
GETDATE(), 8)

Related Reference

Microsoft Docs, SQL, T-SQL Functions, GETDATE (Transact-SQL)

Microsoft Docs, SQL, T-SQL Functions, Date and Time Data Types and Functions (Transact-SQL)

Microsoft Docs, SQL, T-SQL Functions, DATEPART (Transact-SQL)

SQL server table Describe (DESC) equivalent

Advertisements
Transact SQL (T-SQL)

Microsoft SQL Server doesn’t seem have a describe command and usually, folks seem to want to build a stored procedure to get the describe behaviors.  However, this is not always practical based on your permissions. So, the simple SQL below will provide describe like information in a pinch.  You may want to dress it up a bit; but I usually just use it raw, as shown below by adding the table name.

Describe T-SQL Equivalent

Select *

 

From INFORMATION_SCHEMA.COLUMNS

Where TABLE_NAME = ‘<<TABLENAME>>’;

Related References

Microsoft SQL Server – Useful links

Advertisements
Microsoft SQL Server 2017

Here are a few references for the Microsoft SQL Server 2017 database, which may be helpful.

Table Of Useful Microsoft SQL Server Database References

Reference Type

Link

SQL Server 2017 Download Page

https://www.microsoft.com/en-us/sql-server/sql-server-downloads

SQL SERVER version, edition, and update level

https://support.microsoft.com/en-us/help/321185/how-to-determine-the-version–edition-and-update-level-of-sql-server-a

SQL Server 2017 Release Notes

https://docs.microsoft.com/en-us/sql/sql-server/sql-server-2017-release-notes

SQL Server Transact SQL Commands

https://technet.microsoft.com/en-us/library/ms189826(v=sql.90).aspx

Related References

Netezza / PureData – How to Number for day of week in SQL?

Advertisements

Netezza / PureData – Numeric Day of Week

I had reason today to get the number of the day of the week, in PureData / Netezza, which I don’t seem to have discussed in previous posts.  So, here is a simple script to get the number for the day of week with a couple of flavors, which may prove useful.

Basic Format

select extract(dow from <<FieldName>>) from <<SchemaName>>.<<tableName>>

Example SQL

 

SELECT

CURRENT_DATE

,  TO_CHAR(CURRENT_DATE,’DAY’) AS DAY_OF_WEEK

—WEEK STARTS ON MONDAY

,  EXTRACT(DOW FROM CURRENT_DATE)-1 AS DAY_OF_WEEK_NUMBER_STARTS_ON_MONDAY

—WEEK STARTS ON SUNDAY

,  EXTRACT(DOW FROM CURRENT_DATE) AS DAY_OF_WEEK_NUMBER_STARTS_ON_SUNDAY

—WEEK STARTS ON SATURDAY

,  EXTRACT(DOW FROM CURRENT_DATE)+1 AS DAY_OF_WEEK_NUMBER_STARTS_ON_SATURDAY

FROM _V_DUAL;

 

Related References

Extract date and time values

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Functions and operators, Functions, Extract date and time values

Date/time functions

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Date/time functions

Template patterns for date/time conversions

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Conversion functions, Template patterns for date/time conversions

https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.dbu.doc/r_dbuser_ntz_sql_extns_templ_patterns_date_time_conv.html

SQL Server – Basic SQL Server Object Naming Convention Guidance

Advertisements
  • Microsoft

Microsoft doesn’t provide macro guidance for naming convention, however, sometimes it is useful to have a place to start.  Also, there are times when flexibility with naming conventions are necessary.  So, here is a quick set of SQL Server naming conventions, which may be helpful if you find yourself working with a customer who doesn’t have an established set of naming convention standards and you need to assemble a set fast.

Basic SQL Server Object Naming Convention Guidance

  1. Each project will have its own schema.
  2. Schema represents the project
  3. First letter of each word in table/Column starts with Uppercase.
  4. Put Underscore(_) between words of table/Column name
  5. No space between words in table/column name

Object Conventions

SQL Server objects

Subject

Prefix/Suffix

Naming convention

Example

Databasedbdb<name>dbODS
Jobjbjb<Jobname>jbSSIS_RunLoadPackage
Linked Serverlsls<linkedservername>lsServer01
Trigger(Delete)tdtd<name>tdAppointment
Trigger(Insert)titi<name>tiCustomer
Trigger(Update)tutu<name>tuAppointment

SQL Server Database objects

Subject

Prefix/Suffix

Naming convention

Example

Aggregate functionafaf<Name>afTotalSales
Assembliesasass<Name>assDetermineIPs
Attribuut–<name>PatientName
Database data file(s)data#<database>_data#dbAdventureWorks_data1
Database log filelog<database>_logdbAdventureWorks_log
Defaultdfdf<name>dfDate
Dimension Table _Dim<<Table Name>>_DimGeography_Dim
Fact Table_Fact<<TableName>>_FactAccounts_Payable_Fact
Filegroupfgfg<name>fgdbAdventureWorks_Index
Filestreamfsfs<name>fsInvoiceDoc
Foreign keyFKx_FKx_<sourcetable>_<goaltable>_<fieldname(s)># 
Index (not unique, non clustered)IDX_IDX_<name>_<column> 
Index (unique, non clustered)UDX_UDX_<name>_<column> 
Index (not unique, clustered)CIX_CIX_<name>_<column> 
Index (unique, clustered)CUX_CUX_<name>_<column> 
Materialized ViewMV_MV_<Table name> 
Primary keyPK_PK_<name>PK_KlantID
Scalar valued functionscfscf<name>scfDetermineNumberOfCustomers
Schemaschsch<name>schOrders
Stored procedureuspusp<projectname>_<name>uspArchive_DetermineNumberOfOrders
Synonymssynsyn<name> 
Table tbltbl<name>tblCustomers
Table Valued functiontvftvf<name>tvfDeterminecustomers
Temporary Table#tmp#tmp<name>#tmpPatient25Years
Typeuddudd<name>uddPostcode
Typetptp<name>tpPosInt
Viewvw_VW_<table name>vwGetOpenOrders

Transact SQL (T-SQL)

Subject

Prefix/Suffix

Naming convention

Example

Bigintinbinb<Variabelename>inbPatientId
Binarybinbin<Variabelename>binMessage
Bitbitbit<Variabelename>bitOK
Charchrchr<Variabelename>chrPatientname
Code (Field)_Code<Variabelename>_CodeProgram_Code
Cursorcurcur<Variabelename>curField
Date (Field)_Dt<Variabelename>_DtCreated_Dt
Datetimedtmdtm<Variabelename>dtmInsertDate
Description (Field)_Desc<Variabelename>_DescPlan_Desc
Floatfltflt<Variabelename>fltXvalue
Identity (Field) _ID<Variabelename>_IDStatus_Rollup_ID
Integerintint<Variabelename>intPatientID
Moneymnymny<Variabelename>mnyTotalAmount
Ncharchrnchrn<Variabelename>chrnPatientname
Ntexttxtntxtn<Variabelename>txtMessage
Numeric of decimaldecdec<Variabelename>decAmount
Nvarcharchvnchvn<Variabelename>chvnPatientname
Realrearea<Variabelename>reaAmount
Smalldatetimedtsdts<Variabelename>dtsInsertDate
Smallintinsins<Variabelename>insSubcategorieID
Smallmoneymnsmns<Variabelename>mnsAmount
Sql_variantvarvar<Variabelename>varFreevalue
Surrogate key (Field) _ID<Variabelename>_IDPerson_ID
Table variabletavtav<name>tavCustomers
Texttxttxt<Variabelename>txtReport
Timestamptsptsp<Variabelename>tspDeleteDate
Tinyintinyiny<Variabelename>inyCategorieID
Uniqueidentifierguidguid<Variabelename>guidKeu
Varbinarybivbiv<Variabelename>bivMessage
Varcharchvchv<Variabelename>chvPatientname

Netezza – JDBC ISJDBC.CONFIG Configuration

Advertisements
  • JDBC ( Java Database Connectivity)

This jdbc information is based on Netezza (7.2.0) JDBC for InfoSphere Information Server11.5.  so, here are a few pointers for building an IBM InfoSphere Information Server (IIS) isjdbc.config file.

Where to place JAR files

For Infosphere Information Server installs, as a standard practice, create a custom jdbc file in the install path.  And install any download Jar file not already installed by other applications in the jdbc folder. Usually, jdbc folder path looks something like this:

  • /opt/IBM/InformationServer/jdbc

CLASSPATH

  • nzjdbc3.jar
  • Classpath must have complete path and jar name

CLASS_NAMES

  • netezza.Driver

JAR Source URL

IBM Netezza Client Components V7.2 for Linux

File name

  • nz-linuxclient-v7.2.0.0.tar.gz

Unpack tar.gz

  • tar -zxvf nz-linuxclient-v7.2.0.0.tar.gz -C /opt/IBM/InformationServer/jdbc

DB2 DEFAULT PORT

  • 1521

JDBC URL FORMAT

  • jdbc:netezza://:/

JDBC URL EXAMPLE

  • jdbc:netezza://10.999.0.99:5480/dashboard

isjdbc.config EXAMPLE

CLASSPATH=usr/jdbc/nzjdbc3.jar;/usr/jdbc/nzjdbc.jar;/usr/local/nz/lib/nzjdbc3.jar;

CLASS_NAMES= org.netezza.Driver;

Isjdbc.config FILE PLACEMENT

  • /opt/IBM/InformationServer/Server/DSEngine

Related References

SQL Server JDBC ISJDBC.CONFIG Configuration

Advertisements
  • JDBC ( Java Database Connectivity)

This jdbc information is based on Oracle Database 11g Release 2, (11.2.0.4) JDBC for InfoSphere Information Server11.5, and ReedHat Linux 6.  so, here are a few pointers for building an IBM InfoSphere Information Server (IIS) isjdbc.config file.

Where to place JAR files

For Infosphere Information Server installs, as a standard practice, create a custom jdbc file in the install path.  And install any download Jar file not already installed by other applications in the jdbc folder. Usually, jdbc folder path looks something like this:

  • /opt/IBM/InformationServer/jdbc

CLASSPATH

  •  sqljdbc.jar
  •  sqljdbc4.jar
  •  sqljdbc41.jar
  •  sqljdbc42.jar
  • Classpath must have complete path and jar name

CLASS_NAMES

  • microsoft.jdbc.sqlserver.SQLServerDriver;com.microsoft.sqlserver.jdbc

JAR Source URL

DEFAULT PORT

  • 1433

JDBC URL FORMAT

  •  jdbc:microsoft:sqlserver://HOST:1433;DatabaseName=DATABASE

JDBC URL EXAMPLE

  • jdbc:sqlserver://RNO-SQLDEV-SVR1DEV01:55198;databaseName=APP1;

isjdbc.config EXAMPLE

CLASSPATH=/opt/IBM/InformationServer/jdbc/sqljdbc_3.0/enu/sqljdbc4.jar;/opt/IBM/InformationServer/jdbc/sqljdbc_3.0/enu/sqljdbc.jar;/opt/IBM/InformationServer/jdbc/sqljdbc_3.0/enu/sqljdbc41.jar;/opt/IBM/InformationServer/jdbc/sqljdbc_3.0/enu/sqljdbc42.jar;

CLASS_NAMES=com.microsoft.jdbc.sqlserver.SQLServerDriver;com.microsoft.sqlserver.jdbc

Isjdbc.config FILE PLACEMENT

  • /opt/IBM/InformationServer/Server/DSEngine

Related References

Vendor Reference Link:

Technology – Structured Query Language (SQL) Tuning

Advertisements

Tuning SQL is one of those skills, which is part art and part science.  However, there are a few fundamental approaches, which can help ensure optimal SQL select statement performance.

Structuring your SQL

By Structuring SQL Statements, much performance can be gained through good SQL statement organization and sound logic.

Where Clause Concepts:

Use criteria ordering and Set Theory thinking.  SQL  can be coupled with set-theory to aid conception of the operations being conducted. Order your selection criteria to execute criteria which arrives at the smallest possible row set first. Doing so reduces the volume of rows to be processed by follow-on operations. This does require an understanding of the data relationships to be effective.

  • SQL First Select Criteria Pie Chart

Join Rules (equijoins, etc.)

When  constructing your joins, consider these rules:

  • Join on keys and indexed columns: The efficiency of your program improves when tables are joined based on indexed columns, rather than on non-indexed ones.
  • Use equijoins (=), whenever possible
  • Avoid using of sub-queries
  • Re-write EXISTS and NOT EXISTS subqueries as outer joins
  • Avoid OUTER Joins on fields containing nulls
  • Avoid RIGHT OUTER JOINS: Always select FROM your primary table (or derived table) and LEFT OUTER JOIN to auxiliary tables.
  • Use Joins Instead of Subqueries: A join can be more efficient than a correlated subquery or a subquery using IN. Use caution when specifying ORDER BY with a Join: When the results of a join must be sorted, limiting the ORDER BY to columns of a single table can cause the database to avoid a sort.
  • Provide Adequate Search Criteria: When possible, provide additional search criteria in the WHERE clause for every table in a join. These criteria are in addition to the join criteria, which are mandatory to avoid Cartesian products

Order of Operations SQL & “PEMDAS”

To improve your SQL, careful attention needs to be paid to the mathematical order of operations; especially, parentheses since they not only set the order of operation but also the boundaries of each subset operation.

  • PEMAS is “Parentheses, Exponents, Multiplication and Division, and Addition and Subtraction”.
  • Use parentheses () to group and specify the order of execution. SQL observes the normal rules of arithmetic operator precedence.
PrecedenceOperator(s)Operation(s)Notes
1( )ParenthesesIf the parentheses are nested, the expression in the innermost pair is evaluated first. If there are several un-nested parentheses, then parentheses are evaluated left to right.
2*
/
%		Multiplication
Division
Modulus		If there are several, evaluation is left to right.
3+
–		Addition
Subtraction		If there are several, evaluation is left to right.

Index Leveraging (criteria ordering, hints, append, etc.)

  • Avoid Full Table Scans: within the scope of a SQL statement, there are many conditions that will cause the SQL optimizer to invoke a full-table scan. 
  • Avoid Queries:
    • with NULL Conditions (Is NUll, Is Not NUll)
    • Against Unindexed Columns
    • with Like Conditions
    • with Not Equals Condition (<>, !=, not in)
    • with use built-in Function (to_char, substr, decode, UPPER)
  • Use UNION ALL instead of UNION if business rules allow
    • UNION: Specifies that multiple result sets are to be combined and returned as a single result set. Query optimizer performs extra work to return to avoid duplicate rows.
    • UNION ALL: Incorporates all rows into the results. This includes duplicates. Query optimizer just needs to concatenate the result sets with no extra work
  • Use stored procedures instead of ad hoc queries when possible. Stored procedures are precompiled and cached
  • Avoid cursor use when possible
  • Select only the rows needed
  • Use NOLOCK hint in the select statement to avoid blocking
  • Commit transactions in smaller batches
  • Whenever possible use tables instead of views
  • Make sure comparison columns whether using JOIN or WHERE clause are exactly the same data type. For example, if we are comparing Varchar column to nchar columns the query optimizer has to do a CONVERT before comparing the values

Note: You do not necessarily need to remove all full table scans from your query’s execution plan. Tables with few rows, few columns, or thin columns may fit into few database blocks. In this case, a full table scan will always be the most efficient access