February 22, 2012 at 9:14 am
Dear All
In the user tables each table create the alias name dynamically
to increment the alphabetic only..
select o.name from sys.sysobjects o
where xtype='u'
tablename aliasname
studentmaster AAAAA
studentdetails AAABA
coursedetails AAAAC
....
table_name ZZZZZ
any one help me to create the alias name dynamically
Thanks
February 22, 2012 at 10:38 am
your question is not clear, and your pattern does not make sense.
Are you trying to have a value in the column "alias name" increment with letters? On insert or are you calling a function which would create the next value for you?
February 22, 2012 at 11:40 am
WHY? How are you planning on using these aliases? Any queries written using these aliases would be virtually impossible to read. You're better off using aliases that are related to the table name in some way. For example,
StudentMaster AS sm
StudentDetails AS sd
CourseDetails AS cd
Table_Name AS tn
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 24, 2012 at 3:26 am
CELKO (2/23/2012)
WHY? The last time I saw anything like this, the goal was to get physical labels for mag tapes. What are you trying to do?
He need to label rolls of punched tape. Do you remember one?
February 24, 2012 at 3:49 am
Wow. . . OK.
This will generate your 11,881,376 (!!) combinations
SELECT a.alpha+b.alpha+c.alpha+d.alpha+e.alpha
FROM (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) a
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) b
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) c
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) d
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) e
After that, I guess decide on the ordering of your tables with a ROW_NUMBER() then decide on the ordering of the "alias" and assign a ROW_NUMBER() to each of them then finally an INNER JOIN.
February 24, 2012 at 7:32 am
Cadavre: Here is another way to generate the table:
WITHAlphabet
AS(SELECT CHAR(N) AS Letter FROM Tally WHERE N BETWEEN 65 AND 90),
TwoLetter
AS(SELECT a.Letter + b.Letter AS Letters FROM Alphabet a CROSS JOIN Alphabet b),
ThreeLetter
AS(SELECT a.Letters + b.Letter AS Letters FROM TwoLetter a CROSS JOIN Alphabet b),
FourLetter
AS(SELECT a.Letters + b.Letter AS Letters FROM ThreeLetter a CROSS JOIN Alphabet b),
FiveLetter
AS(SELECT a.Letters + b.Letter AS Letters FROM FourLetter a CROSS JOIN Alphabet b)
SELECT*
FROMFiveLetter
ORDER BY Letters
February 24, 2012 at 8:04 am
fahey.jonathan (2/24/2012)
Cadavre: Here is another way to generate the table:
OK, I'll play. . .
Your code:
SET STATISTICS IO ON
SET STATISTICS TIME ON
;WITH t1(N) AS (SELECT 1 UNION ALL SELECT 1),
t2(N) AS (SELECT 1 FROM t1 x, t1 y),
t3(N) AS (SELECT 1 FROM t2 x, t2 y),
Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM t3 x, t3 y),
Alphabet AS(SELECT CHAR(N) AS Letter FROM Tally WHERE N BETWEEN 65 AND 90),
TwoLetter AS (SELECT a.Letter + b.Letter AS Letters FROM Alphabet a CROSS JOIN Alphabet b),
ThreeLetter AS (SELECT a.Letters + b.Letter AS Letters FROM TwoLetter a CROSS JOIN Alphabet b),
FourLetter AS (SELECT a.Letters + b.Letter AS Letters FROM ThreeLetter a CROSS JOIN Alphabet b),
FiveLetter AS (SELECT a.Letters + b.Letter AS Letters FROM FourLetter a CROSS JOIN Alphabet b)
SELECT *
FROM FiveLetter
ORDER BY Letters
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
(11881376 row(s) affected)
Table 'Worktable'. Scan count 4, logical reads 1415000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 56269 ms, elapsed time = 115062 ms.
My code:
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT a.alpha+b.alpha+c.alpha+d.alpha+e.alpha
FROM (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) a
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) b
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) c
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) d
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) e
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
(11881376 row(s) affected)
SQL Server Execution Times:
CPU time = 8924 ms, elapsed time = 69863 ms.
So your code is around 40 seconds slower if we create the tally table on the fly.
If we execute your code against a physical tally table then I get System.OutOfMemoryException on my little test box (can't run on my bigger test box, it's busy), but even that takes 20 seconds longer to just to throw the error than mine does to execute.
There's a small chance that this:
SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',a.N,1)+SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',b.N,1)
+SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',c.N,1)+SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',d.N,1)+
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',e.N,1)
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)) a(N)
CROSS JOIN (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)) b(N)
CROSS JOIN (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)) c(N)
CROSS JOIN (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)) d(N)
CROSS JOIN (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)) e(N)
May be slightly quicker, but I suspect it'll perform about the same.
February 24, 2012 at 8:38 am
I agree that your version is faster, but not by the margin indicated. Most of the difference is created by the ORDER BY clause in "my" code, which is absent in yours.
February 24, 2012 at 8:52 am
fahey.jonathan (2/24/2012)
I agree that your version is faster, but not by the margin indicated. Most of the difference is created by the ORDER BY clause in "my" code, which is absent in yours.
Okies. .
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT a.alpha+b.alpha+c.alpha+d.alpha+e.alpha
FROM (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) a
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) b
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) c
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) d
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) e
ORDER BY a.alpha+b.alpha+c.alpha+d.alpha+e.alpha
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
(11881376 row(s) affected)
SQL Server Execution Times:
CPU time = 63819 ms, elapsed time = 86214 ms.
It's all slow, but still 20 seconds quicker --edit-- on my small test box. I guess the creation time is fairly unimportant, since I suppose you'd create a look-up table with the values in so would only need to execute it once.
February 24, 2012 at 6:41 pm
Sorry... double post removed.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 24, 2012 at 6:43 pm
Cadavre (2/24/2012)
Wow. . . OK.This will generate your 11,881,376 (!!) combinations
SELECT a.alpha+b.alpha+c.alpha+d.alpha+e.alphaFROM (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) a
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) b
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) c
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) d
CROSS JOIN (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha
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)) a(N)) e
After that, I guess decide on the ordering of your tables with a ROW_NUMBER() then decide on the ordering of the "alias" and assign a ROW_NUMBER() to each of them then finally an INNER JOIN.
Now, count the number of combinations that contain swear words. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply