tag:blogger.com,1999:blog-89296737041862549542011-08-03T09:53:13.208+05:30Chintakhttp://www.blogger.com/profile/17556337819366435284noreply@blogger.comBlogger101100tag:blogger.com,1999:blog-8929673704186254954.post-43475628408110837712009-08-14T21:30:00.005+05:302009-08-14T21:32:33.653+05:30

--The below script will show why we need to regularly update full text index, if the change_tracking is not set to Auto--Creating database--There are two filegroups. The second flegroup will be used for the storing the full text objects.--But, this is not a mendetory.Createdatabase FullTextSearch on primary (name = Data, filename = 'c:\FulltextSearch\data.mdf'), FILEGROUP fulltextfilegroup (name=

Chintakhttp://www.blogger.com/profile/17556337819366435284noreply@blogger.com0tag:blogger.com,1999:blog-8929673704186254954.post-3465225540121195592009-07-13T20:11:00.007+05:302009-07-13T20:18:42.826+05:30

/************************************************************************************Description: This script file will truncate the log file. When we run this script for first time this can take some time. So it is advisable that we run after working hours. Though this script can be run online, it is advisable to run the script when very minimal user activity is happening.Warning : This should

Chintakhttp://www.blogger.com/profile/17556337819366435284noreply@blogger.com0tag:blogger.com,1999:blog-8929673704186254954.post-58995124274819492362009-07-09T21:46:00.003+05:302009-07-14T00:46:00.057+05:30

Yesterday, one of my colleagues was asking me about using order by in the view.But, when he is getting error "ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."Another restriction is he can not add column to the data view.But this can be easily done using the top 100 percent in the select query

Chintakhttp://www.blogger.com/profile/17556337819366435284noreply@blogger.com0tag:blogger.com,1999:blog-8929673704186254954.post-40306879476987783162009-03-26T20:38:00.001+05:302009-03-26T22:36:57.128+05:30

Today, I came across the situation where we need to update the text column and we want all the column text to UPPER CASE. But, we do not want to update the rows for which this text column already contains the word in upper case.use AdventureWorksGOif object_id('tblCheckCase') is not nulldrop table tblCheckCaseGOCreate table tblCheckCase(id int identity(1,1) primary key,firstName nvarchar(10))

Chintakhttp://www.blogger.com/profile/17556337819366435284noreply@blogger.com0tag:blogger.com,1999:blog-8929673704186254954.post-58773832642457876702009-01-28T15:43:00.001+05:302009-07-09T17:19:54.754+05:30

This caluse can be used to get additional rows from the base result set with the same value .That is if the last of returned result set consists the same vlaue of order by item for other rows, those rows will also be returned.- This can be only used in select statemnet.- Order by clause is necessary for using this clause- This can also be used with PERCENT clause.Example:-if object_id('test_top',

Chintakhttp://www.blogger.com/profile/17556337819366435284noreply@blogger.com0tag:blogger.com,1999:blog-8929673704186254954.post-91637763122522363102008-10-21T14:52:00.000+05:302008-10-21T14:58:28.578+05:30

We use % and _ in wildcard search with like clause but, some time we need to search the same chars from the string. So below example shows how to tackle this situationFor that we can use the ESCAPE caluse with like clause. we just have to define a ESCAPE char that will ESCAPE the next char from search string.if object_id('test','U') is not nulldrop table TASK_DETAILGOcreate table task_detail(id

Chintakhttp://www.blogger.com/profile/17556337819366435284noreply@blogger.com0tag:blogger.com,1999:blog-8929673704186254954.post-66440919653944336912008-10-20T18:16:00.002+05:302009-03-31T21:25:43.079+05:30

Lots of sripts available for checking index usage, but below script also list down the index key columns and included columns.We just have change the database and table name in below script.declare @databaseName nvarchar(256)declare @tableName nvarchar(256)select @databaseName = 'test'select @tableName = 'script_test'select Object_name(sys.dm_db_index_usage_stats.object_id) as tableName,

Chintakhttp://www.blogger.com/profile/17556337819366435284noreply@blogger.com0tag:blogger.com,1999:blog-8929673704186254954.post-47887423985798871182008-10-03T17:12:00.001+05:302009-03-31T21:24:59.357+05:30

Another difference between union and union allAs we know the difference between union and union all is that, if not specify ALL keyword duplicate rows are removed.Today while writing a script, I found another interesting difference regarding the order of returned result set. Union use to change the order of result set based on first column in result set in ascending order, while union all does

Chintakhttp://www.blogger.com/profile/17556337819366435284noreply@blogger.com0tag:blogger.com,1999:blog-8929673704186254954.post-33487345032303846672008-09-16T11:37:00.000+05:302009-01-29T10:54:32.675+05:30

"Option Strict" is a compiler option that's there to help you from introducing logic errors.We can set this option to off at the beggining on the script task to remove such error.for example,Option Strict OffImports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.Runtime

Chintakhttp://www.blogger.com/profile/17556337819366435284noreply@blogger.com0tag:blogger.com,1999:blog-8929673704186254954.post-19447169959412311812008-09-04T16:38:00.000+05:302008-09-04T16:40:21.235+05:30

Sometimes we get error "Arithmetic overflow" while working with bigger vlauesWhen you execute query: “select 99999*99999”, it will generate the same error “Arithmetic overflow error converting expression to data type int”. This is occurring because SQL server tries to calculate result in terms if INT, but INT can store max value of 2,147,483,647.But when you execute query: “select convert(bigint,

Chintakhttp://www.blogger.com/profile/17556337819366435284noreply@blogger.com0