Happy Coding ...

-- TO REMOVE ALL UNWANTED TERMS FROM THE STRING AND DISPLAY CHINESE , RUSSIAN AND ENGLISH, DUTCH, GERMAN....  CHARACTERS   CREATE PROCEDURE   [dbo] . [XXX]           @UserName nvarchar ( 32 )         AS         BEGIN           -- SET NOCOUNT ON added to prevent extra result sets from           -- interfering with SELECT statements.           SET NOCOUNT ON ;            Declare @strText as nvarchar ( 500 )        select @strText = Replace ( SearchText , N'^' , N'' )        from TABLENAME   where UserName = @UserName                 ...

The ALTER DATABASE command allows a database administrator to modify SQL Server databases and their files and filegroups. This includes permitting the changing of database configuration options. Why Read Only ? When you need to ensure that the data is a database is not modified by any users or automated processes, it is useful to set the database into a read-only mode. Once read-only, the data can be read normally but any attempts to create, updated or delete table rows is disallowed. This makes the read-only mode ideal when preparing for data migration, performing data integrity checking or when the data is only required for historical reporting purposes. Make Database Read Only USE  [master] GO ALTER DATABASE  [TESTDB]  SET  READ_ONLY  WITH  NO_WAIT GO Make Database Read/Write USE  [master] GO ALTER DATABASE  [TESTDB]  SET  READ_WRITE  WITH  NO_WAIT GO In case you get the following error message ...

It is often necessary to change the database to single user mode, especially if you are a DBA. A simple example would be to change the collation settings or any DB settings. The single user will allow only one  user ( usually DBA) to access the database. Hence it will be easy to make changes without the worry of deadlocks or any other type of contention for DB and also without affecting the users. It is very easy to change the database to Single user mode; in fact, it is just an execution of the script away. Use the script below to change the mode. ALTER  DATABASE  < >   SET SINGLE_USER    WITH  NO_WAIT The  NO_WAIT  clause will set it to single user mode as soon as you execute the query.  An alternate  to this is by using the  system  stored procedure  sp_dboption EXEC     SP_DBOPTION    << Data base  Name>>, ‘SINGLE USER...

There are four levels in the naming convention for any SQL Server object [ServerName.[DatabaseName.[SchemaName.]]]ObjectName Schema Name (or Ownership) --the object created is assigned to a schema rather than an owner. Whereas an owner related to one particular login, a schema can now be shared across multiple logins, and one login can have rights to multiple schemas --For object not belonging to default schema state, use the schema name of your object. The Default Schema: dbo::::::::::::::::::::::::::::: --for user of a database MySchema(login name) a table my.table created will have a ownerqualified object name would be MySchema.MyTable. So to access this table we need to use the name MySchema.MyTable (as this is created by a user) --for database owner fred, a table created as myTable , ownerqualified object name would be dbo.MyTable. ****as dbo also happens to be the default owner, any user could just refer to the table as MyTable. --sa (sysadmin role)will always ha...

To read the XML file in SQL we need to use sp_xml_preparedocument and : Syntax sp_xml_preparedocument hdoc OUTPUT -- Is the handle to the newly created document. hdoc is an integer. [ , xmltext ] -- original XML document. [ , xpath_namespaces ] Example DECLARE @hdoc INT, @params_xml XML = ' ' EXEC sp_xml_preparedocument @hdoc OUTPUT, @params_xml The above command, reads the XML text provided as input, parses the text by using the MSXML parser sp_xml_preparedocument returns a handle that can be used to access the newly created internal representation of the XML document. This handle is valid for the duration of the session or until the handle is invalidated by executing sp_xml_removedocument. A parsed document is stored in the internal cache of SQL Server. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory. PUT XML into variables OPENXML provides a rowset view over an XML d...

Step1: Get the Servers Run the following command to get the list of all linked servers. sp_linkedservers Note: sp_helpserver can also be used to list the available servers Step 2: Add the server you want to connect to [This is important, because most people mess up here] To add a linked server we will use the following command sp_addlinkedserver EXEC sp_addlinkedserver @server=N'S1_instance1', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'S1\instance1'; Step 3: Query the Active Directory DECLARE @Application TABLE (cn varchar(50)); DECLARE @ApplicationCN varchar(50); DECLARE @SQLString nvarchar(MAX); DECLARE @ApplicationName varchar(20)= 'yy' -- name of the container DECLARE @Role varchar(20) = 'xxx' DECLARE @Domain nvarchar(20) = 'a.com' -- if this is a.com SET @SQLString='SELECT cn FROM OPENQUERY(ADSI,''SELECT cn FROM ''''LDAP://' +@Domain +''''...

To get a list of TRIGGERS in SQL database use : SELECT      S2.[name] TableName     , S1.[name] TriggerName     , CASE      WHEN S1.deltrig > 0         THEN 'Delete'     WHEN S1.instrig > 0          THEN 'Insert'     WHEN S1.updtrig > 0          THEN 'Update'     END 'TriggerType'  FROM sysobjects S1 JOIN sysobjects S2  ON S1.parent_obj = S2.[id]  WHERE S1.xtype='TR' Want more Detail? Use : SELECT      S2.[name] TableName     , S1.[name] TriggerName     , CASE          WHEN S2.deltrig = s1.id              THEN 'Delete'          WHEN S2.instrig = s1.id              THEN 'Insert'          WHEN S2.updtrig = s1....

In a FOR XML clause, you specify one of these modes: RAW - generates a single <row> element per row in the rowset that is returned by the SELECT statement AUTO - generates nesting in the resulting XML by using heuristics based on the way the SELECT statement is specified EXPLICIT -  provides the most flexibility in generating the XML PATH - a simpler alternative to writing EXPLICIT mode queries Examples for RAW:    Query 1:  Basic                       SELECT doc_id ,                                      media_id                       FROM DOC   ...

Reading a value from the XML file and displaying it in SQL DECLARE @MyXML XML SET @MyXML = ' ' 'print convert(nvarchar(500), @MyXML) SELECT a.b.value('a[1]/BibCodingSearch[1]/@CaseID','varchar(10)') AS CaseID, a.b.value('a[1]/BibCodingSearch[1]/@BibCoding_Id','varchar(10)') AS BibCoding_Id, a.b.value('a[1]/BibCodingSearch[1]/@Value','varchar(10)') AS Value, a.b.value('a[1]/BibCodingSearch[1]/@Operator','varchar(10)') AS Operator FROM @MyXML.nodes('xml') a(b) UNION SELECT a.b.value('a[1]/BibCodingSearch[2]/@CaseID','varchar(10)') AS CaseID, a.b.value('a[1]/BibCodingSearch[2]/@BibCoding_Id','varchar(10)') AS CaseIDBibCoding_Id, a.b.value('a[1]/BibCodingSearch[2]/@Value','varchar(10)') AS Value, a.b.value('a[1]/BibCodingSearch[2]/@Operator','varchar(10)') AS Operator FROM @MyXML.nodes('xml') a(b)

To create a New full text catalog follow the following steps 1. Go to Database => Storage => Full Text Catalog 2. Right clcik and select 'New Full Text Catalog' 3. Enter the name in 'Full Text Catalog Name' (Leave Accent Sesitivity as default). Click OK 4. Right click on the Catalog (you just created) and click on Properties 5. Go to Table/View tab and select the table/view that needs to be assigned to the catalog 6. Select the eligible column and the language for word breaker 7. Let the Track Changes be 'Automatic' . Click on 'Ok' For more info on Full Text visit   http://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/

SQL Server programmers can choose between two functions in SQL Server for converting expressions from one type to another. In many cases there will be a need within a stored procedure or other routine to convert data from, say, a datetime type to a varchar type; CONVERT and CAST are used for such things. Because SQL Server provides both functions, there may be some confusion about which is best to use and under what circumstances. CONVERT is specific to SQL Server , and allows for a greater breadth of flexibility when converting between date and time values, fractional numbers, and monetary signifiers. CAST is the more ANSI-standard of the two functions, meaning that while it's more portable (i.e., a function that uses CAST can be used in other database applications more or less as-is), it's also less powerful. CAST is also required when converting between decimal and numeric values to preserve the number of decimal places in the original expression. For those reasons, it...

I heard my PM saying today "Their are two types of Idiots. One who will take multiple trips to the database to compare value. Others who will take the values in memory and compare then their". We have a situation. We need to take values from one table and check if it exists in the second table. Depending on weather it exists in the second table or not, we need to display with / without highlight it in the datagrid. There is one catch. The query is written by the user in one of the application screen and the user has no idea that the 2nd condition table needs to be applied. I know you would debate that we need to create a screen, so that the user will select what is required instead of writing the query. I know... but we need this asap. Screen will be in the next release. Let me elaborate: Consider that we have table A and table B. Both these tables have ID column. The user writes (on the application screen) SELECT * FROM A   I can check that this ID does not exist in ...

Naming Convention in SQL Stored Procedure.   sp<Application Name>_[<group name >_]<action type><table name or logical instance> Where action is: Get, Delete, Update, Write, Archive, Insert… i.e. verb  Example: spApplicationName_GetUserDetails        spApplicationName_UpdateEmails Note :Do not prefix stored procedure names with “SP_”, as “SP_” is reserved for system stored procedures. Triggers: TR_<TableName>_<action><description>  Example: TR_Emails_LogEmailChanges , TR_UserDetails_UpdateUserName Indexes :  IX_<tablename>_<columns separated by_>  Example: IX_UserDetails_UserID Primary Key : PK_<tablename>  Example: PK_UserDetails PK_ Emails Foreign Key : FK_<tablename_1>_<tablename_2>  Example: FK_UserDetails_Emails Default:  DF_<table name>_<column name>  Example: DF_ UserDetails _UserName Tables, Views, Stored Procedur...

If you are using SQL Server 6.5 you could query the system table SYSLOGS, by using the following statement: SELECT xactid AS TRAN_ID, CASE op WHEN 0 THEN 'BEGINXACT Start Transaction' WHEN 1 THEN 'Sysindexes Change' WHEN 2 THEN 'Not Used' WHEN 3 THEN 'Not Used' WHEN 4 THEN 'INSERT Insert Row' WHEN 5 THEN 'DELETE Delete Row' WHEN 6 THEN 'INSIND Deferred Update step 2 insert record' WHEN 7 THEN 'IINSERT NC Index Insert' WHEN 8 THEN 'IDELETE NC Index Delete' WHEN 9 THEN 'MODIFY Modify Row' WHEN 10 THEN 'NOOP' WHEN 11 THEN 'INOOP Deferred Update step 1 insert record' WHEN 12 THEN 'DNOOP Deferred Update step 1 delete record' WHEN 13 THEN 'ALLOC Allocate Page' WHEN 14 THEN 'DBNEXTID Allocate Next Object ID' WHEN 15 THEN 'EXTENT Allocate Empty Extent' WHEN 16 THEN 'SPLIT Page split' WHEN 17 THEN 'CHECKPOINT' WHEN 18 THEN 'SAV...

Do's: Writing comments whenever something is not very obvious, as it won’t impact the performance.  (--) for single line  (/*…*/) to mark a section Use proper indentation Use Upper Case for all SQL keywords. SELECT, UPDATE, INSERT, WHERE, INNER JOIN, AND, OR, LIKE. Use BEGIN... END block for multiple statements in conditional code  Use Declare and Set in beginning of Stored procedure Create objects in same database where its relevant table exists otherwise it will reduce network performance. Use PRIMARY key in WHERE condition of UPDATE or DELETE statements as this will avoid error possibilities. If User table references Employee table than the column name used in reference should be UserID where User is table name and ID primary column of User table and UserID is reference column of Employee table. Use select column name instead of select * Use CTE (Common Table Expression); its scope is limited to the next statement in SQL query, instead of...

I was reviewing this application created by my colleague and was wondering, do we need a stored procedure for single insert statements. Could this be not written directly in the code? First, let me tell you what is a Stored Procedure is. A Stored Procedure is a group of Transact-SQL statements (DDL & DML) compiled into a single execution plan. It contain programming statements that perform operations in the database, including calling other procedures. A proc can accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch. There are various advantages of a stored procedure (sometimes called a proc, sproc, StoPro, StoredProc, or SP) over inline code. The most important being 'Security' and 'Speed'. Compilation and storing of the query execution plan - Increases Speed & Reduces Network Traffic When SQL Server runs a query it calculates the most efficient method of processing the query and stores it in s...

Data deduplication, often called "intelligent compression" or "single-instance storage", is a process that uses matching logic to eliminate file records that are duplicates (dupes). It is a method of reducing storage needs by eliminating redundant data and replacing it with a pointer to the unique data copy Data deduplication offers other benefits. Lower storage space requirements will save money on disk expenditures. The more efficient use of disk space also allows for longer disk retention periods, which provides better recovery time objectives (RTO) for a longer time and reduces the need for tape backups. Data deduplication also reduces the data that must be sent across a WAN for remote backups, replication, and disaster recovery. Deduping is a 3 step process Step 1: Move the non duplicates (unique tuples) into a temporary table SELECT * into new_table FROM old_table WHERE 1 GROUP BY [column to remove duplicates by]; Step 2: Delete the old table. We no ...

Create Database CREATE DATABASE  -----> syntax *****you can use the CREATE statement only to create objects onthe local server CREATE DATABASE [ON [PRIMARY] ([NAME = <’logical file name’>,] FILENAME = <’file name’> [, SIZE = ] [, MAXSIZE = size in kilobytes, megabytes, gigabytes, or terabytes>] [, FILEGROWTH = ])] [LOG ON ([NAME = <’logical file name’>,] FILENAME = <’file name’> [, SIZE = ] [, MAXSIZE = size in kilobytes, megabytes, gigabytes, or terabytes>] [, FILEGROWTH = ])] [ COLLATE ] [ FOR ATTACH [WITH ]| FOR ATTACH_REBUILD_LOG| WITH DB_CHAINING ON|OFF | TRUSTWORTHY ON|OFF] [AS SNAPSHOT OF ] [;] CREATE DATABASE Accounting ON (NAME = ‘Accounting’, FILENAME = ‘c:\Program Files\Microsoft SQL Server\ MSSQL.1\mssql\data\AccountingData.mdf’, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5) LOG ON (NAME = ‘AccountingLog’, FILENAME = ‘c:\Program Files\Microsoft SQL Server\ MSSQL.1\mssql\data\AccountingLog.ldf’, SIZE = 5...

Converting from bytes to terabytes is very easy considering that terabyte = 1024*Gigabyte              = 1024 * 1024 * Megabyte              = 1024 * 1024 * 1024 KiloByte              = 1024 * 1024 * 1024 * 1024 * Byte Query below gives number of documents in a media and the total size of the documents. See how you have used Alias in the query SELECT        [MEDIA] = X.media_id,         [TOTAL] = COUNT(*),       [Size_KB] = SUM (Size_MB) FROM       (       SELECT              d.edoc_id,              d.media_id ,             SIZE_MB =  (                         CAST(ISNULL(d.Size1,0) AS DECIMAL) +      ...

In SQL we have four different ways to "store" data in a table: local temporary tables (#table_name), global temporary tables (##table_name), permanent tables (table_name), table variables (@table_name).   Local Temporary Tables CREATE TABLE #people ( id INT, name VARCHAR(25) )   A temporary table is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions. The data in this #temp table (in fact, the table itself) is visible only to the current scope (usually a stored procedure, or a set of nested stored procedures). The table gets cleared up automatically when the current procedure goes out of scope, but you should manually clean up the data when you're done with it:   DROP TABLE #people   This will be better on resources ("release early") than if you let the system clean up *after* the current session finishes the r...