;WITH sample_data (resultA, resultB, resultC, resultD) AS (

SELECT 'chicago', 'ID123', '20pax', 'ID123' UNION ALL

SELECT 'austria', 'ID999', '90pax', 'ID999' UNION ALL

SELECT 'Spain', 'ID777', '10pax', 'ID777')

SELECT x.newcol1, x.newcol2

FROM sample_data

CROSS APPLY (

VALUES...

If all of the objects referenced in the query are remote and on the same server, then run the query on the remote server. The two obvious ways to do...

Correct syntax is to use the...

Frank Cazabon (3/10/2016)

---

Thanks Chris, that CTE is incredibly faster!

Is it possible to use CTE in a view definition? All this code relies on this view so it would be...

Those plans suggest that the number of rows returned is only a small fraction of the total, in which case Paul White's super-quick rCTE-based DISTINCT equivalent might be worth a...

Junglee_George (3/10/2016)

---

I wiil use the NVARCHAR 4K version of [DelimitedSplitN4K] for splitting the input parameter string.

If you can help me in rewriting the stoed proc usp_StatusHistory , it will be...

Junglee_George (3/10/2016)

---

I am posting the required code.

ALTER FUNCTION [dbo].[eufn_e5_eLIMS_SE_Parse_Delimited_List](

@listString nvarchar(4000),

@delimiter nvarchar(1)

)

RETURNS @listTable Table( listItem nvarchar(50) )

AS

BEGIN

DECLARE @pos int

DECLARE @itemString nvarchar(50)

SET @listString = LTRIM(RTRIM(@listString))+ @delimiter-- add trailing comma

SET @Pos...

Junglee_George (3/10/2016)

---

I have posted the complete code for the stored proc here.

I need a alternate way that code which cause the error can be avoided.

I have not developed this code...

souLTower (3/9/2016)

---

Please try the following. I haven't tried it but I've used a similar approach in other queries.

Select Items, Qty From Table1

Where

QTY >

-- This can be...

Phil Parkin (3/9/2016)

---

ganapathy.arvindan (3/9/2016)

---

Thanks !!! Same Case statement(same code) used in 4 select statements in single SP so i thought instead of writing 4 times,whether there is anyway i can...

mw112009 (3/8/2016)

---

Select top 100 CONVERT( CHAR(8), LAB_SCR_DT, 112 ) , * FROM LAB_IN

This will not work..( below )

Create INDEX IDX_334455 on LAB_IN ( CONVERT(...

ganapathy.arvindan (3/9/2016)

---

case when AR=1 then ' Calling'

when AR=2 then 'Closed'

when AR=3 then 'Completed'

when AR=5 then 'Resend to A'

when AR=6 then 'Resend to C'

when AR=7 then...

There might...

terry999 (3/8/2016)

---

Maybe I should bite the bullet and make the PK (varchar(12)) a clustered index.

Inserts happen in random order with respect to PK values. So it would become fragmented quickly....

Try this:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT

TableName = SCHEMA_NAME(t.[schema_id]) +'.'+t.Name,

IndexName = i.name,

s.last_user_seek,

s.last_user_scan,

s.last_user_lookup,

s.last_user_update

FROM sys.tables t

LEFT JOIN sys.dm_db_index_usage_stats s

ON s.[object_id] = t.[object_id]

AND s.database_id = DB_ID()

LEFT JOIN...