December 12, 2007 at 9:01 pm
karthikeyan (12/12/2007)
1) How many types are there like 'P' ?2) I did search in Google,but i didn't get clear information about the table.So can you explain me where we need to apply this table ?I mean ,as you told,In future if i faced the above kind of problem then i would use this table,like this in which situations we can use this table.
1. Try a simple SELECT DISTINCT to find out.
2. The table is an undocumented table. Your Google searches will produce more than I know about the table.
If you see the above example , it uses spt_values table.Like this where we can use this table ?
Yes, although I certainly wouldn't use a cursor 😉 I don't know of all the uses... in fact, I know of very few and I've only learned of them by doing a search in SysComments for the spt_Values table... then, I read the stored procedures where it is found to find out more. You can do the same.
Can you give me the detailed information about master.dbo.spt_values table ? Also can you list out some situations that we can use this table ?
No... I can't because, as I previously stated, it's mostly undocumented. It has a lot of "constants" in it related to SQL Server and anytime I want to know more, I read the system stored procedures that use it.
That's about all the help I can offer on spt_Values.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 16, 2007 at 11:38 pm
Jeff,Thanks a lot for your answer.
karthik
December 17, 2007 at 3:14 am
how to form setbased query Without using spt_values table ? is there any other way to avoid while loop ?
Say for example i have to write a query to display the ABCDEF as said format without using While Loop or spt_values logic.is it possible ?
Kindly give me the answer.
karthik
December 17, 2007 at 3:42 am
Karthik
You've got two options:
(1) Think about it for yourself
(2) Read Jeff's post, which is only 5 above your one and gives you an alternative way of doing what you want to do without using the table in question.
John
December 17, 2007 at 5:29 am
Jeff Moden (12/10/2007)
I'll show how, you explain why...
DECLARE @STR CHAR(6)SET @STR = 'ABCDEF'
SELECT LEFT(@Str,Number)
FROM Master.dbo.spt_Values
WHERE Type = 'P'
AND Number BETWEEN 1 AND 6
ORDER BY Number
Do be advised that there are certain types of RBAR that are faster than "set based"... they're far and few between, but they do exist...
If you use SQL Server 2005, then you can make use of CTE as well
with Pyramid(val,i)as
(
select cast('A' as varchar(8000)) as val,65 as i
union all
select val + char(i+1),i+1 from Pyramid where i<70
)
select val from Pyramid order by i
Failing to plan is Planning to fail
December 17, 2007 at 8:25 am
If you use SQL Server 2005, then you can make use of CTE as well
Ummm.... I'm pretty sure I'll avoid recursive CTE's just like I avoid Cursors... they're just too bloody slow. Consider and run the following code... all it does is produce a list of numbers from 1 to 11,000 using a CTE and using a more classic set based method.
SET NOCOUNT ON
DECLARE @StartTime DATETIME
SET @StartTime = GETDATE()
;WITH Tally(N)
AS (
SELECT 1 AS N
UNION ALL
SELECT N+1 FROM Tally WHERE N<11000
)
SELECT N FROM Tally ORDER BY N
OPTION (MAXRECURSION 32000)
PRINT CONVERT(CHAR(12),GETDATE()-@StartTime,114) + ' CTE Duration'
GO
DECLARE @StartTime DATETIME
SET @StartTime = GETDATE()
--===== Create and populate the Tally table on the fly
IF OBJECT_ID('TempDB..#Tally','U') IS NOT NULL
DROP TABLE #Tally
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO #Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE #Tally
ADD PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON #Tally TO PUBLIC
SELECT * FROM #Tally
PRINT CONVERT(CHAR(12),GETDATE()-@StartTime,114) +' Temp Table Duration'
That produces typical run times as follows...
00:00:00:830 CTE Duration
00:00:00:437 Temp Table Duration
...The CTE is almost twice as slow. And, consider the following;
1. The CTE does NOT persist throughout a proc... it's only good for the current query. If the same values are required further in the code, you have to have another copy of the CTE. With the TempTable, you just use it and, since it's small enough, will probably remain in memory making it lightning quick for subsequent usage.
2. The CTE has a result set that is not indexed. In half the amount of time that it took to generate the CTE, the TempTable was formed and a Clustered index was applied.
Now, while I agree that a straight forward CTE to do the same thing is mighty fast, as in the following code...
DECLARE @StartTime DATETIME
SET @StartTime = GETDATE()
;WITH Tally (N)
AS (
SELECT TOP 11000 ROW_NUMBER() OVER(ORDER BY sc1.ID)
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
)
SELECT * FROM Tally ORDER BY N
PRINT CONVERT(CHAR(12),GETDATE()-@StartTime,114) +' Row_Number Duration'
GO
... consider that the result set is still not indexed and that if you need that same result set further in the proc, it'll still need to be regenerated.
Recursive CTE's are nothing but RBAR on steroids and I suggest everyone avoid them for more traditional set based methods. I also suggest that even though CTE's are handy and you get to brag about doing everything in a single query, more traditional set based coupled with "Divide'n'Conquer" methods will usually beat the pants off of the new toys in SQL Server.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2007 at 12:25 am
Thanks Jeff Moden for the testing
I usually suggest CTE to handle small set of data and to retreive hierarchical data 🙂
Failing to plan is Planning to fail
December 21, 2007 at 2:41 am
Now,i want to split the following string in the below format.
Declare @STR varchar(4000)
set @STR = 'David,karthikeyan'
Expected Format:
D
D A
D A V
D A V I
D A V I D
K A R T H I K E Y A N
K A R T H I K E Y A
K A R T H I K E Y
K A R T H I K E
K A R T H I K
K A R T H I
K A R T H
K A R T
K A R
K A
K
shall i use the above logic to solve it ? If no,Kindly give me some ideas.
Thanks in Advance.
karthik
December 21, 2007 at 3:22 am
karthikeyan (12/21/2007)
Now,i want to split the following string in the below format.Declare @STR varchar(4000)
set @STR = 'David,karthikeyan'
Expected Format:
D
D A
D A V
D A V I
D A V I D
K A R T H I K E Y A N
K A R T H I K E Y A
K A R T H I K E Y
K A R T H I K E
K A R T H I K
K A R T H I
K A R T H
K A R T
K A R
K A
K
shall i use the above logic to solve it ? If no,Kindly give me some ideas.
Thanks in Advance.
Well. Before I answer to this question, I want to ask you a question
"Where do you want to show data?"
Note that you can do almost all type of string manipulations using sql but it really matters if it should be done by sql or by the front end application you use. Note that if you use front end application (VB,.NET,java,etc) it is very easy to extract characters from a string and play with it
Anyway I give you code to do the same in sql
But I advise you to replicate this in your front end application if used [:)]
Declare @STR varchar(4000)
set @STR = 'David,karthikeyan'
select substring(substring(@str,1,charindex(',',@str)-1),1,number) as number from
(
select number from master..spt_values where type='p' and
number between 1 and len(substring(@str,1,charindex(',',@str)-1))
) as t
select substring(substring(@str,charindex(',',@str)+1,len(@str)),1,len(substring(@str,charindex(',',@str)+1,len(@str)))-number+1) as number from
(
select number from master..spt_values where type='p' and
number between 1 and len(substring(@str,charindex(',',@str)+1,len(@str)))
) as t
Failing to plan is Planning to fail
December 21, 2007 at 3:45 am
December 21, 2007 at 7:15 am
karthikeyan (12/21/2007)
Madhivanan,Thanks.Suppose if i have like this,
Declare @STR varchar(300)
@STR = 'Karthikeyan,Madhivanan,JeffModen,Steve-Jhones,MattMiller,GilaMonster'
do we have to repeat your code for 6 times? Suppose if i have around 20 names do we repeat the same query again and again ?
First of all, I've gotta ask... why do you want the names to be in the Pyramid format that you keep using as an example? It doesn't seem practical in any sense and is actually detracting from your learning to do a simple split.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply