working on it ...

Filters

snippets
20
followers
20
Published by cghersi

MS SQL Server Design Tips

There are quite few very important corner stones in the design and implementation of SQL Server stuff. Starting from the creation of tables, placement of indexes, and related schema design, we'll arrive to the design and optimization of Stored Procedures and Functions in order to reach a high quality, maintainability and safeness in the development of business logic onto the DB.
Sort by

Found 20 snippets

    public by DinhoPutz  413  0  4  0

    Remover caracteres especiais e quebra-linhas de um resultado em uma tabela

    Substituir o texto, na verdade, por outra coisa, seja por nada ou por um espaço vazio, talvez. Ideal para resolver aqueles problema na exportação de um resultado para CSV para deve ser aberto no Excel e aquele maldito ponto e vírgula (;) acaba levando parte do texto para outra coluna. Aqui também removemos tabulações, quebralinhas e outros.
    SELECT
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(nomeDaColuna,CHAR(13) + Char(10) ,''), CHAR(10), ''), CHAR(13), ''), CHAR(9), ''),';','-'),'  ', ' ') as 'Um nome novo e legal' 
    FROM NOMEDATABELA

    public by cghersi  4162  7  7  0

    Check the missing indexes in SQL Server

    This query returns the list of missing indexes for the queries that are currently running on the server
    SELECT 
    	er.session_id,
    	er.blocking_session_id,
    	er.start_time,
    	er.status,
    	dbName = DB_NAME(er.database_id),
    	er.wait_type,
    	er.wait_time,
    	er.last_wait_type,
    	er.granted_query_memory,
    	er.reads,
    	er.logical_reads,
    	er.writes,
    	er.row_count,
    	er.total_elapsed_time,
    	er.cpu_time,
    	er.open_transaction_count,
    	s.text,
    	qp.query_plan,
    	logDate = CONVERT(DATE,GETDATE()),
    	logTime = CONVERT(TIME,GETDATE())
    FROM sys.dm_exec_requests er 
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) s
    CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
    WHERE 
    	CONVERT(VARCHAR(MAX), qp.query_plan) LIKE '%<missing%'            

    public by cghersi  2714  1  6  3

    How to query some values inside XML in SQL Server

    This query relies on 'value' operator applied on XML fields. You can directly transform the resulting value into a particular type with the second parameter of this function.
    WITH XMLNAMESPACES ('http://www.snip2code.com/namespaces/s2c' AS s2c)
    SELECT  field1, field2
    FROM    MyTable
    WHERE   MyXMLField.value('/s2c:root/fieldName != ""', 'bit') = 1      
    
    WITH XMLNAMESPACES ('http://www.snip2code.com/namespaces/s2c' AS s2c)
    SELECT  field1, field2
    FROM    MyTable
    WHERE   MyXMLField.value('(/s2c:root/fieldName/text())[1]', 'varchar(100)') = 'some text'

    public by cghersi  3319  2  6  1

    Retrieve the domain of an email in MS SQL Server

    Suppose you have a table with an 'Email' field: here is the way to retrieve just only the domain of that email, and the name of the domain without the extension (.com, .net, etc.)
    select 
      SUBSTRING(Email, 
        charindex('@', Email, 0) + 1, 
        (charindex('.', Email, charindex('@', Email, 0)) - charindex('@', Email, 0)) - 1
      ) as domainWithNoExt, 
      SUBSTRING(Email, charindex('@', Email, 0) + 1, 200) as domain
    from MyTable

    public by cghersi  4145  4  6  0

    Query XML fields' content in MS SQL Server

    This is the right syntax to perform XQuery queries for XML columns in a SQL Server table
    SELECT MyField.query('
      declare namespace s2c="http://www.snip2code.com/namespaces/s2c";
    	(/s2c:myroot/myelem/fieldIwant[1]/text())')
    FROM  MyTable

    public by cghersi  4295  6  6  2

    Move a file to another location in SQL Server

    Here is the procedure to move a file to another location (e.g. for the database called MyDB). First you need to execute the first part of this snippet (until the comment '/**** Now you need to physically move the file to the other location ***/'). Then you need to move the file and eventually you can execute the last two statements.
    ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    
    ALTER DATABASE MyDB SET OFFLINE;
    
    ALTER DATABASE MyDB MODIFY FILE 
    (
       Name = MyDB_file1,
       Filename = 'c:\DB\MyDB.mdf'
    );
    
    /**** Now you need to physically move the file to the other location ***/
    
    ALTER DATABASE MyDB SET ONLINE;
    
    ALTER DATABASE MyDB SET MULTI_USER;

    public by cghersi  2704  0  6  0

    How to Retrieve all the tables having indexes on a specific filegroup in SQL Server

    Here are listed all the tables that have one or more indexes stored in a filegroup called 'INDEXES'
    SELECT 
        distinct  TableName = t.name
    FROM 
         sys.indexes ind 
    INNER JOIN 
         sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
    INNER JOIN 
         sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
    INNER JOIN 
         sys.tables t ON ind.object_id = t.object_id 
    INNER JOIN sys.[filegroups] f
        ON f.[data_space_id] = ind.data_space_id
    WHERE f.name = 'INDEXES'
    

    public by cghersi  2342  2  6  0

    Find columns used in indexes for SQL Server

    Retrieves a number of information related to the structure of the available indexes
    SELECT 
         TableName = t.name,
         IndexName = ind.name,
         ColumnName = col.name, FGName = f.name,
    	 ic.is_descending_key, ic.is_included_column, ind.is_unique, ind.type_desc, ic.*
    FROM 
         sys.indexes ind 
    INNER JOIN 
         sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
    INNER JOIN 
         sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
    INNER JOIN 
         sys.tables t ON ind.object_id = t.object_id 
    INNER JOIN sys.[filegroups] f
        ON f.[data_space_id] = ind.data_space_id
    WHERE ind.is_unique_constraint = 0  AND t.is_ms_shipped = 0 
    ORDER BY 
         t.name, ind.name, ind.index_id, ic.index_column_id

    public by cghersi  4962  3  6  0

    Recreate the index structure of an MS SQL Server DB in T-SQL

    This script prepares and executes several dynamic SQL statements to recreate indexes exactly as they are built on the original DB
    USE Snip
    GO
    
    DECLARE @indexName nvarchar(70), @tableName nvarchar(100), @sql nvarchar(4000) = '', @colName nvarchar(100), @desc bit, 
    	@inclCol bit, @prevInclCol bit,@unique bit, @typeDesc nvarchar(40), @previousIndexName nvarchar(70) = '',
    	@filegroup nvarchar(100) = '[PRIMARY]', @fgName nvarchar(100), @prevFGName nvarchar(100)
    
    DECLARE fk CURSOR FOR 
    SELECT 
         TableName = t.name,
         IndexName = ind.name,
         ColumnName = col.name, FGName = '[' + f.name + ']',
    	 ic.is_descending_key, ic.is_included_column, ind.is_unique, ind.type_desc
    FROM 
         sys.indexes ind 
    INNER JOIN 
         sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
    INNER JOIN 
         sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
    INNER JOIN 
         sys.tables t ON ind.object_id = t.object_id 
    INNER JOIN sys.[filegroups] f
        ON f.[data_space_id] = ind.data_space_id
    WHERE ind.is_unique_constraint = 0  AND t.is_ms_shipped = 0 
    ORDER BY 
         t.name, ind.name, ind.index_id, ic.index_column_id
    
    OPEN fk
    FETCH NEXT FROM fk INTO @tableName, @indexName, @colName, @fgName, @desc, @inclCol, @unique, @typeDesc
    WHILE @@fetch_status = 0
    BEGIN
    	IF (@prevFGName = '')
    		SET @prevFGName = @fgName;
    print 'prev=' + @previousIndexName + ';new=' + @indexName + ';tbl=' + @tableName + ';col=' + @colName + 
    	';desc=' + CAST(@desc as nvarchar(10)) + ';fgName=' + @fgName + ';filegroup=' + @filegroup +
    	';include=' + CAST(@inclCol as nvarchar(10)) + ';unique=' + CAST(@unique as nvarchar(10)) + ';type=' + @typeDesc
    	IF (@previousIndexName = @indexName)
    	BEGIN
    		--continue with the previous index:
    		if (@inclCol = 1 AND @prevInclCol = 0)
    			SET @sql += ') INCLUDE ( ' + @colName;
    		ELSE
    			SET @sql += ',' + @colName;
    
    		SET @prevInclCol = @inclCol;
    		print 'sql in equal=' + @sql
    	 END
    	 ELSE
    	 BEGIN
    		--complete and exec the last statement:
    		SET @prevInclCol = 0;
    		IF (LEN(@sql) != 0)
    		BEGIN
    			IF (@prevFGName != @filegroup)
    			BEGIN
    				SET @sql += ') ON ' + @filegroup + ';';
    				--SET @sql += ') WITH (DROP_EXISTING = ON) ON ' + @filegroup + ';';
    				print 'sql to exec=' + @sql
    				EXEC( @sql);
    			END
    		END
    
    		--create a new statement:
    		SET @sql = ' CREATE ';
    		IF (@unique = 1)
    			SET @sql += ' UNIQUE ';
    		SET @sql += @typeDesc + ' INDEX ' + @indexName + '_1 ON dbo.' + @tableName + ' (' + @colName;
    		print 'sql created=' + @sql
    	END
    
    	IF (@inclCol = 0)
    	BEGIN
    		IF (@desc = 1)
    			SET @sql += ' DESC ';
    		ELSE
    			SET @sql += ' ASC ';
    	END
    	SELECT @previousIndexName = @indexName, @prevFGName = @fgName 
    				
    	FETCH NEXT FROM fk INTO @tableName, @indexName, @colName, @fgName, @desc, @inclCol, @unique, @typeDesc
    END
    CLOSE fk
    DEALLOCATE fk
    
    --complete and exec the last statement:
    IF (LEN(@sql) != 0)
    BEGIN
    	IF (@prevFGName != @filegroup)
    	BEGIN
    		SET @sql += ') ON ' + @filegroup + ';';
    		--SET @sql += ') WITH (DROP_EXISTING = ON) ON ' + @filegroup + ';';
    		print 'sql to exec=' + @sql
    		EXEC( @sql);
    	END
    END
    

    public by cghersi  3841  0  6  3

    How to clean all the data from the tables of a MS SQL Server database

    This procedure allows to clean all the data from all the tables of the given DB. Use @rese_ident = 1 if you want to restart the identity columns from the default value.
    CREATE PROCEDURE [dbo].[CleanDB]
    	@db_name sysname = 'MyDBName',
    	@reset_ident tinyint = 0
    as
    begin
    	set nocount on
    
    	if @db_name is null
    		set @db_name = db_name()
    
    	if @reset_ident is null
    		set @reset_ident = 0
    
    	if not exists(select * from sys.databases where name = @db_name and database_id > 4)
    	begin
    		raiserror('Database does not exist or it can not be cleared', 16, 1)
    		return
    	end
    
    	declare @recovery_mode sysname
    
    	select @recovery_mode = cast(DatabasePropertyEx(@db_name,'Recovery') as sysname)
    
    	if @recovery_mode <> 'Simple'
    	begin
    		declare @ncmd nvarchar(max)
    
    		print '-- Changing database recovery mode to SIMPLE'
    
    		set @ncmd = 'alter database [' + @db_name + '] set recovery simple'
    
    		print @ncmd
    
    		exec sp_executesql @ncmd
    	end
    
    	-------------------------------------------------------------------------------------------
    	-- prepare table with tables list
    
    	create table #temp_tables
    	(
    		rec_id int identity(1, 1) primary key not null,
    		schema_name sysname not null,
    		table_name sysname not null
    	)
    
    	declare @n_cmd nvarchar(max)
    
    	set @n_cmd = 'insert into #temp_tables (schema_name, table_name) select ss.name as schema_name, st.name as table_name from [' + @db_name + '].sys.tables as st inner join [' + @db_name + '].sys.schemas as ss on ss.schema_id = st.schema_id where ss.name <> ''sys'' '
    
    	exec sp_executesql @n_cmd
    
    	-------------------------------------------------------------------------------------------
    	-- disable constraints
    
    	declare @table_name sysname
    	declare @schema_name sysname
    	declare @counter_max int
    	declare @counter int
    
    	select @counter_max = max(rec_id) from #temp_tables
    
    	if @counter_max is null
    		set @counter_max = 0
    
    	declare @object_name nvarchar(max)
    
    	set @counter = @counter_max
    	while @counter > 0
    	begin
    		set @table_name = null
    		set @schema_name = null
    
    		select @table_name = table_name, @schema_name = schema_name from #temp_tables where rec_id = @counter
    
    		if @table_name is null or @schema_name is null
    			break
    
    		set @object_name = N'[' + @db_name + N'].[' + @schema_name + N'].[' + @table_name + N']'
    
    		set @n_cmd = N'alter table ' + @object_name + N' nocheck constraint all'
    
    		print @n_cmd
    
    		begin try
    			exec sp_executesql @n_cmd
    		end try
    		begin catch
    			print '-------------------------------------------------------------------------'
    			print 'ERROR - Could not disable constraints for table ' + @object_name
    			print error_message()
    			print '-------------------------------------------------------------------------'
    		end catch
    
    		set @counter = @counter - 1
    	end
    
    	----------------------------------------------------------------------------------------------
    	-- delete records from tables
    
    	set @counter = @counter_max
    	while @counter > 0
    	begin
    		set @table_name = null
    		set @schema_name = null
    
    		select @table_name = table_name, @schema_name = schema_name from #temp_tables where rec_id = @counter
    
    		if @table_name is null or @schema_name is null
    			break
    
    		set @object_name = N'[' + @db_name + N'].[' + @schema_name + N'].[' + @table_name + N']'
    
    		set @n_cmd = 'delete ' + @object_name
    
    		print @n_cmd
    
    		begin try
    			exec sp_executesql @n_cmd
    
    			if @reset_ident = 1
    			begin
    				set @n_cmd = 'if exists(select * from [' + @db_name + '].sys.columns where object_id = object_id(''' + 
    					@object_name + ''') and is_identity = 1) dbcc checkident(''' + @object_name + ''', reseed)'
    
    				print @n_cmd
    
    				exec sp_executesql @n_cmd
    			end
    		end try
    		begin catch
    			print '-------------------------------------------------------------------------'
    			print 'ERROR - Could not clean table ' + @object_name
    			print error_message()
    			print '-------------------------------------------------------------------------'
    		end catch
    
    		set @counter = @counter - 1
    	end
    
    	-----------------------------------------------------------------------------------------------
    	-- enable constraints
    
    	set @counter = @counter_max
    	while @counter > 0
    	begin
    		set @table_name = null
    		set @schema_name = null
    
    		select @table_name = table_name, @schema_name = schema_name from #temp_tables where rec_id = @counter
    
    		if @table_name is null or @schema_name is null
    			break
    
    		set @n_cmd = 'alter table [' + @db_name + '].[' + @schema_name + '].[' + @table_name + '] with check check constraint all'
    
    		print @n_cmd
    
    		begin try
    			exec sp_executesql @n_cmd
    		end try
    		begin catch
    			print '-------------------------------------------------------------------------'
    			print 'ERROR - Could not enable constraints for table ' + @object_name
    			print error_message()
    			print '-------------------------------------------------------------------------'
    		end catch
    
    		set @counter = @counter - 1
    	end
    
    	drop table #temp_tables
    
    	---------------------------------------------------------------------------------------------------
    	-- restore database recovery mode
    
    	if @recovery_mode <> 'Simple'
    	begin
    		declare @ncmd2 nvarchar(max)
    
    		print '-- Restoring database recovery mode'
    
    		set @ncmd2 = 'alter database [' + @db_name + '] set recovery ' + @recovery_mode
    
    		print @ncmd2
    
    		exec sp_executesql @ncmd2
    	end
    
    end
    • Public Snippets
    • Channels Snippets