Repeat the value using Replicate

This post is to repeat a specific value for specified number of times and assign that to a variable or if you have a column then do so.

This uses Replicate() function to repeat a string for max length of variable using sql_variant_property

The last statement LEN(@test) gets the lenght of the value assinged to the variable

DECLARE @test VARCHAR(255)
SET @test = ''
SET @test = REPLICATE('ab',CONVERT(INT,(select sql_variant_property(@test, 'MaxLength'))))
SELECT @test
SELECT LEN(@test)

Data type size of variable and column in sql

This post is to detect the length of the data type of a variable and a column.

The first piece of code declares a variable, and usein sql_variant_property finds out the length of the datatype.

The second one, gets the size of the column data type of a table.

--For variable

DECLARE @test varchar(255)
SET @test = '' --Must assign a value
SELECT sql_variant_property(@test, 'MaxLength')

--For column of a table
SELECT column_name, data_type, character_maximum_length 
FROM information_schema.columns
WHERE table_name = 'myTable'

SQL query performance

Below are some of the tips to improve the performance of stored procedures of queries

-Use TRUNCATE instead of DELETE where ever possible.

-REMOVE 'SELECT TOP 100 PERCENT FROM TABLE' and use 'SELECT * FROM TABLE' or 'SELECT      column_names FROM TABLE'

-Design out the OUTER JOINs. Filter the data as much as possible so that the procedure runs on minimum set of data. Use temp tables or table variables to do this.



-If you are using 'NOT IN' then avoid using that. It causes severe overhead to the performance of the query.

-Have a primary key to the table and create non clustered Indexes where ever necessary, this increases the performace surprisingly.

-Avoid using functions in the query, since each row of the data has to be processed through the function, this causes overhead to the performance.



You can read the below articles for detailed information:

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/


http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/ 

http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/

http://www.sommarskog.se/error-handling-I.html
 

Create Non Clustered Index

CREATE NONCLUSTERED
INDEX Index_Name
ON Table_1 (Col_1)
INCLUDE (Col_2, Col_3, Col_4)

Find duplicate rows in a table

SELECT col_1, col_2, count(col_2)
FROM table_1
GROUP BY col_2
HAVING count(col_2) > 1

Select all tables and their row counts

SELECT DISTINCT convert(varchar(30),object_name(i.id)) [Table Name], i.rows FROM sysindexes i INNER JOIN sysobjects o on i.id = o.id

Shrink Database log files in SQL Server 2008

If you want to shrink the databases log file then here is the process for SQL Server 2008.

-Right click on the Database --> Tasks --> Shrink --> Files

-Select the 'File Type' as 'Log'

-Select 'Reorganize pages before releasing unused space' and modify the size.

-Click 'OK'