Avoid duplicate entry while using insert into .... select statement

 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)

             if @SpaceIndex>0         

                 set @string=( LTRIM(RTRIM(LEFT(@string, @SpaceIndex - 1))))

             else

                 set @string=(LTRIM(RTRIM(@string))) 

             RETURN @string

        END

    GO

Call By Using Below Function-

        UPDATE Tbl_Content SET Name= dbo.substr(Name)

Remove Spaces in columns in sql table

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_nameSETcolumnname= REPLACE(columnname,' ',''),columnname1= 

     REPLACE(columnname1,' ','')

and so on for as many columns as you have.

CHARINDEX: Get index of the delimiting space

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 parameters. Syntax is like this:

PATINDEX('%pattern%', expression)

pattern - string that optionally can contain wildcard characters (%)

expression - column or expression to be searched

Example that solves the same problem as above would be:

SELECT PATINDEX('chart', ProductDescription) FROM Products

CHARINDEX vs. PATINDEX

So, there are two solutions that simulate C# or VB.NET IndexOf() function. The logical question is, which is better and what is the difference between them?

CHARINDEX has three parameters and it is better choice if you need to specify search starting position.

PATINDEX supports patterns with wild characters _ or %, or match range with [ ] and [^ ] so you have much more options to define what you want to find.

How to simulate LastIndexOf() in T-SQL?

You can simulate LastIndexOf with CHARINDEX or PATINDEX combined with REVERSE function. REVERSE function, as the name suggests, returns reverse of character sequence. So LastIndexOf implementation example that finds last position of word "chart" in column ProductDescription would be:

SELECT CHARINDEX('chart', REVERSE(ProductDescription)) FROM Products

Using of CHARINDEX or PATINDEX as substitute for LIKE keyword

One more use of CHARINDEX and PATINDEX functions is to find whether or not given string exists in column or expression. Instead of LIKE keyword, you can use example like this that returns all rows that contain word "chart" in ProductDescription column:

SELECT * FROM Products WHERE CHARINDEX('chart', ProductDescription) > 0



Example -

DECLARE @FullName VarChar(25), @SpaceIndex TinyInt SET @FullName = 'www.java2s.com' -- Get index of the delimiting space: SET @SpaceIndex = CHARINDEX('java', @FullName)
-- Return all characters to the left of the space: SELECT LEFT(@FullName, @SpaceIndex - 1) GO

Output

-------------------------

(1 rows affected)

Count the total number of days in current month

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 functions.



The members of serveradmin server role can change server configuration parameters and shut down the server.



The members of setupadmin server role can manage linked servers (add or remove linked servers), manage replication, manage extended stored procedures, and execute some system stored procedures, such as sp_serveroption.



The members of securityadmin server role can create and manage server logins and auditing, and read error logs.



The members of processadmin server role can manage the processes running in SQL Server.



The members of dbcreator server role can create, alter, and resize databases.



The members of diskadmin server role can manage disk files.



Note. To add a login as a member of a fixed server role, you can use the sp_addsrvrolemember system stored procedure.



This is the syntax:



sp_addsrvrolemember [@loginame =] 'login', [@rolename =] 'role'



where



@loginame - is a SQL Server login or a Windows NT user account.

@rolename - is the name of the fixed server role.



Note. To remove a SQL Server login or a Windows NT user or group from a fixed server role, you can use the sp_dropsrvrolemember system stored procedure.



This is the syntax:



sp_dropsrvrolemember [@loginame =] 'login', [@rolename =] 'role'



where



@loginame - is the name of a login to remove.

@rolename - is the name of the fixed server role.





Note. To return a list of the SQL Server fixed server roles, you can use the sp_helpsrvrole system stored procedure.



This is the syntax:



sp_helpsrvrole [[@srvrolename =] 'role']



where



@srvrolename - is the name of the fixed server role.

Database Roles

In SQL Server 6.5 you can use database groups to simplify management of a large number of database users. For example, you can use database groups to grant and revoke permissions to more than one user at the same time.

But database groups are no longer supported in SQL Server 7.0. SQL Server database roles act like SQL Server 6.5 database groups, but roles have some improvements: in SQL Server 6.5 each user can be a member of only one group (in addition to public group), but in SQL Server 7.0 each user can belong to many roles and the result users permissions are combined for all roles they're members of.



There are three kinds of the database roles:

Fixed Database Roles

Public Role

User-Defined Database Roles

Fixed Database Roles

Fixed database roles are defined at the database level and exist in each database. You cannot add, delete or modify fixed database roles. You can only add users as a member of a fixed database role.



There are nine fixed database roles:

db_owner

db_accessadmin

db_datareader

db_datawriter

db_ddladmin

db_securityadmin

db_backupoperator

db_denydatareader

db_denydatawriter

The members of db_owner database role can perform any activity in the database.



The members of db_accessadmin database role can add or remove Windows NT groups, users or SQL Server users in the database.



The members of db_datareader database role can see any data from all user tables in the database.



The members of db_datawriter database role can add, change, or delete data from all user tables in the database.



The members of db_ddladmin database role can make any data definition language commands in the database.



The members of db_securityadmin database role can manage statement and object permissions in the database.



The members of db_backupoperator database role can back up the database.



The members of db_denydatareader database role can deny permission to select data in the database.



The members of db_denydatawriter database role can deny permission to change data in the database.



Note. To add a security account as a member of an existing SQL Server database role in the current database, you can use the sp_addrolemember system stored procedure.



This is the syntax:

sp_addrolemember [@rolename =] 'role', [@membername =] 'security_account'

where



@rolename - is the name of the database role.

@membername - is the name of the security account.



Any member of a fixed database role can add other users to this role.





Note. To remove a security account from a SQL Server role in the current database, you can use the sp_droprolemember system stored procedure.



This is the syntax:

sp_droprolemember [@rolename =] 'role', [@membername =] 'security_account'

where



@rolename - is the name of the role.

@membername - is the name of the security account.





Note. To return information about the members of a role in the current database, you can use the sp_helprolemember system stored procedure.



This is the syntax:



sp_helprolemember [[@rolename =] 'role']



where



@rolename - is the name of a role in the current database.

Public Role

The public role is a special database role to which every database user belongs. The public role contains default access permissions for any user who can access the database. This database role cannot be dropped.

User-Defined Database Roles

Although the built-in database roles handle permissions for common database management tasks, it's likely that you will want to group users who have access to perform specific database functions.



Note. To create a new SQL Server role in the current database, you can use the sp_addrole system stored procedure.



This is the syntax:



sp_addrole [@rolename =] 'role' [,[@ownername =] 'owner']



where



@rolename - is the name of the database role.

@ownername - is the owner of the new role.





Note. To remove a SQL Server role from the current database, you can use the sp_droprole system stored procedure.



This is the syntax:



sp_droprole [@rolename =] 'role'



where



@rolename - is the name of the role.

Application Roles

Application roles are the SQL Server roles created to support the security needs of an application. Often database applications enforce their own security based on the application logic. For example, you can use application role with its own password to allow the particular user to obtain and modify any data only during specific hours. So, you can realize more complex security management within the application logic.



Note.To add a special type of role in the current database, which is used for application security, you can use the sp_addapprole system stored procedure.



This is the syntax:



sp_addapprole [@rolename =] 'role', [@password =] 'password'



where



@rolename - is the name of the application role.

@password - is the password for the new application role.



Note. To remove an application role from the current database, you can use the sp_dropapprole system stored procedure.



This is the syntax:



sp_dropapprole [@rolename =] 'role'



where



@rolename - is the name of the application role.





Note. To change the password of an application role in the current database, you can use the sp_approlepassword system stored procedure.



This is the syntax:



sp_approlepassword [@rolename =] 'role', [@newpwd =] 'password'



where



@rolename - is the name of the application role.

@newpwd - is the new password for the application role.

What's new in SQL Server 2008 - Some More

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 data type to store time related data.  Time data was only available as part of the datetime (or smalldatetime) data types.  With SQL Server 2008, TIME is a separate data type, able to hold time values such as ‘23:59:59.9999999′.  Along with the new TIME data type, there are additional data types such as datetime2, which holds additional nanosecond data and the datetimeoffset data type, which can hold datetime data that is timezone aware.

Spatial Data Types

Two new data types are included in SQL Server 2008; geometry and geography.  These data types hold information regarding the physical location and shape of geometric objects, which allow for applications to be built that are geographically aware.

Dependency Reporting

Reporting dependencies has never been consistent or reliable in SQL Server.  The difficultly is that when objects are added, modified or dropped, special actions must be taken by the database engine to ensure that the dependency chain is correct.  In 2008, new dynamic management views are included keep track of these dependencies so that reliable reporting can be done one these objects.

Advanced Auditing Features

SQL Server 2008 includes several new features that facilitate the auditing of data.  These features include a new Auditing feature that allows you to easily create customized audits of database engine events.  Change Data Capture, while not auditing specific, makes data changes typically used for loading a data warehouse easily available in an easily used format.  SQL Server 2008 also includes a Data Collector feature that allows the DBA to gather and compare data that is gathered from several different sources.

Policy Based Administration

This is one of my favorite features of SQL Server 2008.  This feature allows a database administrator (DBA) to easily and uniformly set policies across servers that ensure system rules are met.  These policies can be rules such as simplifying administration tasks, preventing unauthorized system changes, making sure code compliance is met, ensuring best practices are met.

TSQL Intelli-sense

SQL Server Management Studio 2008 includes Intelli-sense which is a familiar feature in other Visual Studio products.  This feature presents a popup box when typing SQL statements to give options to choose columns from tables that are involved in your queries.

Central Management Servers

This feature allows the database administrator (DBA) to administer multiple SQL Servers easily and efficiently.  These servers allow for multi-server queries and for policy based management policies to be executed against multiple servers at the same time.

Compound Operators

This form of syntactic sugar is present in many other programming languages such as C++ or C#.  Consider the following TSQL statement.

SET @x = @x + 1

Using compound operators, the above statement can be rewritten in the following manner:

SET @x += 1

This statement, while not groundbreaking, takes TSQL a step forward in the evolution of programming languages.

Transparent Encryption

As data becomes more and more sensitive, the ability to keep the data secret becomes more important.  SQL Server 2008 includes the ability to encrypt data stored in the database transparently, which keeps you from having to alter applications to take advantage of the feature.  This feature prevents unauthorized users from reviewing the data even if they have the backup files.

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 manage security keys within the enterprise. SQL Server 2008 provides excellent support for this need by supporting third-party key management and hardware security module (HSM) products.

Enhanced Auditing

SQL Server 2008 improves compliance and security by allowing you to audit activity on your data. Auditing can include information about when data has been read, in addition to any data modifications. SQL Server 2008 has features such as enhanced configuration and management of audits in the server, which enable organizations to meet varied compliance needs. SQL Server 2008 can also define audit specifications in each database, so audit configuration can be ported with databases. Filtering of audits to specific objects allows better performance in audit generation and flexibility in configuration.

Enhanced Database Mirroring

• Automatic page repair. SQL Server 2008 enables the principal and mirror computers to transparently recover from 823 and 824 errors on data pages by requesting a fresh copy of the corrupted page from the mirroring partner.
• Improved performance. SQL Server 2008 compresses the outgoing log stream in order to minimize the network bandwidth required by database mirroring.
• Enhanced supportability
• SQL Server 2008 includes additional performance counters to enable more granular accounting of the time spent across the different stages of Database Management System (DBMS) log processing.
• SQL Server 2008 includes new Dynamic Management Views and extensions of existing views to expose additional information about mirroring sessions.

Hot Add CPU

Extending existing support in SQL Server for adding memory resources online, Hot Add CPU allows a database to be scaled on demand. In fact, CPU resources can be added to SQL Server 2008 on supported hardware platforms without requiring application downtime.

Performance data collection

Performance tuning and troubleshooting are time-consuming tasks for the administrator. To provide actionable performance insights to administrators, SQL Server 2008 delivers more extensive performance data collection, a new centralized data repository for storing performance data, and new reporting and monitoring tools.

Extended Events

SQL Server Extended Events is a general event-handling system for server systems. The Extended Events infrastructure is a lightweight mechanism that supports capturing, filtering, and acting upon events generated by the server process. This ability to act upon events allows users to quickly diagnose run time problems by adding contextual data, such as Transact SQL call stacks or query plan handles, to any event.  Events can be captured into several different output types, including Event Tracing for Windows (ETW).  When Extended Events are output to ETW, correlation with operating system and database applications is possible, allowing for more holistic system tracing.

Backup compression

Keeping disk-based backups online is expensive and time consuming. With SQL Server 2008 backup compression, less disk I/O is required, less storage is required to keep backups online, and backups run significantly faster.

Data compression

Improved data compression enables data to be stored more effectively and reduces the storage requirements for your data. Data compression also provides significant performance improvements for large input/output-bound workloads such as data warehousing.

Resource Governor

SQL Server 2008 enables organizations to provide a consistent and predictable response to end users with the introduction of Resource Governor. Resource Governor enables database administrators to define resource limits and priorities for different workloads, which enables concurrent workloads to provide consistent performance to end users.

Plan Freezing

SQL Server 2008 enables greater query performance stability and predictability by providing new functionality to lock down query plans, enabling organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.

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

1. 
   If Schema View does not appear, open the View menu, point to Other Windows, and click Schema View.
   
2. 
   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.
   
3. 
   On the View menu, click Open.
   
   The file that contains the table definition opens in the Transact-SQL (T-SQL) editor.
   
4. 
   Modify the table definition to remove the column that you want to delete.
   
5. 
   On the File menu, click Save TableName.Table.sql.
   
   The changes are saved, and the table definition is updated in Schema View. The column that you deleted no longer appears.
   

To delete a parameter from a stored procedure or function

1. 
   If Schema View does not appear, open the View menu, point to Other Windows, and click Schema View.
   
2. 
   In Schema View, click the stored procedure or function that contains the parameter that you want to delete.
   
3. 
   On the View menu, click Open.
   
   The file that contains the procedure or function definition opens in the Transact-SQL (T-SQL) editor.
   
4. 
   Modify the procedure or function definition to remove the parameter that you want to delete.
   
5. 
   On the File menu, click SaveProcedureName.proc.sql or SaveFunctionName.function.sql.
   
   The changes are saved, and the procedure or function definition is updated in Schema View. The parameter that you deleted no longer appears.
   

How to: Delete Database Objects

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

1. 
   If Schema View does not appear, open the View menu and click Schema View.
   
2. 
   In Schema View, click the object that you want to delete.
   
3. 
   On the Edit menu, click Delete. You can also click the object and press the DELETE key.
   
   A confirmation dialog box appears.
   
4. 
   Click OK in the confirmation dialog box.
   
   The object is deleted from the database project in both Solution Explorer and Schema View. If the database project is under version control, when you delete the object, the project file will be checked out. Deleting an object from Schema View also deletes all child objects. For example, if you delete a table, all keys, triggers, indexes, constraints, and statistics are also deleted.
   
   For the change to be reflected on the database server, you must build and deploy your changes.
   

To delete the file that contains an object definition from a database project

1. 
   In Solution Explorer, click the file that represents the object that you want to delete.
   
2. 
   On the Edit menu, click Delete. You can also click the object and press the DELETE key.
   
   A confirmation dialog box appears.
   
3. 
   Click OK in the confirmation dialog box.
   
   The object is deleted from the database project in both Solution Explorer and Schema View. If the database project is under version control, when you delete the object, the project file will be checked out. If the object you deleted had child objects, such as indexes or keys on a table, the child objects will appear in the Orphaned Objects folder in Schema View. Errors will appear in the Error List indicating that the parent object cannot be found.
   
   For the change to be reflected on the database server, you must build and deploy your changes.
   

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: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name}"
 echo "Drops all tables from a MySQL"
 exit 1
fi
 
TABLES=$($MYSQL -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )
 
for t in $TABLES
do
 echo "Deleting $t table from $MDB database..."
 $MYSQL -u $MUSER -p$MPASS $MDB -e "drop table $t"
done

S

Find and Replace a substring in sql in a column of sql table

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)

How to search and replace SQL Server data in all columns of all tables, in a given database?

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