Saturday, December 29, 2012

SQL Server – Delete the duplicate record (data) form table


 It is very easy to delete duplicate record from table in sql server. SQL Server always stores each tupple (Row) as unique into the table.

To see duplicate record, we can use the count function with group by clause with having in the condition.

 To delete the record to Max function with NOT IN keyword.

Just execute and see how it work to delete duplicate record  into table.

USE tempdb
GO
CREATE TABLE Jainendra_TestTable (My_ID INT, Rank_Col VARCHAR(50))
Go
INSERT INTO Jainendra_TestTable (My_ID, Rank_Col)
SELECT 1, 'First'
UNION ALL
SELECT 2, 'No Rank'
UNION ALL
SELECT 3, 'Second'
UNION ALL
SELECT 4, 'Second'
UNION ALL
SELECT 5, 'Second'
UNION ALL
SELECT 6, 'Third'
UNION ALL
SELECT 7, 'Five'
UNION ALL
SELECT 8, 'Second'
UNION ALL
SELECT 9, 'Five'
UNION ALL
SELECT 10, 'Nine'
UNION ALL
SELECT 11, 'Third'
GO

-- See the inserted data in create table
SELECT *
FROM Jainendra_TestTable
GO

-- Now below query is detecting duplicate records into table

SELECT Rank_Col, COUNT(*) TotalCount

FROM Jainendra_TestTable GROUP BY Rank_Col

HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC

GO

-- Now below query is deleting the duplicate record into table

DELETE FROM Jainendra_TestTable
WHERE My_ID NOT IN
( SELECT MAX(My_ID) FROM Jainendra_TestTable GROUP BY Rank_Col)

GO

-- Selecting Data
SELECT *
FROM Jainendra_TestTable
GO
DROP TABLE Jainendra_TestTable

GO


If it is useful than please like and share it to other SQL Server learners

Like and Share to SQL Integrity Blog

Friday, October 19, 2012

SQL SERVER - How you maintain production database in daily basic



 How to maintain production database in daily basic. There are some basic steps, we can check/ consider / follow to it, To maintain the SQL database-


  • Is all database is online or not
     
  • Typically, I check and verify scheduled to database backup.
     
  • All jobs run successfully and completed.
     
  • See to Job History / Log file Viewer
     
  • See to Job Activity Monitor
     
  • Check the database size.
     
  • Current transaction
     
  • Error log and Agent error log.








Like and Share to SQL Integrity Blog

Saturday, October 13, 2012

SQL SERVER - Step by Step deployment of package in SQL Server.


 Here I am sharing very basic and simple step of deployment of package in SQL Server which are as follows:-

1.      Create package-using BIDS.

2.      Right click on the solution in BIDS and select property.

3.      Set true second option that is CearteDeploymentUtility in property window.

4.      You can also change the path of package to change the 3rd property in property windows.

5.      Go the stored location of package, and open the Bin folder, and into open the deployment folder (C:\ssisdeployement\ssisdeployement\bin\Deployment) where you will get the two files one is .dtsx and second is .manifest extension files.

6.      Run the manifest file and select the storage type of file system; choose the path of package (C:\Program Files\Microsoft SQL Server\100\DTS\Packages\ssisdeployement). We can select the file system or SQL database itself. Next and finish it.

7.      Now open the deployed location of the package (C:\Program Files\Microsoft SQL Server\100\DTS\Packages\ssisdeployement) and run it, and set the source server connection and target server connection.

8.      Open SSMS and SSIS in SQL Server.

9.      Open stored package navigate it and right click on the package and select configuration manager, select  the used connection and

10.  Run the package. And see the result in SSMS

11.  If you want to run package in scheduled time, we need to create a job and can schedule the package in SQL Agent.

Like and Share to SQL Integrity Blog

SQL SERVER - How to create Full Text Search in SQL Server


Just follow this simple steps for Full Text Search in SQL Server
 
1.      Open the Solution Explorer in SSMS and navigate to database
2.      Into the database navigate to Storage Folder
3.      Rigth click on the full text Catalog and provide the Catalog name and press OK button
4.      Now open to database table, and right click on the table and go into the   Full Text Index option and select the ‘Define Full Text Index’ option.
5.      Then Full Text search wizard will open and click on next and choose the Indexed column or Identity column (i.e. EmployeeID) from drop down list, and when will select query will fire this indexed column must be in a query (See Example – EmployeeID).
6.      Next, then select the Column name (i.e. Title) for full text searching and select the language. Then click Next
7.      select any one the change track that are automatic(for default), manual and Do not change track, then Click Next
8.      Select the Catalog name and next and Finish.
9.      Then run the below query on SSMS.

Examples :-
   -- This query will search every Column of the table

SELECT EmployeeID , [Title]   FROM [Employee]
         where freetext (*, 'Accountant or Manager')

   -- or it will search only Title Column

SELECT EmployeeID , [Title]   FROM [Employee]
where  contains (Title , 'Accountant AND Marketing')

Like and Share to SQL Integrity Blog

Sunday, September 23, 2012

SQL SERVER - How to verify Backup FIle before restoring

Verify backup-   There are some script to check and verify existing database before restoring. SQL Server has provided some Keywords that are mentioning below:-
  1. Verifyonly
  2. Headeronly
  3. Filelistonly
  4. Labelonly
-- - Checks to see that the backup set is complete and that all volumes are readable
restore verifyonly from disk='C:\Bacukup\Test_backup_DB.bak'

 ---Returns a result set containing all the backup header information for all backup sets on a particular backup device
restore headeronly from disk='C:\Bacukup\Test_backup_DB.bak'

 --- Returns a result set containing a list of the database and log files contained in the backup set.
restore filelistonly from disk='C:\bacukup\Test_backup_DB.bak'

 --sample for error message:
restore verifyonly from disk='C:\Test\Test_Error_BackUP.bak'

 --ingormation and software version information
RESTORE LABELONLY FROM DISK='C:\Backup\Full_Test_Error_BackUP.bak'

Like and Share to SQL Integrity Blog

Friday, September 14, 2012

SQL SERVER – How to see how much percent of backup has been completed while taking backup

SQL SERVER – How to see how much percent of backup has been completed while taking backup

There is simple command to get the level of percentage of backup in SQL SERVER.
When we have to take backup of big database at that time we can add the STATS option and can see the percentage of running backup

-- Creating test backup
CREATE DATABASE TESTDB
GO
--creating test table
CREATE TABLE TEST_TABLE (ID INT)
GO
-- Taking bacup with STATS option
BACKUP DATABASE TESTDB  TO DISK= 'C:\TESTDB.BAK' WITH STATS =10
GO


Like and Share to SQL Integrity Blog

Monday, September 10, 2012

SQL SERVER - What is RAISERROR with details and Example


Using RAISERROR, we can throw our own error message while running our Query or Stored procedure.
·        It allows developers to generate their own messages
·        It returns the same message format that is generated by SQL Server Database Engine
·        We can set our own level of Severity for messages
·        It can be associated with Query and stored procedure
·        ERROR message can have 2047 character and show only 2044

-- syntax 


RAISERROR ( { Message ID | Message Text} { ,severity ,state }
    [ ,argument [ ,...n ] ] )
    [ WITH option [ ,...n ] ]

·        Custom error message ID should be greater than 5000.
·        Severity option should between 0 to 25 and for fatal error 20 to 25
·        State option is default set 1 but we can set 1 to 127
·        With option can set for log it can true or false like as:-

 Example:

exec sp_addmessage @msgnum=50010,@severity=1,@msgtext='my custom error message text',@with_log='true'

We can see error massage in sys.messages view and using sp_addmessage procedure we can add new custom error message in sys.messages view.
exec sp_addmessage @msgnum=50009,@severity=1,@msgtext='Adding Custom Error Message'

OR
 
            SELECT * FROM sys.messages

Like and Share to SQL Integrity Blog

Friday, August 31, 2012

SQL SERVER – Concept, Benefits and Use of Resource Governor in SQL Server


Concept Resource Governor in SQL Server
Benefits of Resource Governor in SQL Server 
Use of Resource Governor in SQL Server
Example of Resource Governor in SQL Server 
Resource POOL in Resource Governor in SQL Server  
Classification Resource Governor in SQL Server 
Workload Group Resource Governor in SQL Server   
How to Drop  Resource Governor in SQL Server   
How to  Disable Resource Governor in SQL Server
How to reset statistic Resource Governor in SQL Server   
Resource Governor in SQL Server  
Error: Cannot drop user-defined function dbo.ResourceClassifier. It is being used as a resource governor classifier.
 


SQL server 2008 has provided this feature to mange and workload of resources of SQL Server instance and system database.  Resource Governor can manage the resource utility for SQL Server and using it we can specify the memory and CUP limits for requesting application or task. It is available in SSMS, Object explorer -> Management tab.


There are only 3 concepts in Resource Governor in SQL Server

Resource POOL: When we install new SQL Server it’s create 2 Resource Pool that is Internal and Default. We can also add User Defined Resource Pool.
Workload Group: When we install new SQL Server it’s create 2 Resource Workload Group that is Internal and Default. We can also add User Defined Resource Workload Group. Work group are mapped with respective Resource Pool.
Classification: It is support is user defined function to classifier. Its work is to classify of incoming request and decides the way of internal rule.

Example:  I have to handle the work load of named MYPOOL application and this application work as Data mining which perform operation on huge databases. So now we can handle to define the limit of the resource and utilization of SQL Server.

Step 1: To Create POOL, Workload Group
--Resource pool to be used by named MyPool Application
CREATE RESOURCE POOL My_Pool
WITH
(
 MIN_CPU_PERCENT=50, MAX_CPU_PERCENT=100,
 MIN_MEMORY_PERCENT=50, MAX_MEMORY_PERCENT=100
)
GO
--create New resource pool to be used by named Reporting Application
CREATE RESOURCE POOL My_Report_Pool
WITH
(
 MIN_CPU_PERCENT=25, MAX_CPU_PERCENT=100,
 MIN_MEMORY_PERCENT=25, MAX_MEMORY_PERCENT=100
)
GO
--Workload Group to be used by named MyPool Application
CREATE WORKLOAD GROUP My_Group
    USING My_Pool ;
GO
--Workload Group to be used by named Reporting Application
CREATE WORKLOAD GROUP My_Report_Group
    USING My_Report_Pool ;
GO   

Step 2: To create User-Defined Function for User.

USE master;
GO
CREATE FUNCTION dbo.ResourceClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
 --Declare the variable to hold the value returned in sysname.
 DECLARE @My_Workload_Group AS SYSNAME
 --If the user login is 'bulk_admin', than only map the connection to ---the My_Group Group workload group.
 IF (SUSER_NAME() = 'bulk_admin')
  SET @My_Workload_Group = 'My_Group'
 --If the user login is 'sa',than only map the connection to
 --the My_Report_Group workload group.
 ELSE IF (SUSER_NAME() = 'sa')
  SET @My_Workload_Group = 'My_Report_Group'
 ELSE
  SET @My_Workload_Group = 'default'
 RETURN @My_Workload_Group
END
GO
Step 3: To register created function and reconfigure, Disable and Resetting the statistics of Resource governor.

--Now we have to register the classifier for user-defined function and -- update the in memory configuration.
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=dbo.ResourceClassifier);
GO
--Enabling Resource Governor(By default when you install
--SQL Server, Resource Governor is disabled)
--It loads the stored configuration metadata into memory
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
--To disabling whole Resource Governor
ALTER RESOURCE GOVERNOR DISABLE
GO
--The below T-SQL will used as resets statistics on all workload groups and resource pools.
ALTER RESOURCE GOVERNOR RESET STATISTICS
GO
Step 4: All data clean if it is not required
--NOTE- Clean all data after testing this example
Drop RESOURCE POOL My_Pool
drop WORKLOAD GROUP My_Group
DROP WORKLOAD GROUP My_Report_Group
DROP RESOURCE POOL My_Report_Pool
ALTER RESOURCE GOVERNOR WITH  (CLASSIFIER_FUNCTION=null)
DROP FUNCTION dbo.ResourceClassifier
--NOTE- if you will direct use the DROP FUNCTION <FUNCTION NAME> Commnad then it will return the following error so you have to use below commnad to resolve it.

Msg 10920, Level 16, State 1, Line 1
Cannot drop user-defined function
dbo.ResourceClassifier. It is being used as a resource governor classifier.

ALTER RESOURCE GOVERNOR WITH  (CLASSIFIER_FUNCTION=null)
DROP FUNCTION dbo.ResourceClassifier

Step 5: The Following snap shot shown the create POOL, Workload Group and Classification:-



This above example can be done using also Window wizards

Like and Share to SQL Integrity Blog