Viewing 15 posts - 796 through 810 (of 1,228 total)
Hi Ki
I now where you're going with this construction (APPLY with row generation), it makes sense and looks cool - but it's quite expensive. In this case, plain old-fashioned syntax...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 27, 2011 at 3:43 pm
If you can't write to the database, then and only then your best option is a recursive CTE, as follows:
DROP TABLE #MySample
CREATE TABLE #MySample (ID INT, [counter] INT)
INSERT INTO #MySample...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 20, 2011 at 3:43 pm
JamesMorrison (10/20/2011)
The pound is only $1.57 conversion rate. That means the upper end is only $63,000 per year?Is that correct? Are BI developers really paid that low in the UK?
Our...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 20, 2011 at 2:34 pm
It's easy to think Sort = bad, but that's not necessarily the case, just...often.
SET STATISTICS TIME OFF;
PRINT '===== Mark''s Code (98% of batch) ================================================================================='
SET STATISTICS TIME ON;
WITH AllAlpha(Letter) AS (
...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 20, 2011 at 1:24 pm
Heh all finished? Here's another way:
SELECT String = SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', start, length)
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26)) t1 (start) ...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 20, 2011 at 12:41 pm
ekant_alone (10/17/2011)
The possible values in the table are 'm' or 'f'
In this puzzle i need to swap m with f and f...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 17, 2011 at 12:24 pm
SQL Kiwi (10/17/2011)
SQL Kiwi (10/17/2011)
One thing you might like to look at, to eliminate that eager spool: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2008/09/22/distinct-aggregation-considered-harmful.aspx
Just remembered: there's an optimizer improvement in Denali SQL Server 2012 RC0 (not...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 17, 2011 at 12:07 pm
Aspet Golestanian Namagerdi (10/15/2011)
If I remove the select statment runs very fast.Is there anyway to remove that select statment.this code has been generated from Stored Procedure...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 15, 2011 at 5:54 am
An index containing childid and parentid (and nothing else).
You could also consider creating the clustered index over these two columns - unique if possible.
What other columns are there in...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 15, 2011 at 5:29 am
You could pivot the result of a rCTE but this is easier to code and tons easier to get your head around:
SELECT n1.PARENT_ID, n1.CHILD_ID, n2.CHILD_ID, n3.CHILD_ID, n4.CHILD_ID, n5.CHILD_ID
FROM TABLE1 n1
LEFT...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 15, 2011 at 1:46 am
Jack Corbett (10/7/2011)
SELECT
tblUCSU.strLocationName,
COUNT(dbo.tblTACSU.Pen) AS Trunks,
...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 8, 2011 at 1:00 am
jared-709193 (10/3/2011)
If this query:
SELECT CHARINDEX('</t2>', data)
FROM TableName
WHERE CHARINDEX('</t2>', data) < 4
returns ANY data, (which it has already stated it does) the substring will fail every time on the data...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 3, 2011 at 11:43 pm
komal145 (10/3/2011)
I have table T11234
And table T2
3456
And my result set should be
123456
Is there any way other than union?
Yes, several, but union (without ALL) would be best. Why do you...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 3, 2011 at 3:57 pm
jim.powers (10/3/2011)
That isn't the point at which the problem is occurring. ...
Nope, but it sure will make it easier to find - this time and the next.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 3, 2011 at 3:44 pm
DROP TABLE #Tablename
CREATE TABLE #Tablename (ID INT, [Data] text)
INSERT INTO #Tablename (ID, [Data])
SELECT 1, 'sdfjksdhfksdhkfhsdkfhksdhfksdhfksdhf<t2>net.rim.blackberry</t2><t3>1</t3><t4>sadfsfsdfdsf</t4><t5>asdasdasdsa</t5><t6>7.0.0</t6><t7>asdasdasdsa</t7><t8>asdasdasdas</t8><t9>asdasdasd;</t9></R>'
union all
SELECT 2, 'sdfjksdhfksdhkfhsdkfhksdhfksdhfksdhf<t2>net.rim.blackberry<t2><t3>1</t3><t4>sadfsfsdfdsf</t4><t5>asdasdasdsa</t5><t6>7.0.0</t6><t7>asdasdasdsa</t7><t8>asdasdasdas</t8><t9>asdasdasd;</t9></R>'
-- this will return all of the string following '<t2>'...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 3, 2011 at 3:40 pm
Viewing 15 posts - 796 through 810 (of 1,228 total)