SQL SERVER – Database Recovery models

Database recovery models are used to control Transaction log maintenance. There are 3 Models namely

  • Full
  • Simple
  • Bulk –logged

 

Full Recovery Model:

In Full recovery model, all the transactions are logged in the Log file. We can recover a database to point in time recovery, if the database is in Full recovery mode. In Full recovery model, we can restore the individual pages. Log Maintenance is required in this model.

Bulk logged recovery model:

Bulk logged recovery model is almost similar to Full recovery model, only difference is Bulk operations will not be logged in the T-Log.  We can change the Full recovery model to Bulk recovery model   before the Bulk operations (Recommended method).           Log backup is possible in Bulk recovery model. Log Maintenance is required in this model.

Simple Recovery model:

Simple recovery model will not log the transactions in the T-log. So full backup is take at regular intervals for the minimal loss of data. Log Backup is not possible in this recovery model. Log Maintenance is  not required in this model.

Changing Recovery Model using T-SQL:

Alter Database Database_name set recovery Recovery_Model
--Example
Alter Database TestDB set recovery Full

 

SQL SERVER – VIEWS  and its Limitations

SQL SERVER – VIEWS and its Limitations

View is a virtual table whose contents are defined by a query.  Why we are using views?

The Answer is,

  • A security mechanism to allow users to access the data through views, without granting permissions to users to access the underlying tables.
  • Complex queries can re-write to view, so that we can use simple queries to extract the data.
USE [MyExperiments]
GO
 
/****** Object:  Table [dbo].[EmployeeDetails]    Script Date: 01/11/2012 05:43:47 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[EmployeeDetails](
	[ID] [int] NOT NULL,
	[Name] [varchar](50) NULL,
	[EmpType] [bit] NULL,
	[Designation] [varchar](50) NULL
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO

Now I am inserting some data into the table.

--Inserting Data into the table
insert into EmployeeDetails
Select 1,'praveen',1,'DBA'
union all
select 2,'kiran',1,'juniorDBA'
union all
select 3,'Fazal',0,'QA Analyst'
Creating a view for this table.
 
--Creating a view
 
Create view vw_EmpNameDesignation
as
select name,Designation from EmployeeDetails
GO

Encryption option to restrict the view details:
If we want to secure metadata of the view, we can use Encryption option. We can restrict the users to see the create view statement from sys.syscomments
Before Encryption:
Here I am issuing to view the metadata
Select * from sys.syscomments

 

Applying Encryption option:
Here I am altering the view with Encryption option.

 

--Encrypting  the view - Query statement
Alter  view vw_EmpNameDesignation with Encryption
as
select name,Designation from EmployeeDetails
GO
 
select * from sys.syscomments 
GO

 

 

Some of the Restriction of views:

  • We should enable ANSII_NULL ON
  • We should enable QUOTED_IDENTIFIER ON
  • You cannot perform outer joins
  • Indexed view should be created with schema binding option
  • Repeated columns cannot use in select list of views.
  • AVG, MAX, MIN, STDEV, STDEVP, VAR, and VARP are not allowed.
  • We can’t use ROLLUP, HAVING and CUBE in Group by clause.

SQL SERVER- Guidelines and Coding Standards – Best Practices

SQL SERVER- Guidelines and Coding Standards – Best Practices

Few days back our senior project manager Ashish joshi asked me to prepare a document for SQL Server Guidelines. As you all know this is really a very tough topic, we need to analyze more on guidelines which we are presenting. I had done some analysis and prepared the document. Really this was very interesting Experience for me. This is the first time I am uploading a document in SQLINFO.

 

You can download the SQL Server Guidelines from the below location:Download_SQLServerGuidelines