Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Archives: February 2014

Rebuild all indexes on all tables in the SQL Server database

One of the key tasks of a DBA is to maintain the database indexes and make sure they are not fragmented. You can use a sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes based on fragmentation. However, sometimes we may need to rebuild all indexes on all tables in… Read more

2 comments, 254 reads

Posted in Basit's SQL Server Tips on 23 February 2014

Removing part of string before and after specific character using Transact-SQL string functions

Problem

Today, one of the developers come to me and asked me the question that is there any T-SQL function that he could use to remove everything before and after a specific character in string. For example, if the table contains the full names of the people in the format… Read more

0 comments, 150 reads

Posted in Basit's SQL Server Tips on 23 February 2014

Transact-SQL query to obtain basic information about partitioned tables

Here is the simple query that returns basic information about all tables in a database that are partitioned:

SELECT SCHEMA_NAME([schema_id]) AS [schema_name]
      ,t.[name] AS [table_name]
      ,i.[name] AS [index_name]
      ,i.[type_desc] AS [index_type]
      ,ps.[name] AS [partition_scheme]
      ,pf.[name] AS [partition_function]
      ,p.[partition_number]
      ,r.[value] AS [current_partition_range_boundary_value]
      ,p.[rows] AS [partition_rows]
      ,p.[data_compression_desc]
FROM sys.tables t
INNER JOIN…

Read more

0 comments, 402 reads

Posted in Basit's SQL Server Tips on 17 February 2014

Get performance statistics for queries and query plans that are cached by SQL Server for faster query execution

You can use the following two DMVs to give you that information:

  • sys.dm_exec_cached_plans – You can use this dynamic management view to see information about the plans that are cached by SQL Server, along with other information such as cached query text, the amount of memory taken by cached plans,…

Read more

0 comments, 154 reads

Posted in Basit's SQL Server Tips on 17 February 2014