Showing posts with label SQL SERVER. Show all posts
Showing posts with label SQL SERVER. Show all posts

order by alphanumeric in sql server

In this article, we will see how to sort the alphanumeric columns. See the below example,

Create a Temporary Table with column name is ID. It contains the alphanumeric values.

CREATE TABLE #temp(id VARCHAR(20))

INSERT INTO #temp VALUES('101')
INSERT INTO #temp VALUES('aaa')
INSERT INTO #temp VALUES('aa')
INSERT INTO #temp VALUES('201')
INSERT INTO #temp VALUES('101')
INSERT INTO #temp VALUES('aba')
INSERT INTO #temp VALUES('301')
INSERT INTO #temp VALUES('53')
INSERT INTO #temp VALUES('Aa')
INSERT INTO #temp VALUES('aAa')
INSERT INTO #temp VALUES('aA')
INSERT INTO #temp VALUES('Aba')

Now see the normal selection

SELECT * FROM #temp

The output is



Normal selection with order

SELECT * FROM #temp ORDER BY ID

The output is


Now we see the selection order with right way.


SELECT * FROM #T
ORDER BY
CASE WHEN ISNUMERIC(id) = 1 THEN right(Replicate('0',21) + id, 21)
WHEN ISNUMERIC(id) = 0 then Left(id + Replicate('',21), 21)
ELSE id
END
 
 The output is ,


 
Now we see the selection without Case sensitive order with right way.
SELECT * FROM #temp
ORDER BY
CASE WHEN ISNUMERIC(id) = 1 THEN right(Replicate('0',21) + id, 21)
WHEN ISNUMERIC(id) = 0 then Left(id + Replicate('',21), 21)
ELSE id
END
COLLATE SQL_Latin1_General_CP850_CS_AS

The output is


Now we see the selection with Case sensitive order.

SELECT * FROM #temp
ORDER BY
CASE WHEN ISNUMERIC(id) = 1 THEN right(Replicate('0',21) + id, 21)
WHEN ISNUMERIC(id) = 0 then Left(id + Replicate('',21), 21)
ELSE id
END
COLLATE Latin1_General_CS_AI

The output is



05:37 | Category: | 0 comments

Distinct within Aggregate Function and case with Distinct - SQL Server

In this Example, When the time of Aggregate Functions calculation, sometimes we need to remove the duplicates from our count, its may be an SUM or COUNT. See the below example for quick understand

CREATE TABLE #DistinctAggregate(id INT IDENTITY(1,1),EmpID INT,DocumentNo INT)

INSERT INTO #DistinctAggregate(EmpID,DocumentNo) VALUES(1,14)
INSERT INTO #DistinctAggregate(EmpID,DocumentNo) VALUES(1,13)
INSERT INTO #DistinctAggregate(EmpID,DocumentNo) VALUES(1,14)
INSERT INTO #DistinctAggregate(EmpID,DocumentNo) VALUES(2,1000)
INSERT INTO #DistinctAggregate(EmpID,DocumentNo) VALUES(2,1000)

SELECT * FROM #DistinctAggregate

SELECT EmpID,COUNT(DISTINCT DocumentNo) TotalDocuments FROM #DistinctAggregate GROUP BY EmpID


The result is like below




The Same Table , I need to calculate the employees whoes are worked in the Document no 14 then use the below query.

SELECT EmpID,COUNT(DISTINCT Case when DocumentNo = 14 THEN  DocumentNo else NULL END) TotalDocuments from #DistinctAggregate
GROUP BY EmpID


The resul is like below

 

This is just an example. Based on our need we will make use of it logic.




05:01 | Category: | 0 comments

ORDER BY class with Case Statement using Variables - SQL Server 2008
Most time we are used a sort option with differen columns is a Dynamic Query. In this example will give a solution for removing the Dynamic Query.

DECLARE @SortBy AS VARCHAR(20)
SET @SortBy = 'EmployeeID'
SELECT * FROM TableName
ORDER BY
          CASE WHEN @SortBy = 'EmployeeId' THEN EmpID
          WHEN @SortBy = 'DomainID' THEN DomainID
          ELSE
                    Location
          END
ASC

The Case Result Value must be a column name of the given table.
04:26 | Category: | 0 comments

Grand Total and SubTotal using Gouping
In this example, we calculated the the customer wise total, Project wise total and Grant total.

First we create a below table, it is having the 4 colums. Here NoOfDocuments means, each user(DoaminID) worked document count as per customer project.

CREATE TABLE #tmpTestGrouping (
Customer VARCHAR(100),
Project VARCHAR(100),
DomainID VARCHAR(100),
[NoOfDocuments] INT
)

INSERT INTO #tmpTestGrouping
SELECT 'UHC', 'ACE - Adjudication','nsuresh',9 UNION ALL
SELECT 'UHC', 'ACE - Adjudication','rkbabu',8 UNION ALL
SELECT 'UHC', 'ACE - Adjudication','vijayans',5.5 UNION ALL
SELECT 'UHC', 'ACE - Adjudication','venkatramanp',7.5 UNION ALL
SELECT 'UHC', 'HN West','veeramainp',9.5 UNION ALL
SELECT 'UHC', 'HN West','jeyavania',2.5 UNION ALL
SELECT 'UMR', 'HN West','gpsuba',1.5 UNION ALL
SELECT 'UHC', 'UBH-PDM','kalai',30 UNION ALL
SELECT 'UHC', 'UBH-PDM','rajkumar',20 UNION ALL
SELECT 'UHC', 'UBH-PDM','deepu',11 UNION ALL
SELECT 'UHC', 'UBH-PDM','prakeerthvijayan',6.5

Select the Table now,

SELECT * FROM #tmpTestGrouping


Now we gets the all results of Grouping with the DocumentsTotal.

SELECT
         GROUPING(Customer) GroupingCustomer,
    GROUPING(Project) GroupingProject,
    GROUPING(DomainID) GroupingDoaminID,
         Customer,
         [Project],
         DomainID,
         SUM ([NoOfDocuments]) AS [NoOfDocuments]
FROM   #tmpTestGrouping
GROUP BY Customer,[Project],DomainID WITH cube




In the above fig, GroupingCustomer, GroupingProject, GroupingDoaminID values only having 0 or 1 that is a boolean. If its 1 means that columns grouped as per the aggregate function which is given by us.


No we need to get the Total, based on the below condition we will get the result as per the columns or clustered columns or the Grand total.
        
SELECT  
CASE WHEN GROUPING(Customer) = 1 AND GROUPING([Project]) = 1 AND
GROUPING(DomainID) = 1 THEN 'Grand Total'
WHEN GROUPING(Customer) = 0  AND GROUPING([Project]) = 0 AND
GROUPING(DomainID) = 1 THEN ''
WHEN GROUPING([Project]) = 1 AND GROUPING(DomainID) = 1 THEN ''
           ELSE Customer END AS Customer,
   
CASE WHEN GROUPING(Customer) = 0 AND GROUPING([Project]) = 1 AND      
     GROUPING(DomainID) = 1 THEN 'Customer Total'
     WHEN GROUPING(DomainID) = 1 AND GROUPING(Customer) = 0 THEN ''
     WHEN GROUPING(Customer) = 1 THEN '' ELSE
CAST([Project] AS VARCHAR) END AS [Project],

CASE WHEN GROUPING(Customer) = 1 AND GROUPING([Project]) = 1 AND
GROUPING(DomainID) = 1 THEN ''
     WHEN GROUPING(Customer) = 0 AND GROUPING([Project]) = 0 AND
GROUPING(DomainID) = 1 THEN 'Project Total'
     WHEN GROUPING(Customer) = 0 AND GROUPING([Project]) = 1 AND
GROUPING(DomainID) = 1 THEN ''
    ELSE DomainID END AS DomainID,SUM ([NoOfDocuments]) AS [NoOfDocuments]
FROM
#tmpTestGrouping
GROUP BY Customer,[Project],DomainID WITH ROLLUP     

The output is like below,




This helps to calculate the totals instead of calculate from our application.
01:35 | Category: | 0 comments

How to assign XML variable value in SP using sql 2008

In this example, first we need to declare a XML variable for assigning, it is like a normal value assignment using SET but we need to put a parenthesis of xml select clause. See below,

DECLARE @PFPRun XML
SET @PFPRun =
(           SELECT
            [vikingid], 
            [System], 
            [totalstrokes], 
            [TotalDocuments], 
            [Quality]
FROM
            tempdb..#tmpOverAllPFP       
FOR
XML PATH('PFPEmployees'),TYPE
)


After assingment, we have sent a bulk table as single parameter from one SP to another SP. See the below,

EXEC USP_PFPReportViewJob  @LOC,@DATE, @CreatedBy,@PFPRun

We also possible to use a Typed parameter or Table Variable instead of using xml
05:45 | Category: | 0 comments

Get Column name using SQL Query
SELECT
column_name 'Column Name',
data_type 'Data Type',
character_maximum_length 'Maximum Length'
FROM
information_schema.columns
WHERE
table_name = 'Your Table Name'
04:58 | Category: | 0 comments

Query with Remote server / OpenQuery
There may be times when you'll want to run an ad hoc query that retrieves data from a remote data source or bulk loads data into a SQL Server table. In such cases, you can use the OpenQuery,OpenRowSet or OpenDataSource function in Transact-SQL

This queries are connects to the destination server and runs the query on that server and returns the resultset. Whereas, in case of linked server query is executed on the local server 


openquery is much faster. Only one problem is the query string is limited to 8k only

See the below examples…

Format:

SELECT * FROM OPENQUERY(remotelinkedservername,Query)

Example:

SELECT * FROM OPENQUERY(GUNBATS,'select * from [BRETRACKER].[dbo].[ViQuality]' )

Format:

SELECT * FROM OPENDATASOURCE ( provider_name,
Connectionstring ).dbname.schemaname.TableName

Example:

SELECT * FROM OPENDATASOURCE('SQLOLEDB',
'Data Source=10.20.36.61;User ID=pfp;Password=xxxx').usrdb.dbo.T_pfpamount

Format:

SELECT * FROM OPENROWSET ( 'provider_name', { 'datasource' ; 'user_id' ; 'password' |
'Connectionstring' } , {[catalog. ] [ schema. ] object | 'query' } )

Example:

SELECT A.* FROM OPENROWSET ('SQLOLEDB','10.20.36.61' ; 'PFP' ;
'xxxx','select * from [usrdb].[dbo].[T_pfpamount]' 
) as a

When you try to execute queries involving OPENROWSET on SQL Server you get the error in some
places because of Ad Hoc Distributed Queries is disabled.

Following are the steps to enable Ad Hoc Distributed Queries on SQL Server:

sp_configure 'show advanced options',1
reconfigure
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

Ad Hoc Distributed Queries are now enabled. If you run the query with OPENROWSET
now you do not get the error again.


Linked Server

The Linked Servers option allows you to connect to another instance of SQL Server running
on a different machine

When you execute a distributed query (query a remote database) against a linked server, you
must include a fully qualified, four-part table name for each data source to query

i.e    linked_server_name.catalog.schema.object_name.

Example

SELECT * FROM [GUNBATS].[BRETRACKER].[dbo].[ViQuality] wher Quality = ‘Y’
05:33 | Category: | 0 comments

Listenable in Sql Server 2008
·         ORDER BY Columns are must appear in the SELECT clause, if you SELECT DISTINCT is specified.

·         All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists


06:51 | Category: | 0 comments

What is the difference between a local and a global variable?
A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.
04:49 | Category: | 0 comments

@@ERROR and Raiseerror
@@ERROR
The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.

Raiseerror
Stored procedures report errors to client applications via the RAISERROR command. RAISERROR doesn’t change the flow of a procedure; it merely displays an error message, sets the @@ERROR automatic variable, and optionally writes the message to the SQL Server error log and the NT application event log.
04:48 | Category: | 0 comments

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. HAVING criteria is applied after the the grouping of rows has occurred.
04:47 | Category: | 0 comments

Difference between Function and Stored Procedure?
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
04:47 | Category: | 0 comments

What is difference between DELETE & TRUNCATE commands
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

TRUNCATE
TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE can not be Rolled back using logs.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.

DELETE
DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE Can be Rolled back using logs.
DELETE is DML Command.
DELETE does not reset identity of the table.
04:46 | Category: | 1 comments

Trigger - After Delete Examples
CREATE TRIGGER trgPFPAmountLog
ON [dbo].[t_PfpAmount]  
AFTER DELETE 
AS 
      INSERT INTO t_PfpAmount_Log
      (
            LocationID,[Month],[Year],
            EmpID,PFPAmount,[System],
            CreatedBy,CreatedOn,
            DBLoginName,SystemIP
      )  
     
      SELECT
                  LocationID,[Month],[Year],
                  EmpID,PFPAmount,[System],CreatedBy,
                  CreatedOn,SYSTEM_USER ,HOST_NAME()
      FROM deleted

05:09 | Category: | 0 comments