Mustafa EL-Masry Index, Script

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

Mustafa EL-Masry New Feature, SQL Server 2012

Replicate Function in SQL Server 2012


This New function in SQl Server 2012  (Replicate)

it repeats the string/character expression N number of times specified in the function.

 

Example :

DECLARE @FirstString nVARCHAR(MAX)
SET @FirstString = REPLICATE(‘A’,12000)
Select @FirstString
SELECT LEN(@FirstString) LenFirstString;

Replicate Function

 

Now we see the Result of character”A” it’s repeated but the Question

Why when i Select the Len the result is 8000 not 12000 ?

 

Mustafa EL-Masry Script, SQl server Administration

the use of “SET NOCOUNT ON;” in SQL Server?


This statement is used to stop the message that shows the count of the number of rows affected by the SQL statement written in the stored procedure or directly SQL Statement

When it is ON - the number of affected rows will not be returned 
When it is OFF - the number of affected rows will be returned

Example 


USE AdventureWorks2012;
GO
SET NOCOUNT OFF;
GO
-- Display the count message. The number of affected rows will be returned 
SELECT TOP(5)LastName
FROM Person.Person
WHERE LastName LIKE 'A%';
GO
-- Disable the Count massage Then Number of affected rows will not be returned.
SET NOCOUNT ON;
GO
SELECT TOP(5) LastName
FROM Person.Person
WHERE LastName LIKE 'A%';
GO
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO

Thanks for All

Eng. Mustafa Elmasry

MSSQL DBA


		


		
			
 





	

		

					

			 Mustafa EL-Masry Script					


How to Disable or Enable All constraint  Key in Database
		

		

			

Disable all the constraint in database




EXEC sp_msforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”


Enable all the constraint in database




EXEC sp_msforeachtable “ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”

		


		
			
 





	

		

					

			 Mustafa EL-Masry Administration, SQl server Administration					


How to Change the Default Database Location
		

		

			

Problem :


After I Create Cluster and I make 2 Share Disk one for Data and the another for Logs I forget to Customize the location For the database What can I do to Change this Location ?


Salutation : 


You have two ways the first by T-SQL and the Second is Wizard Steps :


First Way:


A- Change the Default location for data file :


EXEC xp_instance_regwrite


N’HKEY_LOCAL_MACHINE’,


N’Software\Microsoft\MSSQLServer\MSSQLServer’,


N’DefaultData’,


REG_SZ


,N’F:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA’


GO


B- Change the Default location for Logs file :


EXEC xp_instance_regwrite


N’HKEY_LOCAL_MACHINE’,


N’Software\Microsoft\MSSQLServer\MSSQLServer’,


N’DefaultLog’,


REG_SZ


,N’G:\MSSQL10_50.MSSQLSERVER\MSSQL\Log’


GO


Second Way:


Open the management Studio >>> Write Click on the Server >>> Select properties


Change1


Then Select Database Setting >>> you Will See Database Default Location


Change2