none
How to find the degree of Normalisation in SQl Server 2008

    Question

  • Hi Team,

                 Could  you please suggest the T-SQL query to find the Normalisation of a database in Sql Server 2008

                and the lebel  of Normalisation exists , so that we  can try to denormalize to reduce lot of joins which is affecting the application a lot.

    • Moved by SSISJoost Monday, September 30, 2013 8:54 AM Not SSIS related
    Monday, September 30, 2013 7:58 AM

Answers

All replies

  • The Normalization is a part of Logical Design. Sorry, you cannot use a query to find what's the Data Logic!

    More info:

    Database Normalization


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Monday, September 30, 2013 8:52 AM
  • No such query... Generally if you  have a query with more than 5-6 JOINs and it performs slowly  you need to think about some level of denormalization...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 30, 2013 9:53 AM
  • But only when it is already correctly normalized and optimized :)
    Monday, September 30, 2013 10:00 AM
  • >you need to think about some level of denormalization

    That is bad idea!  Never think of denormalization! That is a slippery slope which leads to a messy database.

    Your best remedy for too many JOINs is views or stored procedures:

    http://www.sqlusa.com/bestpractices2005/innerjoin/

    The telltale sign of lack of 3NF normalization is data duplication (other columns than FK-s). Lots of NULL values also suspect.

    AdventureWorks2012 is 3NF normalized with minor violations such as Color in the Product table should have its own table and FK referenced from Product.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012







    Monday, September 30, 2013 10:37 AM
  • Kalman

    Have you ever worked on real world web projects where in order to return the data quickly sometimes there IS a need to denormalize the design or even enforcing  business rules ?

    http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/23/11318.aspx

    http://technet.microsoft.com/en-us/library/cc505841.aspx

    >>>>Your best remedy for too many JOINs i

    How the view in terms of performance is different  from simple SELECT ????


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 30, 2013 11:20 AM
  • Hi Uri,

    >Have you ever worked on real world web projects

    Yes I was the DBA for a number of consumer websites among them http://www.southbeachdiet.com which went absolutely crazy with traffic on the first working day of the new year (women signing up from their workplace).

    Can you post a script to prove your point on the perceived "benefits" of denormalization?

    Thanks.

    You can address performance issues many ways, but you can only fix a messy database by 3NF normalization redesign which can be very expensive if the database is already in production.

    • What are some benefits of denormalization? 
    • None whatsoever. A pure illusion. I have never seen a script proving any benefit!
    • What problems can you encounter when you decide to introduce some denormal-ization into your model?
    • The result is an un-normalized database with all the curses: data duplication, low developer productivity, difficulty in expanding the database in a consistent manner & high maintenance cost.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    Monday, September 30, 2013 12:05 PM
  • What scripts are  you talking about, have you read Alex real word example?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 30, 2013 12:39 PM
  • >What scripts are  you talking about,

    A T-SQL script to prove the "benefits" of denormalization. Thanks.

    Hugo Kornelis comment on the Kuznetsov blog: "However, I don't think I'd actually use it very often. You have to store lots of redundant data, which increases the I/O for most queries and hence hurts overall performance. There is also the issue of maintaining the information - if a doctor changes her working hours, the change has to be propagated to all the appointment rows in order to prevent a foreign key violation. That's a lot of work, along with a lot of locking and possibly blocking. The cascading update doesn't allow me to control the order in which locks are taken, so deadlocks might occur as well. And in this particular case, it makes no sense - if a doctor starts working shorter hours next month, the appointments for the previous month should not suddenly start violating the business rule (but I understand that I should take this as a simplified example).

    Another point, one of your arguments to avoid stored procedures or triggers for this is that stored procedures can be avoided and triggers can be temporarily disabled. The latter means your end users have permission to disable a trigger, which I think implies that they have permission to disable a CHECK or FOREIGN KEY constraint as well, so what exactly is the advantage here? Editing data outside of the stored procedure is a somewhat better point, but easily avoidable by giving users only permission to execute the stored procedures and no permissions whatsoever on the tables."

    UDF CHECK constraint are very powerful but there maybe performance issues resulting:

    http://www.sqlusa.com/bestpractices/udf-check-constraint/

    Here is another point Uri: I am not worried if Alex, Hugo, Erland or Uri does denormalization because I know that the database is in good hands. However, I am worried if denormalization is suggested to the average IT department as "performance tuning" tool.

    Optimization bible:

    http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    Monday, September 30, 2013 1:04 PM
  • There is no such query possible. Normalization is an abstract concept at the data model level. 

    Denormalization is always a bad idea. Why do you want an anomaly? 

    80-95% of the work in SQL is in the DDL that you did not post. A heuristic is that a query should have five or fewer tables; not a rule.  Maybe if you had followed Netiquette, we could help.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, September 30, 2013 3:50 PM
  • >>>A T-SQL script to prove the "benefits" of denormalization. Thanks.

    Take a query that JOINs lots of huge tables ....even with appropriate indexes and WHERE condition... it takes some time... 

    >>>However, I am worried if denormalization is suggested to the average IT >>>department as "performance tuning" tool.

    Yes , main thing of doing the denormalization is a performance consideration....It does not suit to every business BUT it has its place ....


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, October 01, 2013 3:55 AM
  • >Take a query that JOINs lots of huge tables

    If you combine huge tables into one (denormalize), you may speed up one query and slow down others due to data duplication and increased size. At the same time create lots of RDBMS headaches with the messy (or lack of) design.

    Data warehouse is the place for the denormalized, performance-oriented design not an OLTP database.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Tuesday, October 01, 2013 7:28 AM
  • Was the OP talking about OLTP?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, October 01, 2013 12:04 PM
  • hi Uri,

    I don't think that Alexander's example is a good example. Cause it is not about denormalization, it's imho about the use of natural keys:

    CREATE TABLE dbo.Professionals
    (
    	ProfessionalID INT NOT NULL,
    	ProfessionalName VARCHAR(50) NOT NULL,
    	CONSTRAINT PK_Professionals	
    		PRIMARY KEY ( ProfessionalID ) 
    );
    
    CREATE TABLE dbo.WorkingHours 
    (
    	ProfessionalID INT NOT NULL,	--\
    	StartedAt DATETIME NOT NULL,	-- > Natural key.
    	FinishedAt DATETIME NOT NULL	--/   
    	CONSTRAINT PK_WorkingHours	-- Enforce natural key.
    		PRIMARY KEY ( ProfessionalID, StartedAt, FinishedAt ), 
    	CONSTRAINT FK_WorkingHours_ProfessionalID
    		FOREIGN KEY ( ProfessionalID ) 
    		REFERENCES dbo.Professionals ( ProfessionalID )
    );
    
    CREATE Table dbo.Appointments 
    ( 
    	AppointmentCustomerID VARCHAR(50) NOT NULL,	--\
    	AppointmentStartedAt DATETIME NOT NULL,		-- > Natural key.
    	AppointmentFinishedAt DATETIME NOT NULL,        	--/   
    	WorkingHourProfessionalID INT NOT NULL,		        --\
    	WorkingHourStartedAt DATETIME NOT NULL,		-- > Natural foreign key relation.
    	WorkingHourFinishedAt DATETIME NOT NULL,	        --/   
    	CONSTRAINT PK_Appointments 
    		PRIMARY KEY ( AppointmentCustomerID, AppointmentStartedAt, AppointmentFinishedAt ),
    	CONSTRAINT FK_Appointments_WorkingHours 
    		FOREIGN KEY ( WorkingHourProfessionalID, WorkingHourStartedAt, WorkingHourFinishedAt )
    		REFERENCES dbo.WorkingHours ( ProfessionalID, StartedAt, FinishedAt ) 
    		ON UPDATE CASCADE
    );

    We're using the natural key of the WorkingHours table to link to the Appointments table. Then we're using the CHECK constraint.

    I omitted the CHECK constraints for clarity.



    Tuesday, October 01, 2013 5:30 PM