DATABASE

ByHariharan Rajendran

Database Table and Primary Key – Part 1

Creating a table in MS SQL Server is very easy, this can be achieved by using T-SQL scripts or GUI. This article will use T-SQL Scripts to a create a table.

Basic Pre-Requisites to create a table,

  1. Understand the purpose of the table
  2. Identify the columns and data types. Refer this link, to know about data types.

Table is the storage unit in a database which stores the data in rows and columns as like excel sheet. The output of the table result is similar to the excel sheet data.

If you are decided to store some data in a database then you must use table.

The basic table structure will look like below.

CREATE TABLE Users

(Id int)

We can add more columns in the structure, each column should be define with the right data type. Here Id is the column name and int is the data type which is nothing but an integer.

Let us discuss, what is a primary key and how to create it.

Primary key is a constraint which we need to define in any one of the column in a table. It will not allow null values in that defined column and entry to the column should be unique  i.e. it won’t allow duplicate values.

Choose an ID or Unique Identifier (GUID) column to create a primary key, this is the best way to define a table structure.

Create a table with primary Key on integer column,

CREATE TABLE Users

(Id int primary key,

Name varchar(50))

In the above script, ID column is considered as a primary key. You can check this by clicking the columns folder under dbo.Users table.

Create a table with primary key on unique identifier column,

CREATE TABLE Users

( Id uniqueidentifier primary key,

Name varchar(50))

ByDr. SubraMANI Paramasivam

What is XACT_ABORT in terms of implicit or explicit transactions?

When it comes to data modification statements for OLE DB systems, the XACT_ABORT option with ON and OFF functions helps the Developer/Administrator to decide based on the requirements from the business.

For example lets take a stored procedure that will complete fully, only if the UPDATE statements are completed. In this case the XACT_ABORT option should be set to ON like below.

SET XACT_ABORT ON;

GO

BEGIN TRANSACTION

By setting this XACT_ABORT option to ON, it does not affect trapping of any errors using TRY-CATCH, but it helps in a scenario where the commit or rollback was successful or not.

 

 

ByDr. SubraMANI Paramasivam

What is Hot, Warm & Cold Data?

We all work with Data everyday, but is everyone required to access every single data on a daily basis? Mostly it is a BIG NO. Because we don’t have to see what happened, 3 years ago but may be used to compare and that too bit rarely.

When your company ask for current week, last week, this month, last month, this quarter is what we talk about latest data and this becomes the hot topics and this is referred to HOT data.

In the below picture, I have given a simple example from Jan to Dec. Let’s say we are in Dec 15 and the data showing up for Oct, Nov, Dec is going to be much more latest data compared to Jan, Feb, Mar. The below picture gives you an ideal scenario on how you can segregate the Hot, Warm & Cold data.

Also HOT data is very heavily accessed but comparing the same with cold data is very less accessed. Also the data in recent months changes quite frequently compared to the very earlier months.

HotWarmCold_Chart

ByDr. SubraMANI Paramasivam

Configurations – Management Studio and sp_configure

Comparing these two there are far more configuration options available using sp_configure. sp_configure is nothing but a query run in a query window in a Management studio. But the server properties can be changed using this query and here are some of the examples.


Once sp_configure query is executed the following 16 options are displayed with its default values. By changing these values the advanced configurations can be set up here.

sp_configure

I will show the detailed options in my next post for most of them. Till then good luck on your search on my blog.

ByDr. SubraMANI Paramasivam

Create new SQL Server Job

Whatever developments you have done, may require a job to get it scheduled to ensure the data is always up to date for your reporting purpose. The schedule to update the job is achieved using SQL SERVER AGENT JOBS. Below is the screenshot that shows how you can create a new job. Open SSMS and connect to your server and navigate down to SQL Server Agent and expand it. Here you will find jobs and right click on it to get a new menu and choose “New Job…”

Create_Job_01

After this a new pop-up screen appears and you need to key in your new job name. I am using below screenshot from one my colleague’s computer and hence you see a different name on it.

Create_Job_02

In the same pop-up you have to go step by step tab in the left side. Go to steps and then provide the path to your SSIS package, but in the below example, I am giving an option to run SQL Script. And then go to schedules tab and choose how frequent you want this job to run, like hourly / daily / weekly / monthly / quarterly / yearly.

Create_Job_03

ByDr. SubraMANI Paramasivam

Dynamic Management Views

Microsoft have released many DMV & DMF for every release of its new SQL Server release. This has helped every administrator and developers a VERY BIG HAND in sorting out many problems. The days in writing our own scripts are now over and these DMV’s also provide support for almost all areas no matter whether it is to do with sessions, tables, indexes, Operating system, I/O. All are now taken care. Below I am providing some of the DMV’s that might be helpful for you.

Execution Related

• sys.dm_exec_connections
• sys.dm_exec_sessions
• sys.dm_exec_requests
• sys.dm_exec_cached_plans
• sys.dm_exec_query_plans
• sys.dm_exec_sql_text
• sys.dm_exec_query_stats

Index Related

• sys.dm_db_index_physical_stats
• sys.dm_db_index_usage_stats
• sys.dm_db_index_operational_stats
• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_groups
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_columns

SQL Server Operating System

• sys.dm_os_performance_counters
• sys.dm_os_schedulers
• sys.dm_os_nodes
• sys.dm_os_waiting_tasks
• sys.dm_os_wait_stats

I/O Related

• sys.dm_io_virtual_file_stats
• sys.dm_io_pending_io_requests
• sys.dm_io_cluster_shared_drives

ByDr. SubraMANI Paramasivam

Change Database Recovery Model

In this post, I am going to provide a simple demo on how to change the Database Recovery Model. For this example I have chosen the AdventureWorkdDW2008R2 database and you can download it from here.

Once you have opened your SQL Server Management Studio, expand the databases and right click on our AdventureWorkdDW2008R2 database and choose properties. The below image will appear. In here go to the Options tab from the left and then you can see “Recovery Model” options which has Full, Bulk-logged, Simple. You can simply select the required recovery model and click on OK button at the bottom of the pop-up box. That’s it, your recovery model will be changed.

ChangeRecoveryModel

ByDr. SubraMANI Paramasivam

Differential Database Backup

This post is to cover differential database backup in Microsoft SQL Server. You can try this in your local system and for this demo, I am using AdventureWorksDW2008R2 database. You can download this from this link.

In the below image, you can see the database backup options that are available by navigating to the Database and then right click and choose Tasks => Back Up…

FullDatabaseBackup_01

Following this, a new pop-up appears which gives much options to choose the available backup options. In here, I have highlighted “Backup type” and selected “Differential”. If you click on the drop down box, you will then see options for other types of backups.

Differential_DatabaseBackup_01png

In “Backup set”, I am giving a name for the differential backup. And then at last choosing the “Destination”, where the backup file will be placed. In here you also have options to Add new location, remove the default location.

Also you can use below T-SQL scripts to take backups instead of using GUI interface.

BACKUP DATABASE [AdventureWorksDW2008R2]
TO DISK = N’F:\\AdventureWorksSimpleRM.bak’ WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N’AdventureWorksDW2008R2-Differential Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

NOTE: Full Database backup should be taken before taking differential backup.

Give a try at your end.

 

12