A totally hypothetical and insane requirement, incontestably prone to excessive criticism (to put it mildly) from Joe Celko, if he ever happens to visit my blog :)
But, lets just assume that the requirement is warranted and we need a solution in SQL Server 2000.
I have a table with this definition.
CREATE TABLE BAD_ENCRYPTION(VAL_ID INT, VALUE VARCHAR(100))
I asked a few of my colleagues whether they knew that there were templates in SQL Server. Their responses were typically either "Is it?" or "Yeah. Saw it once when I was trying to create a new query. So?"
When Microsoft tries to pack in so many features into SQL Server 2005 aimed at improving the life of developers, why haven't they done much on the templates, which I feel, is a powerful tool for speeding up the development process.
Here is a question which I find to be pretty interesting. Can anyone come-up with a query or a batch which, when executed, will print itself? I would like to see the lengthiest batch anyone can come up with. The solution can be in SQL Server 2000 or 2005. For example, here is a no-brainer solution that works for SQL Server 2005
SELECT (select text from sys.dm_exec_sql_text(sql_handle))
FROM sys.dm_exec_requests
WHERE session_id = @@spid
This should be quite simple. But, I would like to find the best approach to this problem.
The question is to get the binary values for positive integers.
Lets take this table called numbers that has one column called id holding positive integer values. Now, I need to write a query (or set of queries) to select from the table "numbers" and return me the id and the binary value of that id.
I had already blogged on how we can split a comma separated list using CTEs in my other post on Interesting queries using recursive Common Table Expressions , the idea of which was a slight modification to the one conceived and presented by Erland Sommarskog in Arrays and Lists in SQL Server.
After reading through the blog of Hugo Kornelis on using stuff for string manipulation, I realised that the STUFF function, indeed, made splitting a comma separated values string (CSV) pretty much easy.
I accept that this post looks a bit out of context for SQL, but its defenitely an interesting "garbage" to collect. For all having some aptitude in maths, you would wonder how the value of pi is derieved? Here is a small attempt in finding the value of pi from what we already know.
This scenario doesn't (and hopefully shouldn't) relate to any requirements, you will ever see, to be implemented at the database tier. But just for the fun of solving it, I am putting it here.
Please have a look at the image. Its a sine curve, that is generated from a query selecting 2 columns (both computed, may be from number table). One column gives the angle in degrees and the other column is the corresponding sine value, graphically represented.
Now this is pretty similar to the previous scenario (#2) , but a bit more interesting, I hope.
Here is the question.
I have a table maxnum with the following data:
create table maxnum (a int, b int, c int)
insert into maxnum values (1,2,3)
insert into maxnum values (4,3,5)
insert into maxnum values (23,44,23)
insert into maxnum values (23,45,45)
Now I need to get the result with 4 columns. a,b,c and the intermediate (neither max nor min) value of a,b,c. So, the result will be this way:
a b c intermed
1 2 3 2
4 3 5 4
23 44 23 23
23 45 45 45
How do I get the result?
Take it, that all three values (a,b,c) in the row cannot be the same.
Unlike the previous scenario, I am not tying this up with any SQL Server version. Here is the question.
I have a table maxnum with the following data:
create table maxnum (a int, b int, c int)
insert into maxnum values (1,2,3)
insert into maxnum values (4,3,5)
insert into maxnum values (23,44,23)
insert into maxnum values (23,45,45)
Now I need to get the result with 4 columns. a,b,c and the max value of a,b,c. So, the result will be this way:
For all those using SQL Server 2005, here is a scenario, see if you can come up with a solution:
I thought I will check out the different implementations possible and find the best out of it.
Say you have a table:
Customers (CustomerID int primary key, CustomerName varchar(50))
A pretty simple table structure. And it has 1000 rows.
Now, I am conducting a contest for the customers where I will randomly pick up 5 to 20 customers every week and give away prizes.
How will I go about doing it?
I need to create a stored procedure/query/function that will accept no parameters but will return random list of customers and random number of customers (between 5 and 20)
Posts
All Comments
