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
If you want to simulate bulk-deleting in Microsoft SQL-server this can do the trick. I use it to avoid getting a transaction log that fills up the harddisk.
The example uses a stored procedure, but you don’t have to do this. The stored procedure is given a year-variable that is used in the SQL-sentences that selects what has to be deleted.
USE [YOUR_DATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sp_BULK_DELETE_AREA_YEAR @ActionYear char(4)
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
— Insert statements for procedure here
DECLARE @Count INT
Declare @for_delete INT
Declare @chunk_size INT
SELECT @chunk_size=100000
SELECT @Count = 0
select @for_delete=count(*) from [YOUR_TABLE] where [YOUR_VARIABLE] in (@ActionYear)
While (@Count < @for_delete)
BEGIN
SELECT @Count = @Count + @chunk_size
BEGIN TRAN
DELETE top(@chunk_size) from [YOUR_TABLE] where [YOUR_VARIABLE] in (@ActionYear)
COMMIT TRAN
END
END
GO
It’s excecuted in the following manner: exec sp_BULK_DELETE_AREA_YEAR ‘2011’
I haven’t figured out how to use a variable that contains the tablename. So I don’t think it’s possible. You have to write the table name.
The solution was found here.
Disclaimer: Beware of the possible security threat when you enable xp_cmdshell usage. You can also take a look at this article, it’s at bit more complicated, but a lot safer.
The article pretty much walks you through, what you need to do to use SQLCLR. I tried it out and got stuck when i got a security threat. I got the threat because you have to give the assemblies that you use (‘alter assemblies…’) a given state – safe, unsafe and external in the SQL-server.
EXEC master.dbo.sp_configure ‘show advanced options’, 1
RECONFIGURE
EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1
RECONFIGURE
It’s also possible to use ‘sp_oacreate’.
set statistics time {on|off}
Displays the number of milliseconds required to parse, compile, and execute each statement.
set statistics io {on|off}
Causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements.
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
AS RANGE RIGHT
FOR VALUES (N’2007′, N’2008′, N’2009′, N’2010′, N’2011′)
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.
This SQL-sentence helps you find the tables with enabled or disabled indexs.
I haven’t been able to find this information through SSMC.
SELECT name, is_disabled FROM sys.indexes
where is_disabled = 1
These commands will clear the cache from Microsoft SQL-server. It can be used during test to emulate runing a query against a “cold” server. A server where the cache hasn’t been filled with data.
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE