That's used if you delete a variable-length column from a table and want to release the space that...

Hi Phil and Steve I am very sorry about my font size.

my expected result like this

Name        Salary          Salary_rank

Shiva        8000 ...

Without seeing the query it's difficult to say exactly what could improve it. But something I've done before with successful results is to split a query into more than one...

I think you could do it with LEAD or LAG on two columns and a COALESCE

If you install the function here: DateRange Table Valued function

You can use nova's SQL but call the table valued function instead of a calendar table:

SELECT id,
CASE...

Hi Aaron, Yes, good idea to remove the cross apply and put the logic in the CTE. Also, changing the IIF to CASE means it should work on older versions...

Create the table:

IF OBJECT_ID('tempdb..#myTable','U') IS NOT NULL
    DROP TABLE #myTable

SELECT *
  INTO #myTable
  FROM (VALUES (1, 203, 1232),
    ...

Our employer once told us that there was a ban on telling anyone your salary. Later it became illegal (in the uk) for an employer to enforce such a rule....

If you know which row you want to use from table dbo.Ref_ICD_10_edition4 then you can change the update to use a cross apply with select top(1) and order by.

            
August 28, 2019 at 10:51 am
            
#3674997

            
            
                                                
        

1. Yes, SQL Server uses the transaction logs to apply all changes to the previous backup, if you set it to simple then shrink it would lose this data so...

if you want it in one line then here it is, but I wouldn't recommend it

;WITH CTE AS (SELECT 'urn:ADL:CC_LB_SF_EN_D_C_%5BLB_LS_TCD%5D_%5BLB_LS_CotCF%5D_28_1' Original)
SELECT W.Original, Z.Final
  FROM CTE W
...

Just paste in your original query and I'll show you how it can be done.

I was hoping to have it as 1 select statement as I have to join it to other data.  I do appreciate what you showed me however, as it...

;WITH CTE AS
(
    SELECT 'urn:ADL:CC_LB_SF_EN_D_C_%5BLB_LS_TCD%5D_%5BLB_LS_CotCF%5D_28_1' Original
),
CTE1 AS
(
    SELECT *,
           REPLACE(REPLACE(CTE.Original,'%5B','['),'%5D',']') SquareBrackets
...

Seeks show efficient use of the index.

The optimiser would rather do a scan of a clustered instead of a scan on an index if it thinks a lot of rows...