Tags » Database Design - SQL Server 2008, 2008R2 & 2012

Comparing sys.dm_db_index_physical_stats mode levels with Heap and Clustered Index

Create a table with some data

drop table t3
CREATE TABLE t3 (col1 int PRIMARY KEY, col2 varchar(500)) WITH(DATA_COMPRESSION = PAGE);
GO
--BEGIN TRAN
DECLARE @idx int = (select max(isnuLL(col1,0))+1 from t3);
WHILE @idx < 1000000
BEGIN
BEGIN TRAN
    INSERT INTO t3 (col1, col2) 
    VALUES (@idx+convert(int,rand() * 100), 
    REPLICATE ('a', 100) + CAST (@idx as varchar(10)) + REPLICATE ('a', 380))
    SET @idx = @idx + 1
	COMMIT
END
--COMMIT;
GO

…
446 more words

Administration

Standard SQL Server Error log procedure

we spend lot of time trying to look at log files when we run any particular SQl files have a standard frame work to collect these errors and store in table, amkes the application management simple at same time provides some insights into exceptions over the time… 369 more words

Administration

How to FIX: Manage Orphaned users in SQL SERVER with a simple script

Using below scripts

  • Fixes all orphaned users in the current database if a login exists
  • If a login does not exist in thesql server instance …
  • 266 more words
Administration

How to fix: Access to the remote server is denied because the current security context is not trusted SQL Server

You are seeing this error because you are trying to impersonate or  switching the execution context using EXECUTE AS clause and Executing a RPC on a linked server… 84 more words

Administration

One of the best SQL Server Index Internals: A Deep Dive

its from Tim Chapman, one of the best index internal presentation

I will try to highlight the things he talk through this presentation

Indexes

  • Clustered vs Non clustered and filtered indexes…
  • 75 more words
Administration

Sql server datacollector issue specified @collector_type_uid is not valid in this data warehouse

Executing the query “{call .(?, ?, ?, ?, ?, ?…” failed with the following error: “The specified @collector_type_uid (14AF3C12-38E6-4155-BD29-F33E7966BA23) is not valid in this data warehouse.”. 160 more words

Administration

Data Cube: A Relational Aggregation Operator : Microsoft research paper

Very nice article on group by clause , how it works and what are its limitations and how can we overcome with a CUBE operator… 167 more words

Database Design - SQL Server 2008, 2008R2 &amp; 2012