Sql Code Masterz

 Avoid duplicate entry while using insert into .... select  statement        Insert into Names        select name.Value as name, abr.Value as abr         from  name        INNER JOIN   abr ON name.RowID = abr.RowID        where NOT EXISTS (select 1                  from Names a                  where a.name =name.Value                  and a.abr = abr.Value)

Find and Delete duplicate records Find Duplicate Records using SQL        SELECT name, COUNT(name) AS namecount        FROM tbl_content        GROUP BY name        HAVING ( COUNT(name) > 1)  Delete Duplicate Records using SQL        delete T1 from tbl_content T1, tbl_content T2 where T1.name = T2.name and T1.id > T2.id  Delete Duplicate Records using Access       delete from       MyTable       where uniqueField not in       (select min(uniqueField) from       MyTable T2       where T2.dupField=MyTable.dupField)

Remove the string after a particular character(Making Sub string) This code may be used where we want to remove a the string after a particular character. Example :- Suppose your column have like this - ID Name 1 MNIT,Jaipur 2 GVSET,JAipur 3 MSJ,Bharatpur etc. Now you want to remove the cities name from this column. You can do this by simply using this code -         UPDATE Table_Name SET Name= dbo.substr(Name) Function should be look like this-            CREATE FUNCTION dbo.substr(@string nVARCHAR(MAX))      RETURNS nVARCHAR(MAX)          BEGIN                   DECLARE @SpaceIndex TinyInt                   SET @SpaceIndex = CHARINDEX(',', @string)           ...

Remove Spaces in columns in sql table         CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))         RETURNS VARCHAR(MAX)              BEGIN                   RETURN LTRIM(RTRIM(@string))              END        GO SELECT dbo.TRIM(' aaaaa strig ') or UPDATE Table_name SET columnname= dbo.TRIM(columnname)) or WITH trimmed AS (SELECT LTRIM(RTRIM(fld1)) as fld1, LTRIM(RTRIM(fld2)) as fld2 ....) SELECT... You can use following query for removing white spaces from all the columns UPDATE Table_name SET columnname = REPLACE ( columnname , ' ' , '' ),columnname1=     REPLACE ( columnname1 , ' ' , '' ) and so on for as many columns as you have.

CHARINDEX: Get index of the delimiting space .Net Framework has very useful String.IndexOf that returns position of first occurrence of given char or substring in string. Although there is no function named IndexOf in T-SQL you can achieve same result with CHARINDEX and PATHINDEX function. CHARINDEX function as counterpart of IndexOf() CHARINDEX function has the following syntax: CHARINDEX(expression1, expression2 [, start_location]) expression1 - sequence of characters to be found expression2 - column or expression that is searched start_location - optional parameter, start position of searching Example that search for first position of word "chart" in ProductDescription column could look like: SELECT CHARINDEX ( 'chart' , ProductDescription ) FROM Products PATINDEX function PATINDEX function works very similar to CHARINDEX. Unlike CHARINDEX, PATINDEX function has only two parameters and first parameter is pattern that can include wildcard p...

Count the days in current month- Count the total number of days in current month 1.) select day(dateadd(mm,1, getdate())-day(getdate())) 2.) SELECT (CASE MONTH(GETDATE()) WHEN 1 THEN 31 WHEN 2 THEN (CASE YEAR(GETDATE())%4 WHEN 0 THEN 29 ELSE 28 END) WHEN 3 THEN 31 WHEN 4 THEN 30 WHEN 5 THEN 31 WHEN 6 THEN 30 WHEN 7 THEN 31 WHEN 8 THEN 31 WHEN 9 THEN 30 WHEN 10 THEN 31 WHEN 11 THEN 30 WHEN 12 THEN 31 END) AS LastDayOfMonth

Microsoft SQL Server roles Introduction Roles are the new SQL Server 7.0 feature, which was not available in the previous versions. SQL Server roles act like Windows NT local groups. Microsoft SQL Server supports several different types of roles. There are: server roles database roles application roles In this article, I want to tell you about each kind of roles, about how you can add new role, how you can drop existing role, how you can return information about the roles, and so on. Server Roles Server roles are defined at the server level and exist outside of users databases. There are only fixed server roles, so you cannot add, delete or modify server role. You can only add users as a member of the server roles. There are seven fixed server roles: sysadmin serveradmin setupadmin securityadmin processadmin dbcreator diskadmin The members of sysadmin server role can perform any activity in SQL Server and have completes control over all database fun...

Filestream Storage As the name may suggest, Filestream enables the storage of unstructured data such as documents and videos.  This feature integrates with the NTFS files system by using the nvarchar(max) data type to store data on the file system.  This feature is great for when you’re currently storing documents inside of your SQL Server system that are larger than 1 MB and fast read access is important. Sparse Columns Sparse columns are very similar to other types of database columns, except that they are specialized and optimized for null values.  If you have a table that holds a lot of NULL values, first consider your table design.  If your design is solid or cannot be altered, then your table may be a candidate to take advantage of sparse columns.  The rule from Microsoft is to consider using Sparse columns when 20-40 percent of the values in a column will be NULL. New Date & Time Data Types Prior to SQL Server 2008, there was no way to native dat...

What's new in SQL Server 2008 SQL Server 2008 will be released in 2 nd quarter of 2008. SQL Server 2008 will also be part of a joint launch with Windows Server 2008 and Visual Studio 2008 on February 27, 2008 in Los Angeles, CA. Transparent Data Encryption SQL Server 2008 enables encryption of entire databases, data files, and log files, without the need for application changes. Encryption enables organizations to meet the demands of regulatory compliance and overall concern for data privacy. Some of the benefits of transparent data encryption include searching encrypted data using either range or fuzzy searches, more secure data from unauthorized users, and data encryption. These can be enabled without changing existing applications. External Key Management SQL Server 2008 provides a comprehensive solution for encryption and key management. To meet the growing need for greater security of information within data centers, organizations have invested in vendors to ...

Deleting Columns and Parameters A few types of objects appear in Schema View , even though you cannot delete them from Schema View . The examples are columns in a table and parameters of a stored procedure or function. Both these types of objects appear in Schema View so that you can better understand the table, procedure, or function without having to open its definition. However, each column and parameter is defined within the file that defines the entire table, procedure, or function. To delete these items, you must modify the definition for the table, procedure, or function. To delete a column from a table If Schema View does not appear, open the View menu, point to Other Windows , and click Schema View . In Schema View , click the table that contains the column that you want to delete. - or - In Solution Explorer , click the file containing the table that contains the column that you want to delete. ...

How to: Delete Database Objects To delete a database object from a database project , you can delete the object from Schema View , or you can delete the file that contains the object definition from Solution Explorer . You must follow a different process to delete table columns and the parameters of a stored procedure or function. After you delete one or more objects from a database project, you must build and deploy the changes to your database server. Deleting Database Objects To delete an object from a database project If Schema View does not appear, open the View menu and click Schema View . In Schema View , click the object that you want to delete. On the Edit menu, click Delete . You can also click the object and press the DELETE key. A confirmation dialog box appears. Click OK in the confirmation dialog box. The object is deleted from the database project in b...

MySQL Empty Database / Delete or Drop All Tables MySQL drop all tables syntax: DROP DATABASE { mysql-database-name } Method #1: Empty database with root user In order to use this procedure you must have the drop and create database privilege (otherwise you will drop database but not able to create it again). Login as MySQL root or admin user to drop atomstore database: $ mysql -u root -p Now drop database: mysql> DROP DATABASE atomstore; Now create database again: mysql> CREATE DATABASE atomstore; Exit and close the session: mysql> quit Method #2: Drop all tables using shell script w/o root access I've small handy shell script that removes all tables without dropping and creating MySQL database again. #!/bin/bash MUSER= "$1" MPASS= "$2" MDB= "$3"   # Detect paths MYSQL= $ ( which mysql ) AWK= $ ( which awk ) GREP= $ ( which grep )   if [ $# -ne 3 ] then echo "Usage: $...

Find and Replace a particular substring in a particular row of sql server declare @SearchStr as nvarchar(50)   declare @SearchStr2 as nvarchar(50)   declare @ReplaceStr as nvarchar(50)   declare @SQL nvarchar(4000)   set @SearchStr='&'   SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')   set @ReplaceStr='and'   SET @SQL=    'UPDATE tbl_us_h_bkp SET Name =  REPLACE(Name, '                         + QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +                         ') WHERE name LIKE ' + @SearchStr2                 EXEC (@SQL)

This summary is not available. Please click here to view the post.