Archive for the ‘SQL Server’ Category

A view that calls a view that calls a view that calls a view that….

Monday, July 9th, 2012

OK, I have a serious pet peeve: Views calling views. Who the heck started that? Why would we bury simple code so deep that you can’t immediately see an issue? How can you properly optimize the thing?!???

I’ve had developers give me quite a few reasons why they stack views (views calling views). The reasons generally fall into these categories:

  • It reduces the code we have to write.
  • Easier to maintain.
  • Provides a layer of abstraction.

Views are not an arbitrary feature that databases have; they serve a purpose. Views exist to:

  • Simplify security.
  • Simplify coding.
  • Simplify presentation.

The key word here is SIMPLIFY….

sim·pli·fy (sîm′plê-fì) tr.v. sim·pli·fied, sim·pli·fy·ing, sim·pli·fies

To make simple or simpler, as:

a. To reduce in complexity or extent.

b. To reduce to fundamental parts.

c. To make easier to understand.

So, in what way is an architecture where views are calling views simplifying?

Reasons why stacking views are misguided:

SQL is not OO. Having stacks of views does not make the code more efficient or faster. Any measurement thus would be coincidental.

  • Views are compiled base on position. Changing a referenced view could possibly affect referencing views. To prevent this you have to refresh the views in the order of referencing.
  • Dependency verification is not straightforward. You might not see all the dependencies using the standard system views that detail dependencies.
  • Code obfuscation: Views abstract complexity (i.e., simplification), so when you use an abstraction to create another abstraction you start to lose sight of what is really going on (i.e., add complexity).
  • Duplicate references: Once the complexity is hidden in the code, references to the same table or column are used multiple times without it being readily acknowledgeable.
  • It is difficult to properly optimize a view embedded in another view; ultimately you end up trying to optimize multiple views w/o understanding the effect on other referencing objects.

This is by no means an exhaustive list and each situation is a little different, but in general this covers most of the issues. Views, as with any feature, should not be abused. Just because something is possible doesn’t make it a good idea.

Posted in Design, SQL Server | No Comments »

“I’ve got an order for table 4!”

Thursday, June 7th, 2012

One of the most useful things anyone dealing with a database can learn is the system tables. Period. And there are many reason for this, most of which revolve around getting tedious work done fast and correctly.

Ever wanted to automate table DDL scripts so that they run in the correct order? If you know how the system tables work, you could just ask the database.

Case in point: I automated SQL builds for TEST, QA, Proof, and Prod. One of the issues was that I needed to know what order the table scripts needed to be run in so that I didn’t get a foreign key construction error. My solution was to query the DEV box that the developers used to create/modify the scripts. The steps top make this work:

  1. Get the scripts that were created/modified since the start of the release.
  2. Get the order of all tables from DEV database based on the Parent-Child relationships (foreign keys).
  3. Order the scripts based on where they fell in the list and execute in that order.

Easy! And I can use it to automatically execute the scripts (as I did in TEST), or I can use it to create the properly order DDL script for a TCW request (as I did for QA and PROD). The script is below:

 Declare @Continue int
, @ColContinue int
, @InterSQL nvarchar(4000)
, @SQL nvarchar(4000)
, @TableName varchar(250)
, @ColumnName varchar(250)
, @AllColumns varchar(4000)
, @DatabaseName varchar(200)
, @SourceDatabase varchar(200)
, @TableLevel int
 
Select @SourceDatabase = DB_Name()
 
Create table #TableOrder
( [Order] [int] IDENTITY (1, 1) NOT NULL
, [TableName] [varchar] (250) NOT NULL
, [TableLevel] [int] DEFAULT (0)
)
 
select @Continue = 1
, @TableLevel = 0
 
while @Continue > 0
Begin
Insert Into #TableOrder
( TableName
, TableLevel
)
Select SO.name
, @TableLevel
from sys.objects SO
where type = 'U'
and SO.object_ID not in ( select distinct so.object_id
from sys.foreign_key_columns SFK
INNER JOIN SYS.FOREIGN_KEYS SF
on SFK.Constraint_object_ID = SF.object_ID
inner join sys.objects SOC
on SFK.Constraint_Object_ID = SOC.Object_ID
inner join sys.objects SO
on SFK.referenced_Object_ID = SO.Object_ID
inner join sys.objects SOR
on SFK.Parent_Object_ID = SOR.Object_ID
inner join sys.columns SC
on SOR.Object_ID = SC.Object_ID
and SFK.Parent_Column_ID = Sc.Column_ID
inner join sys.columns SCP
on SO.Object_ID = SCP.Object_ID
and SFK.Referenced_Column_ID = SCP.Column_ID
inner join sys.schemas S
on SOC.Schema_ID = S.Schema_ID
where SOR.name not in ( Select TableName
From #TableOrder
)
)
and SO.name not in ( Select TableName
From #TableOrder
)
 
select @Continue = @@rowcount
, @TableLevel = @TableLevel + 1
 
print @Continue
End
 
select *
from #TableOrder
order by
TableLevel desc
 
drop table #TableOrder

Tags:
Posted in Automation, SQL Server | No Comments »