Best Practices for using the Structured Query Language (SQL)
SQL (Structured Query Language) is an exceptionally high-performing database, universally accepted for its simplicity and well developed querying features that makes it distinct from its competitors in the industry. It facilitates the user with a structured organization of table structures and a relatively simple English language to query them.
SQL holds its firm position in the software field not merely by its querying capabilities but due to additional qualities like publication, subscription, merged application, cloning, transition, replication, job scheduler, index and defragmentation. These are some of the best practices associated with SQL whose clear understanding enhances an easy handling of the database. Brief explanations of all these terms are provided below.
Publication:
Publication is an essential top-acted feature of SQL Server. This enables article definition. Publication can be created through the publication wizard. Steps involved in publishing are:
Open SQL Server Management studio, connect to publisher.
Right-click local publications from replication folder.
Click New Publication.
Finally, follow the instructions in the publication wizard to do required activities like naming the publication, specify database objects etc.
After creating publications, modifications can be performed through the article properties. Articles can be defined for transactional or merged publication. This uses certain stored procedures to complete the action. A few stored procedures involved in article publication are:
sp_addarticle (Creates an article and adds it to a publication)
sp_articlefilter (Filters data that are published based on a table article)
sp_articlecolumn (specifies columns included in an article to vertically filter data in a published table)
sp_articleview (Creates the view that defines the published article when a table is filtered vertically or horizontally)
sp_helppublication (Returns information about a publication)
sp_refreshsubscriptions (Add subscriptions to new articles)
Subscription:
Subscription permits the site database information to access SQL Server replica located on the SQL Server computer which acts as the publisher.
Steps involved in subscribing to a SQL server are as follows:
In the SQL Server 2005 SQL Server Management Studio, click Replication, right click Local Subscriptions and click New Subscriptions.
Click Next on the New Subscription Wizard.
Select Find SQL Server Publisher from Publisher and connect to the remote SQL Server computer. Select the publication created for the site database.
On the Distribution Agent Location page, select Run each agent.
On the Subscribers page, select the database created to host the site database replica from the Subscription Database list.
On the Distribution Agent Security page, enter the security account settings
Select Define schedule from the Agent Schedule list and set the Frequency to occur Daily and set it to approximately 15 minutes.
On the Initialize Subscriptions page, select the defaults and click next.
On the Wizard Actions page, accept the default to create the subscription and click next.
On the Complete Wizard page, review the subscription settings and click Finish.
On the Creating Subscription page, click close after the subscription has completed successfully.
Merged application:
Merge application in SQL has its contributions to data warehousing ETL cycles where updating and insertion have to be performed on multiple tables. Scenarios like inserting a record if it does not exist and updating an existing record called the UPSERT, is easily implemented by the SQL merge in a single query reducing the complexity of programming to a large extent. A merge statement performs insertion and update in a single statement.
Cloning:
Cloning as the name indicates is creating an exact clone or copy of a table. Not only is a copy produced but it needs to hold within indexes, default values and so on. The best way to implement cloning on a table is to follow three simple steps:
Get the complete structure of the table which needs a clone of itself.
Rename this table and create another table as required.
Now a clone of the previous table is produced. Execute INSERT INTO and SELECT if data has to be copied from an old table.
Cloning is one of the best SQL practices as an enormous database system like data warehouses will always require multiple tables with same structures. A cloning procedure renders these tables in a hassle-free way.
Transition:
With upgraded versions of 64-bit SQL servers present, a transition from 32-bit SQL sweeps into the minds of SQL users. The first thing to check out is the availability of supporting drivers and linked servers. A 64-bit SQL Server cannot use 32-bit ODBC/OLEDB drivers. Hence, ensure that there are 64-bit drivers available for whatever data sources required for connection. Also check for any DLL assembly being imported in the SQL server. Changing platforms will also affect the execution of all the existing managed database objects.
Replication:
SQL Replication describes creating replicas or mirror data of different databases. It enables creating replicas of objects within database still maintaining synchronization. It performs data distribution through remote networks including Local and Wide Area networks. It ensures redundancy and load-balancing distributing data equally among all databases.
SQL Server has 3 replication models namely Merge, Transactional and Snapshot Replication. The Merge replication comes into picture when Publisher and Subscriber make changes to databases. Using merge replication may give rise to conflicts though they follow best practices to resolve this conflict.
The Transactional replication is used in databases where information changes often and requires constant refreshing. This process checks publisher’s databases and if any changes are found, distributes them to the subscribers.
The Snapshot replication performs a snapshot of the publisher database at particular intervals of time and accordingly distributes data to subscribers. The Snapshot replication consumes a lot of time and resources. Hence it’s rarely used.
Job:
An SQL Server job is a collection of steps executed by SQL Server Agent in the database engine. Procedure to create SQL server jobs are:
In the Object Explorer, connect to an instance of the SQL Server Database Engine.
Expand SQL Server Agent.
Right-click Jobs and then click New Job.
On the General page, in the Name box, type a name for the job.
Clear the Enabled check box
Scheduler:
SQL Scheduler is a fully functional client/server application written in C# that allows administrators to schedule various SQL jobs for SQL Server Express and other versions of SQL Server. A schedule can be attached to an existing job by following a few easy steps.
In Object Explorer, connect to an instance of the SQL Server Database Engine
Click SQL Server Agent, expand Jobs, right-click the job that you want to schedule, and click Properties.
Select the Schedules page, click Pick.
Select the schedule to be attached, click OK.
In the Job Properties dialog box, double-click the attached schedule.
Set the date required for schedule and then click OK.
In the Job Properties dialog box, click OK.
Index:
SQL indexes are meant for performance tuning. Indexes speed up the querying process by providing swift access to rows in the data tables; they are created on columns within a table. They permit an easy access and retrieval of data by specifying it with a WHERE clause. An index is made up of a set of pages that are organized in a B-tree structure. This structure is hierarchical in nature, with the root node at the top of the hierarchy and the leaf nodes at the bottom. Indexes can be either clustered or nonclustered. A clustered index stores the actual data rows at the leaf level of the index. An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order. As a result, there can be only one clustered index on a table or view. In addition, data in a table is sorted only if a clustered index has been defined on a table. Nonclustered indexes cannot be sorted like clustered indexes; however, you can create more than one non-clustered index per table. The leaf nodes of a nonclustered index contain only the values from the indexed columns and row locators that point to the actual data rows, rather than data rows themselves.
In spite of easy retrieval of data, indexes are a performance drag when the time comes to modify records. Any time a query modifies the data in a table, the indexes on the data must also change. Achieving the right number of indexes will require testing and monitoring of your database to see where the best balance lies
Defragmentation:
Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Fragmented objects such as tables, indexes, files or databases exert a heavy hit on performance. Database information is fragmented. Space is allocated to records, tables, rows and columns within the database file in extents. To determine whether disk fragmentation exists, use system tools provided in Microsoft Windows or from third parties to analyze drives on which SQL Server databases reside. On small-scale environments with more conventional I/O subsystems, it is recommended that a correction of disk fragmentation is performed before running index defragmentation tools. On large-scale environments that benefit from more intelligent disk subsystems, such as SAN environments, correcting disk fragmentation is not necessary.
Implementing these SQL practices gets more acquainted with SQL and its features where extended capabilities of SQL can be utilized to a large extent by even new users.
