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

 

 

 

AGILE- CHALLENGES IN AGILE DATABASE DESIGN

Database Design:

As you all know, database design is a very basic need in a software development. I heard from lot of people that database programming and design can be handled by anyone who has some little experience in database. I am sure that it is a mistake and a strong mistake.

Why I am saying it is a mistake?

Developing software without a proper database design is similar to building a Multi-storied building with no proper foundation. Multi-storied building will collapse once it is occupied or some floors are added to the existing building. The same scenario will happen here also. I will show how it happens with one simple example.

This is one example I got from the real world scenarios. I am forced to see lot of tables without even a primary key. This is one of the basic things which are explained in the database design normalization rules.

 

Here I am going to explain a small scenario which will hit the Performance.

Let’s look this example:

-- Simple Example of coosing DataTypes
CREATE TABLE test4u
  (
     id  UNIQUEIDENTIFIER,
     id2 NVARCHAR(MAX)
  )
 
INSERT INTO test4u
SELECT Newid(),
       Newid()
 
SELECT *,
       Datalength(id)  AS [No of bytes Used for ID],
       Datalength(id2) AS [No of bytes Used for ID2]
FROM   test4u

Look at this scenario,

We are inserting two GuiD values in two columns ID and ID2 and their data types are uniqueidentifier and nvarchar(max). Here I am displaying the no of bytes used to store the data. For ID Column we used unique identifier which uses less bytes than ID2. So storing a GUID value in uniqueidentifier is most recommended method. This will really improve the performance due to the less Memory Utilization. After database comes into production, we can’t change the DB structure since it will affect DB and Application codes.

This is only a very simple example which helps others to think about the database in a Performance point of view. A lot of things will improve the performance. So proper database design is a  very basic need in every project.

 

What is agile methodology?

One line difference for Agile and Waterfall model:

When the client is not clear with requirements we can go for agile methodology.When the client is clear with his requirements we can go for waterfall model.

Developing a database in agile environment:

Here there is a challenge for the development DBA’s. I will explain with a small scenario, which I understood from the internet and books I learned. Some of the links I am posting here. Thanks to john Brady an Oracle database specialist (20 years of Experience), who explains about the agile database development in his blog databaseperformance.blogspot.com.

Requirements:

  • The team should contain Database Administrators/Architects/Developers who has knowledge of agile database design techniques.
  • Application team should have a thorough knowledge of what they are going to do in each iteration.
  • Programmers, Team leads and PM should  work closely with DBA’s
  • DBA’s should know through knowledge of Database performance Tuning.

Implementation:

  • DBA’s should design Database as if database will change in Future rather than we are not clear with the requirements. The Question is how DBA’s will think like that?
  • Look at this simple example,  you will be able to see what happens in each iteration  through database diagrams.

Example requirement:

  • 1st Iteration:  An sms/voice-call (Not Confirmed by Client) should sent to the students and Staffs once they will enter into the college to make them alert that they reached the campus premises.
  • 2nd Iteration: Students parents also should be alerted while entering the college.
  • 3rd Iteration: Finalized that SMS should Sent.

How we will design database with Agile Methodology:

1st Iteration:

Database Diagram for 1st Iteration:

2nd Iteration:

Database Diagram for 2nd Iteration:

3rd Iteration:

Database Diagram for 3rd Iteration:

References: http://www.agiledata.org/essays/bestPractices.html

References: http://databaseperformance.blogspot.com/2009/10/challenge-of-agile-database-design.html