Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server Advent Calendar 11 – Report Builder v2

Day 11 of my virtual advent calendar, about stuff I like in SQL Server 2008.. …and I mentioned redesigning charts in my last post and the best answer might be to get your users to do it for themselves in Report Builder 2. Leaving you (the DBA BI expert) to concentrate on getting them the… Read more

SQL Server Advent Calendar 10 – Reporting Services Charts

Day 10 of my virtual advent calendar, about stuff I like in SQL Server 2008.. I have been using Reporting Services since beta 2 in SQL Server 2000, and for me the weak link was the charts which even  then looked pretty basic… Many people resorted to using add-ons including the ubiquitous Dundas charts.  Microsoft… Read more

SQL Server Advent Calendar 9 – Management with Powershell

Day 9 of my virtual advent calendar, about stuff I like in SQL Server 2008.. Many SQL Server databases are managed by part time or accidental DBA’s, who have a whole bunch of other duties possibly including networking, active directory, SharePoint and Exchange.   As well as learning about what these all do part time DBA’s… Read more

SQL Server Advent Calendar 8 – Analysis Services Named Sets

Day 8 of my virtual advent calendar, about stuff I like in SQL Server 2008.. Analysis Services allows you to create a set of things you are interested in and persist this in the cube.  MDX is used to define the set e.g. CREATE SET CURRENTCUBE.[Top 10 Resellers] AS TopCount([Reseller].[Reseller].[Reseller].Members, 10, [Measures].[Sales Amount]); creates a… Read more

SQL Server Advent Calendar 7 – Partitions & Indexed Views

Day 6 of my virtual advent calendar, about stuff I like in SQL Server 2008.. Indexed Views allow result sets to be persistent as they are materialized on physical storage which avoids performing costly joins and/or aggregations at execution time.  However in SQL Server 2005 they would have to be dropped and recreated if one… Read more

SQL Server Advent Calendar 6 – Hierarchy Data Type

Day 6 of my virtual advent calendar, about stuff I like in SQL Server 2008.. Using a relational database to store structures like the hierarchy of a company or the product catalogue usually involves one of two techniques: Have a table for each level in the hierarchy with a foreign primary key relationship to represent… Read more

SQL Server 2008 – Hierarchy ID Data Type

To show this we need to create a table with the new data type.. create table [dbo].Organisation (   DepartmentID int primary key nonclustered,   DepartmentName varchar(100) not null,     DepartmentHierarchyNode hierarchyid not null,   DepartmentHierarchyLevel as DepartmentHierarchyNode.GetLevel() persisted   — plus any other columns you need ) Note the level is a derived function of… Read more

SQL Server Advent Calendar 5 – Analysis Services Performance

Day 5 of my virtual advent calendar, about stuff I like in SQL Server 2008.. Over 80% of the budget on improvements to Analysis Services in SQL Server 2008 went on improving performance. The answer was called block space computation and to understand this you should be aware that Cubes are often quite sparse so… Read more

SQL Server Advent Calendar 4 – Filtered Indexes

Day 4 of my virtual advent calendar, about stuff I like in SQL Server 2008.. Filtered indexes are indexes with a where clause, allowing you to index the part of the  table you are interested in. For example if a column can have nulls in you could create an index on it that only indexes… Read more

SQL Server Advent Calendar 3 – Analysis Services Backup

Day 3 of my virtual advent calendar, about stuff I like in SQL Server 2008.. Backing up of analysis services in SQL Server 2005 ran out for steam for databases of about 20Gb, and so you have to follow a different route to back them up (see this technet article).  The time taken to back… Read more