My first technical Video in SQL Server Technology


Hello Guys “a journey of a thousand miles begins with a single step.” based on that i started my journey to MVP award i am now have more than 200 Post in SQL server Technology i am founder of Community SQL Database administration from 2012 and i started today New step to Promote my work I published today my first Technical Video in SQL Server 2014 “How we can crate Clustered Columns Store index on all tables by Single Click” this the Description of the video

Download Scripts : http://sqlserver-performance-tuning.n…

Filling all your tables with test data VII


introduction

Hi guys in my last post  (Filling all your tables with test data) i explained how we can fill multiple tables by test data by using My own Stored procedure , but after i creating the script and i do on it more test on our staging environment and it’s  working very well i got  one issues on the script ( SP can not cover the tables with composite primary key )  at this time I reviewed  the Stored procedure from the scratch to know where is the issue and how i can solve it at the end i asked on e of my best friends Mohamed Abdel Kariem mentor T-SQL to help me in this subject and at the end we do awesome script and here the below new enhancement on our main stored procedure .

Stored procedure enhancement:

  1. SP now supported tables with composite primary key
  2. SP now supported tables with composite primary key with data type uniqueidentifier.
  3. reduced the liens in the SP by using CASE technology
  4. using QUOTENAME function instead of more concat.

Continue reading “Filling all your tables with test data VII”

Create Clustered ColumnStore index for All tables


Problem :

we are working in big project SQL Server 2014 upgrade and SQL Server 2014 Performance this project our role to test the new feature in SQL Server 2014 from the performance wise so we decide  to deploy the new Enhancement in SQL Server 2014 (Clustered ColumnStore index ) CCI so i take backup from one database in my work environment and restored it again with prefix _2014 .

this new type of index to create it on the table this table should be no index on it so i drooped all the index from all the tables but the Problem now How can i create the Clustered columnstore index on all tables( tables supported CCI) by one Click.

Solution :

i do one script to create Clustered columnstore index on all supported tables and here the description of the Script

1- to create CCI on any table we have some limitations one of this limitations the data type of the Columns CCI not supported Some data type.

2- I inserted the data type not supported by CCI in temp Tables

CREATE TABLE #CCI_DataType_Limitation ( DataType NVARCHAR(MAX) )
INSERT INTO #CCI_DataType_Limitation
VALUES ( ‘text’ ),
( ‘timestamp’ ),
( ‘hierarchyid’ ),
( ‘Sql_variant’ ),
( ‘xml’ ),
( ‘geography’ ),
( ‘geometry’ )
Select * from #CCI_DataType_Limitation

Continue reading “Create Clustered ColumnStore index for All tables”