Tag: Miscrosoft SQL-server

Find partitioned tables in Microsoft SQL-server

This will find partitioned tables

select distinct object_name(object_id) from sys.partitions
group by object_id, index_id
having COUNT(*) > 1

This will find the partitions for a specific table

SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID(‘<Schema>.<Table>’)

Securing the Tabular BI Semantic Model

Summary: This paper introduces the security model for tabular BI semantic models in SQL Server 2012. You will learn how to create roles, implement dynamic security, configure impersonation settings, manage roles, and choose a method for connecting to models that works in your network security context.

Download here

Partitioning in Microsoft SQL-server 2008

When partitioning tables in Microsoft SQL-server 2008 you could get this error when your trying to move to the next step after mapping your partitions.

The number of filegroups or range values is not valid. Enter an extra filegroup in addition to the number of boundary values.

Your partition mapping will probably look something like this.

The error occurs because you haven’t choosen a filegroup for the last empty partition (the one after 2011). Your partition mapping should look like this.

In SQL the partition function will look like this.

CREATE PARTITION FUNCTION [<pf_Year>](varchar(4))
AS RANGE RIGHT
FOR VALUES (N’2007′, N’2008′, N’2009′, N’2010′, N’2011′)

Tablesample

This SQL-sentence will first of all create a table (destinationtable) containing variable1, variable2 and variable3. Into the destinationtable it will make a tablesample from 10 percent of the observations in the soucetable.

SELECT [variable1]
,[variable2]
,[variable3]
into [databse].[schema].[destinationtable]
from [database].[schema].[sourcetable]
tablesample (10 percent)

You could eg. use tempdb (a temporary database in Microsoft SQL-server) to store the sample.