Friday, September 18, 2009

SchemaBinding views...

The error message "Cannot create index on view '*' because the view is not schema bound. (Microsoft SQL Server, Error: 1939)" clearly suggested that in order to create an index on this view I have to make it schema bound, but what is schema binding and how does this work? Schema binding ties an object to the base object that this new object depends upon. So without schema binding if a view is created and the underlying table is changed, the view may break, but the table change can still occur. With schema binding, if the base object is bound to another object, you will not be able to modify the based object unless you drop or alter the object to remove the schema binding.

Be aware that if you do not use the schema name, (dbo) in this case, then you will get the following error while creating the view. "Cannot schema bind view 'dbo.vw_sampleView' because name 'SAMPLETABLE' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself."
This error is only generated in case of schema bound views. In the case of ordinary views you will not get this error.

An important point to note is that you must first create a unique clustered index on the view, before you can create any non-clustered indexes. The script below shows us how we can create this unique clustered index or you can do this via SQL Server Management Studio (SSMS).
Create Unique clustered index on view
CREATE UNIQUE CLUSTERED INDEX [TestIndex] ON [dbo].[vw_sampleView] ( [rowguid] ASC ) GO
When this is run the command successfully creates the unique clustered index "TestIndex" on "vw_sampleview". At this point we can add any additional indexes as needed.
Here are some things to consider:
You can not create an index on a view with outer joins used in it, even if you use schema binding
You can not use '*' in the select statement of a view when it is schema bound. In such case you will clearly get error message of level 15 as "Syntax '*' is not allowed in schema-bound objects.".
You will not be able to create clustered index on a view if the view references any nondeterministic functions.
You can not use aggregate functions when using schema binding.
You can not migrate the base table of a schema bound view.

To change the base table we need to remove the SCHEMABINDING option from our objects or drop and recreate these objects once the table has been altered.
On the other hand schema binding does not implement any restriction on the alteration of the view. You may alter or drop the view the same way as you normally would.

Thursday, September 10, 2009

Date trims for SSIS expressions

//
//
Adam Jorgensen



Date Expression Compilation in SSIS
By Adam Jorgensen in Adam Jorgensen 09-08-2009 7:52 PM Categories:
Rating: Discuss 484 Reads 484 Reads in Last 30 Days no comments
There are many different ways to manipulate dates when working with them in SSIS. Many great examples have been posted across the web and I use many of them on a regular basis in my coding. It can be difficult however to get a single source for many of these since many of us use the ones we need and move on.
This blog entry (and follow ups) will highlight the most useful date strip/manipulation expressions on the web and link back to their creator/publisher.
Let's dive in !
This wiki over at SQLIS.com does a great job of collecting some of these date scripts. There is also an article published by Darren Green with most of the items in the WIKI. The Wiki is here: http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html
My goal in this posting is to highlight some cool expressions and encourage you to get engaged and post some of your own.
Some of the coolest and most useful expressions on the WIKI include:
Removing Time from Date:
This is very useful especially when data is coming from non Microsoft systems and has full datetime values everywhere. You may need to do lookups in your ETL against a date dimension and will need to perform this task routinely.
Two good approaches are shown below:
(DT_DATE)(DT_DBDATE)@[User::WorkingDate]
OR
DATEADD("day",DATEDIFF("day",(DT_DBTIMESTAMP)0,GETDATE()),(DT_DBTIMESTAMP)0)
Calculating the beginning and end of the previous month:
Beginning of the Month:
This expression moves three months back from today, subtracting the day count from the current day of month to get the first day of the month. Then it converts the expression to a DT_DBDATE type and then back to a DT_Date to reset the time to 00:00AM.
(DT_DATE)(DT_DBDATE)DATEADD("dd",-1 * (DAY(GETDATE())-1),DATEADD("month",-3,GETDATE()))
End of the Month:
This employs similar logic in reverse, going back to the last day of the preceding month, and then does some data conversions to truncate the time to 00:00AM.
DATEADD("mi",-1,(DT_DATE)(DT_DBDATE) DATEADD("dd",0,DATEADD("dd",-1 * (DAY(GETDATE())-1),DATEADD("month",-2,GETDATE()))))
If you have an expression (date or otherwise) that makes your life better or development faster, please post it here or email me and I will get it posted with your credits ! :)
Don't forget to post your thoughts or email me your questions to ajorgensen@pragmaticworks.com. As always, this Blog is to help you better understand the tools at your disposal ...