RSS

Monthly Archives: May 2013

The 1st SQL Saturday event in the middle east @Riyadh

you can watch the 1st video record of the 1st SQL Saturday Event in the middle east at

 

 

 
Leave a comment

Posted by on May 28, 2013 in General topics

 

When we Create Primary Key !!!

Question : Choose the wrong Answer

When We Create primary key on Table :

1- it Always Create ” Clustered index ”

2- It Always Create ” Non Clustered index ”

3- It Always Create ” Clustered and Non Clustered index ”

Answer :

The Wrong answer in number 3 primary key create one index on table (Clustered or Non Clustered ) Automatically .

When we created Primary key on any table this primary key Check the Table if this table have Non Clustered Index Automatic Create Clustered index Else if the table have Clustered index primary key Automatic Create Non Clustered index

 

Demo :

1- CREATE TABLE

Create table Table_primaryKey

(

Pkey_ID int  Not Null,

Pkey_name Nvarchar(50) ,

Pkey_Lsn bigint

)

2- Check Index type on Table Name ” Table_PrimaryKey ” 

after the result Show we will check the Column Name ” typ_desc ” to know the type of the indexes on this table .

Note : Typ_desc = HEAP ( Meaning this Table not have ” CLUSTERED INDEX ” .

 

A- Check the index on table by Script

SELECT obj.name,
ind.name,
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IndexFillFactor’),0) [fill_factor],
create_date, modify_date, ind.type_desc,
fill_factor, has_filter,
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IndexDepth’),0) [IndexDepth],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsAutoStatistics’),0) [IsAutoStatistics],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsStatistics’),0) [IsStatistics],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsUnique’),0) [IsUnique],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsClustered’),0) [IsClustered]
FROM sys.objects obj
INNER JOIN sys.indexes ind
ON obj.object_id = ind.object_id
WHERE obj.type = ‘U’
and obj.name = ‘Table_primaryKey’

 

Heap

now we see the result is HEAP  So we don’t have any clustered index or Non Clustered index also because the result not show any thing expected the HEAP

B -Check index table from SQL Server Management Studio 

Check Database >> Tables >> Indexes – you will not found any index

3- Create Primary Key

ALTER TABLE Table_primaryKey
ADD CONSTRAINT [PK_Table_primaryKey_Pkey_ID]
PRIMARY KEY (Pkey_ID ASC
)
GO

Primary key will Create Automatic Clustered index ” PK_Table_primaryKey_Pkey_ID ” on the table

Clustered index

4- Drop the Primary Key

ALTER TABLE Table_primaryKey
DROP CONSTRAINT [PK_Table_primaryKey_Pkey_ID]
GO

Now the Clustered index Dropped

Check Database >> Tables >> Indexes – No indexes

5- Create Clustered Index

CREATE CLUSTERED INDEX
[CL_Table_primaryKey_Pkey_ID] ON Table_primaryKey
( Pkey_ID ASC)
GO

Check Database >> Tables >> Indexes – Index Name (CL_Table_primaryKey_Pkey_ID) Clustered Index

6- Create Primary Kay again (Check point No 3 )

Check Database >> Tables >> Indexes – PK_Table_primaryKey_Pkey_ID (NONCLUSTERED)

NonCluestered

Now we now if the table have Clustered iindex and you create Primary key it will Create automatic Non-clustered Index

7- drop the primary key again (check point No 4)

Check Database >> Tables >> Indexes – Index Name PK_Table_primaryKey_Pkey_ID (NONCLUSTERED) DELETED

8- Create Non-Clustered Index 

9- Create primary Key

Check Database >> Tables >> Indexes -you will find New Non Clustered index now we have 2 Non-Clustered Index

10- drop All index in table 

Check Database >> Tables >> Indexes >>> Delete one by one

11 -drop the primary key again (check point No 4)

Note : Now i i Create New primary key it will Create Automatic New Clustered Index but if you need to Create Primary Key With Non-Clustered index .

12- Create Primary Key With Non-Clustered Index

ALTER TABLE Table_primaryKey
ADD CONSTRAINT [PK_Table_primaryKey_Pkey_ID]
PRIMARY KEY NONCLUSTERED (Pkey_ID ASC
)
GO

13 – Check the index on Table 

Primary Key With NonClustered

Primary Key Can Create Non-Clustered Index When we don’t have Clustered index in on Table .

I Wish Success To Everyone

Eng. Mostafa Elmasry

Database Administrator

 
Leave a comment

Posted by on May 24, 2013 in Index

 

Index : Using SORT_IN_TEMPDB to improve Index Performance

With SORT_IN_TEMPDB = ON 

This option can improve your Index to be more Performance but you must take care about this Size of the TempDB

— Create some indexes
CREATE UNIQUE CLUSTERED INDEX [Clustered_SalesOrderDetails]
ON [Sales].[SalesOrderDetail]
([SalesOrderDetailID] ASC)
WITH (SORT_IN_TEMPDB = ON)
GO

 
Leave a comment

Posted by on May 19, 2013 in Index

 

Script : index Information by Using DMV and INDEXPROPERTY Option

you can use this DMV Script to Show Index information in your database

SELECT obj.name,
ind.name,
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IndexFillFactor’),0) [fill_factor],
create_date, modify_date, ind.type_desc,
fill_factor, has_filter,
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IndexDepth’),0) [IndexDepth],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsAutoStatistics’),0) [IsAutoStatistics],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsStatistics’),0) [IsStatistics],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsUnique’),0) [IsUnique],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsClustered’),0) [IsClustered]
FROM sys.objects obj
INNER JOIN sys.indexes ind
ON obj.object_id = ind.object_id
WHERE obj.type = ‘U’
—and obj.name = ‘Computed_Cal’ —where you need to Show index info in Specfice table
–And ind.type_desc = ‘Heap’ — Where you need the Tables not have any Indexes

 
2 Comments

Posted by on May 19, 2013 in Index, Script

 

History of all Committed DDL Statement Executions

Now if i need to Make Auditing in my SQL Server production to know any thing happens in my SQL Server  We have 3 Ways :

1- We can Use third party tool like this one for free

http://www.toadworld.com/KNOWLEDGE/ToadKnowledge/TipsandTricks/tabid/74/TID/303/cid/38/Default.aspx

2- use SQl Server Report to Audit your SQL Server . it Will give you Summary about the Object Changed like Create , Drop , Alter .

3- Use SQL Server Audit Feature . Under Security  in SQL Server SSMS . it can give you any info you need to know it.

I will Explain today the How to check the History of all Committed DDL Statement by SQL Server Reports.

in the first i will create table to Explain my Examples on it

Create Database Audit

go

use Audit

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TABLEAUDIT](
[EMP_ID] [int] IDENTITY(1,1) NOT NULL,
[EMP_NAME] [nvarchar](50) NULL,
[EMP_SALARY] [bigint] NULL,
[EMP_BOUNS] [bigint] NULL,
[TOTAL_SALARY] AS ([EMP_SALARY]+[EMP_BOUNS]),
[EMP_ADDRESS] [nvarchar](100) NULL,
CONSTRAINT [PK_TABLEAUDIT] PRIMARY KEY CLUSTERED
(
[EMP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

then now i will insert Data :

—INSERT DATA
INSERT INTO TABLEAUDIT ([EMP_NAME],[EMP_SALARY],[EMP_BOUNS],[EMP_ADDRESS])
VALUES
(‘Mostafa’,2500,500,’Egypt-Cairo-Helwan’)
, (‘M.Osman’,3000,500,’Egypt-Alex’)
, (‘M.Omar’,3000,500,’Egypt-Assuit’)

Create Example

SQL Server Reports to Audit your SQL production :

1- Schema Changes History Reports

Open SSMS >>> Database Audit >>>> Right Click on it >>>> Reports >>>> Stander Reports >>>> Schema Changes History Report .

you will see the Changes happens in database Audit but only in the Create and Alter and if you make and Drop to Column , table , View Like this you can reviwe it by the time and How make this action from this report on this database

Schema Changes History Report

also you can check any changes happens in your server on all database_user by this steps :

Open SSMS >>>> Connect to your instance you need to make audit on it >>>> right click on your server name >>>> Reports >>>> Stander Reports >>>> Schema Changes History report .

Schema Changes History Report On Server

so now we can know a history of all Committed DDL Statement Executions Recorded. but not give us all info like the DML Statement Executions.

 
Leave a comment

Posted by on May 18, 2013 in Performance MSSQL

 

DBA Check List

Any DBA must now :

How to become As DBA ?

What is General DBA Best Practices ?

What DBA do Day-to-Day

And more tips like Installation , Upgrade , maintenance , performance , High Availibilty , and other Services (SSIS,SSAS,SSRS)

So if you need to know more about this Check this Link is very useful to any DBA (SQL Server , Or Oracle )

https://www.simple-talk.com/sql/database-administration/brads-sure-dba-checklist/

 

How Can i Create index on Non-Deterministic Column in SQL Server

Question 1 :

How Can i Create index on Non-Deterministic Column in table or View ?

Answer :

I can’t Create Index on Non-Deterministic Column in table or View because this is the limitation of the index .

Question 2 :

What is the Deterministic and Non-Deterministic Function in SQL Server ?

Answer :

These built-in functions from categories of built-in functions other than aggregate and string functions are always deterministic:

ABS DATEDIFF PARSENAME
ACOS DAY POWER
ASIN DEGREES RADIANS
ATAN EXP ROUND
ATN2 FLOOR SIGN
CEILING ISNULL SIN
COALESCE ISNUMERIC SQUARE
COS LOG SQRT
COT LOG10 TAN
DATALENGTH MONTH YEAR
DATEADD NULLIF

These built-in functions from other categories are always nondeterministic:

@@ERROR FORMATMESSAGE NEWID
@@IDENTITY GETANSINULL PATINDEX
@@ROWCOUNT GETDATE PERMISSIONS
@@TRANCOUNT GetUTCDate SESSION_USER
APP_NAME HOST_ID STATS_DATE
CHARINDEX HOST_NAME SYSTEM_USER
CURRENT_TIMESTAMP IDENT_INCR TEXTPTR
CURRENT_USER IDENT_SEED TEXTVALID
DATENAME IDENTITY USER_NAME

to know more information about this Check this link Check this Link

http://msdn.microsoft.com/en-us/library/aa214775(v=sql.80).aspx

Question 3 :

How can i check the Column is deterministic or deterministic ?

Answer :

you can check this by retrieving the Column properties with COLUMNPROPERTY .

LIKE :

Select COLUMNPROPERTY (OBJECT_ID( ‘Table_Name‘ ), ‘Column_Name‘, ‘IsDeterministic‘);

the result return from this Select Statement one of them :

1 = TRUE
0 = FALSE
NULL = Input is not valid. Not a computed column or view column.

Note : You can Change the value of  ‘IsDeterministic‘ to another value like (‘IsIndexable‘,’IsComputed‘)

to know more about this values Check this link

http://www.mssqltips.com/sqlservertip/1298/retrieving-sql-server-column-properties-with-columnproperty/

Example :

—Create Table name Computed_Col

CREATE TABLE [dbo].[Computed_Cal](
[ID] [int] NULL,
[Sallary] [bigint] NULL,
[Bouns] [bigint] NULL,
[Date] [datetime] NULL,
[Total] AS ([Sallary]+[Bouns]),
[ADDDATE] AS (dateadd(day,(20),[Date])),
[returndate] AS (getdate()),
[Calc_Year] AS (DATENAME(YEAR,[DATE]))
) ON [PRIMARY]

—-Insert Data

SET DATEFORMAT DMY
INSERT INTO Computed_Cal (ID,SALLARY,BOUNS,[DATE])
VALUES (1,2500,500,’16-05-2013′)

Computed_Cal

now i created 4 Computed Column (Total,ADDDATE,returndate,Calc_Year)

if you select * from this table you will find the Column Working good but the Question what will happened when i try to Create index on this Columns let’s see i will Create 2 index one in Column name (ADDDATE) and one in (returndate)

—-Create Index on Column name (ADD_DATE) :

CREATE NONCLUSTERED INDEX ind2_ADD_DATE
ON Computed_Cal ([ADD_DATE]);
GO

the index created Successfully because this column not NonDeterministic Column Meaning i don’t use NonDeterministic Function in the Computed Formula

now i will Check the Column property for column name ADDDATE to know IsDeterministic and IsIndexable if the result is (( 1 )) its will Accept the index and it not have any function form NonDeterministic Function the If it (( 0 )) it will not Accept like we will see in another Creating index

—CHECK THE COLUMNPROPERTY
SELECT COLUMNPROPERTY (OBJECT_ID(‘Computed_Cal’), ‘ADDDATE’, ‘IsDeterministic’);
SELECT COLUMNPROPERTY (OBJECT_ID(‘Computed_Cal’), ‘ADDDATE’, ‘IsIndexable’);

Check Columnproperty

—-Create Index on Column name (returndate) :

in this Column if you check the formula you will see i select to getdate() Function to retrieve the time of the insert data  in this Function is one form the NonDeterministic Function .

At the First i will Check the COLUMNPROPERTY for this column (returndate):

—CHECK THE COLUMNPROPERTY
SELECT COLUMNPROPERTY (OBJECT_ID(‘Computed_Cal’), ‘returndate’, ‘IsDeterministic’);
SELECT COLUMNPROPERTY (OBJECT_ID(‘Computed_Cal’), ‘returndate’, ‘IsIndexable’);

Result is 0 so it meaning this Column have NonDeterministic Function and it will not Accept index .

NonDeterministic

Now i f  i try to Crate This Index on this Column :

— index 2

CREATE NONCLUSTERED INDEX ind3_return_date
ON Computed_Cal (returndate);
GO

SQl Serve will give me Error:

Msg 2729, Level 16, State 1, Line 3
Column ‘returndate’ in table ‘Computed_Cal’ cannot be used in an index or statistics or as a partition key because it is non-deterministic.

Non-Determimistic2

In the Finally we must be take care when we build the table Structure 

Eng.Mostafa Elmasry

Database Administrator

 
Leave a comment

Posted by on May 17, 2013 in Index