Can you share apllication code? Is it T-Sql, c#, ... ?

Jeff, thank you for pointing perfomance issue out. I would also note that it would be so simple for MS to add parent reference to the sp_dirtree output thus eliminating...

Nulls may be of different types. So nulls in some columns may be a problem. HASHBYTES raises an error on 'untyped' null. Compare

select HASHBYTES('MD5', ''+null);

select HASHBYTES('MD5', cast(null as char(1)));

select...

Try to specify COLLATE for every char column in ORDER BY.

...

ORDER BY container COLLATE Persian_100_CI_AS, isfile, subdirectory COLLATE Persian_100_CI_AS;

Also note the difference between case sensitive and case insensitive collations...

Try

...

Group by OrderID,PackageID, CustomerName, Colors

BTW, i see no Child2ID in your Child2.

And I'd better normalize Child1 and Child2 to simplify queries. It can also really help with...

You may also try FISRT_VALUE(), LAST_VALUE() instead of firstApply, secondApply.

Specify proper collation for the column in ORDER BY

See http://msdn.microsoft.com/ru-ru/library/ms184391.aspx

Not sure which one is for Persian.

This batch will show all objects (directories, files) and their full paths

--parameter

declare @myPath nvarchar(4000) = 'E:\ElectronicArchieve';

IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL

DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (

id int IDENTITY(1,1)

,subdirectory nvarchar(512)

,depth int

,isfile...

I'm a bit confused.

If you already have loaded dir structure into archive as it was discussed here http://www.sqlservercentral.com/Forums/Topic1640110-391-1.aspx then you can just run the last query in the...

OK, then LAG() is the best choice as it was proposed by colleages.

with [AAA_Mytable] as(

select *

from (

values

(12, N'2011', 18314, N'HBPP112', NULL)

,(13, N'2012', 18314, N'BACS315',...

Shadab Shah (12/5/2014)

---

Well, there is no business logic. It can be anything, i was trying out...

kevin_nikolai (12/5/2014)

---

Hi Serg-52

Based on [dbo].[AAA_Mytable], you get the following:

TermCalendarIDRegYearStudentUID Qual Rollover

2 2011 ...

Last portion of @dsql, why not just

DECLARE @dsql varchar(1000) = N'

WITH searchtext(searchtext) AS (SELECT REPLACE(@searchtext,''*'',''%''))

SELECT ParamValue = @ParamValue, ParamCaption = @ParamCaption

FROM '+ /* Your clean string...

There are many ways to do that. For example

select c1 = case when City2 > City1 then City1 else City2 end

, c2 = case when City2 > City1 then City2...

Hi Kevin.

Your second table definition greatly differs from the first one. While

the first one allows something like (don't know if it makes any sense)

TermCalendarIDRegYearStudentUIDQualYear1QualYear2QualYear3QualYear4QualYear5Rollover

220118314HBPP112NULLNULLNULLNULLNULL

320128314NULLBACS315NULLNULLNULLNULL

420138314NULLBACS315NULLNULLNULLNULL

520148314NULLNULLBACS315NULLNULLNULL

620158314NULLNULLNULLBACS315NULLNULL

, the second one has...