Question of the month May 2018 – Find out erroneous query

In SQL Server, only one out of the following four SELECT queries will throw an error. What is it?

SELECT 5number
SELECT 5.number
SELECT 5[number]
SELECT 5..number
Advertisements

SQL Server – How to Uniquely identify a row?

Without seeing the table structure,If you are interested in knowing which columns uniquely identify a row in a table, you can use this system procedure

Consider the following table

create table #testing(id int primary key, emp_name varchar(100))

Note that the column named id is unique by default. Now execute the following system procedure sp_special_columns

EXEC tempdb..sp_special_columns '#testing'

The result is as shown below

unique_column

Scripting out Stored Procedures and Functions using Query

One of my friends asked me if there is an way to script out the definitions of stored Procedures and Functions using Query. There can be several methods. This is one of the methods that uses BCP utility

All you have to do is replace DBname by the actual Database Name

EXEC master..xp_cmdshell 
'bcp "Select routine_definition from 
DBname.information_Schema.routines 
order by routine_name" queryout "C:\scripts.sql" -c'

After it runs successfully, the file C:\scripts.sql will have the scripts

Note that due to security issues, xp_cmdshell is disable by default. You need to use sp_configure to enable it. You can find more information in this https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/xp-cmdshell-server-configuration-option

Happy holidays from T-SQL

Curious to know the result of the following query? All you need to do is, in SQL Server, goto SSMS, set the result mode to Text (Press CTRL+T) and then execute the following code

set nocount on select space(17-len(replicate(
char(135),no)))+ replicate(char(135),no*2-1)
from (select top 10 row_number() over (
order by (select 1)) as no from 
(select 0 as no union all select 0 union 
all select 0) as t1 
cross join (select 0 as no union 
all select 0 union all 
select 0) as t2) as t union all 
select space(14)+replicate(char(124),5) union 
all select space(10)+
cast(0x486170707920486F6C6964617973 
as varchar(100))

MySQL – Functions to get current date and time

You may often need to do some data filters based on the current date and time. In MySQL you can use the system functions available to get current date and time.

Refer the following examples.

1 Get current date

SELECT CURRENT_DATE() AS today_date

or

SELECT CURDATE() AS today_date

The result is

2017-11-20

2 Get current time

SELECT CURRENT_TIME() AS today_time

or

SELECT CURTIME() AS today_time

The result is

08:36:29

3 Get Current Date and time

SELECT NOW() AS today_date_time

The result is

2017-11-20T08:36:29Z

MySQL – How to create random number with fixed length?

One of my friends asked me “How do I create a 10 digit random number?”

MySQL supports RAND() system function which can produce a random number between 0 and 1 with 16 digits as the decimal part.

An example for a random value can be obtained using

SELECT RAND() as random_number;

The result is

random_number
-------------------
0.7638836063108254

Note that every time you run this code, you will get a different number.

Now to get 10 digit whole number, multiple it by 10000000000 and omit the decimal part by converting the result into UNSIGNED number

SELECT CAST(rand()*10000000000 as UNSIGNED) as random_number

The result is

random_number
-------------------
5176665713

This can be modified easily to get other number with fixed digits. For example if you want a random 4 digit number, you can use

SELECT CAST(rand()*10000 as UNSIGNED) as random_number

The result is

random_number
-------------------
5278

You can parameterize this to work for any number of fixed digits you want