Feeds:
Posts
Comments

Posts Tagged ‘SQL Query’

Few days earlier, I was working on a legacy application data and found that some characters were merged with the numbers in a column of a table and the issue was, I had to get only the numbers from this column.
Given below are the samples which were available in the column.
129 mph
550 kmp

Let me create a sample to demonstrate the problem and its solution.

USE tempdb
GO
--Create a table
DECLARE @tbl_sample TABLE
(
ID INT,
Varchar_col VARCHAR(10)
)
--Insert sample records in the table
INSERT INTO @tbl_sample VALUES (1,'12.9 mph')
INSERT INTO @tbl_sample VALUES (2,NULL)
INSERT INTO @tbl_sample VALUES (3,'45')
INSERT INTO @tbl_sample VALUES (4,'90 mph')

--Browse the table
SELECT * FROM @tbl_sample

remove trailing characters.1.1

SOLUTION :
Given below is the solution using string manipulation function.

SELECT
 ID
,Varchar_col
,LEFT(Varchar_col
,DATALENGTH(Varchar_col)-
(PATINDEX('%[0-9]%',REVERSE(Varchar_col))-1)) AS [Number]
FROM @tbl_sample

remove trailing characters.1.2

Let me know if you came across this situation and resolved it with the different solution.

Advertisements

Read Full Post »

SQL Server new versions always come up with the new performance optimization techniques, enhancements and additions. FORCESEEK table hint enhancement is also one of the new enhancements that came up in SQL Server 2012.

Let me create an index on Purchasing. [PurchaseOrderDetail] table to demonstrate this enhancement.

USE [AdventureWorks2012]
GO
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderDetail_PurchaseOrderID]
ON Purchasing.[PurchaseOrderDetail]
(
[PurchaseOrderID] ASC
) WITH (DROP_EXISTING = OFF) ON [PRIMARY]

FORCESEEK in earier version of SQL :
In the ealier version of SQL Server, FORCESEEK table hint needed only an index on the table and it could optimize your query. But sometimes you may have more than one index and you cannot define those indexes anywhere in the syntax of forceseek (Limitation). Let me script it to elaborate the same.

---This query will work on SQL Server 2008 and above.
USE [AdventureWorks2012]
GO
Select * from Purchasing.PurchaseOrderHeader as h
Inner join Purchasing.PurchaseOrderDetail as d with (forceseek)
On h.PurchaseOrderID =d.PurchaseOrderID
Where h.TotalDue >50

FORCESEEK in SQL Server 2012 :
In SQL Server 2012, you can define index name along with the column in forceseek table hint to further optimize your query. But you can also use the above syntax  and it will not break your code.

Let me explain it with examples :

Example 1 : (Define the index name in forceseek)

---This query will work on SQL Server 2012 and above.
USE [AdventureWorks2012]
GO
Select * from Purchasing.PurchaseOrderHeader as h
Inner join Purchasing.PurchaseOrderDetail as d with
(forceseek,INDEX(IX_PurchaseOrderDetail_PurchaseOrderID))
On h.PurchaseOrderID =d.PurchaseOrderID
Where h.TotalDue >50

Example 2 : (Define the index name along with the column name in forceseek)

---This query will work on SQL Server 2012 and above.
USE [AdventureWorks2012]
GO
Select * from Purchasing.PurchaseOrderHeader as h
Inner join Purchasing.PurchaseOrderDetail as d
with (forceseek(IX_PurchaseOrderDetail_PurchaseOrderID(PurchaseOrderID)))
On h.PurchaseOrderID =d.PurchaseOrderID
Where h.TotalDue >50

Conclusion:

In SQL Server 2012, FORCESEEK  came up with the very nice enhancement that you can define index name along with the column in FORCESEEK, to have further control on query optimization. However, remember this is the last option we should choose at the time of optimization. Finally, this is only recommended for experienced programmers.

Reference : MSDN

Read Full Post »