T-SQL: How to update random data in a table in sql server
- Details
- Written by Ranjeet
It’s always being asked in interview or required in day to day work, when need to update random data in a table. Microsoft SQL Server has given very good feature/keyword in SELECT query by which we can accomplish this task. The keyword we use is TABLESAMPLE SYSTEM(2 PERCENT). Let me show you with an example.
-->> Check the data in table by executing below query
SELECT * FROM EE
-->> Use below update query to update random data in table
UPDATE EE
SET dsc = 'RandomUpdate'
FROM EE
TABLESAMPLE SYSTEM(5 PERCENT)
T-SQL: UPPER Built-In string Function in sql server
- Details
- Written by Ranjeet
UPPER built-in string function is very common and known function. And also it accept only a parameter. Let me just point out its usage. I have not much to say about this function. It take the string input and return the string.
1. Use to fetch string column data converting to all in UPPER case
2. Use to compare the string column in where clause when column is case sensitive.
Please see below example queries:
Query 1:
SELECT UPPER('SQLSERVERBASE.COM is SQL blog site') as UPPERFucntionOutput
Output:
Query 2:
SELECT UPPER(name) as UPPERFucntionOutput FROM TESTP(NOLOCK)
Output:
T-SQL: LOWER Built-In string Function in sql server
- Details
- Written by Ranjeet
LOWER built-in string function is very common and known function. And also it accept only a parameter. Let me just point out its usage. I have not much to say about this function. It take the string input and return the string.
1. Use to fetch string column data converting to all in LOWER case
2. Use to compare the string column in where clause when column is case sensitive.
Please see below example queries:
Query 1:
SELECT LOWER('SQLSERVERBASE.COM is SQL blog site') as LowerFucntionOutput
Output:
Query 2:
SELECT LOWER(name) as LowerFucntionOutput FROM TESTP(NOLOCK)
Output:
T-SQL: RTRIM Built-In Function
- Details
- Written by Ranjeet
RTRIM is again a string function which use to trim the blank space from end side of the string/expression. This function is applied through all the available SQL server version through SQL Server 2008. It requires one parameter. It returns string only.
Please see below example queries:
Query 1:
SELECT 'SQLSERVERBASE.COM is SQL blog site ' as StringWithSpace
Output:
Query 2:
SELECT RTRIM('SQLSERVERBASE.COM is SQL blog site ') as StringWithSpace
Output:
You can notice that first query output is have a single blank space at the end of string. And in second query output is trimmed by RTRIM built-in sting function
T-SQL: LTRIM Built-In Function in sql server
- Details
- Written by Ranjeet
LTRIM is again a string function which use to trim the blank space from beginning of the string/expression. This function is applied through all the available SQL server version through SQL Server 2008. It requires one parameter. It returns string only.
Please see below example queries:
Query 1:
SELECT ' SQLSERVERBASE.COM is SQL blog site' as StringWithSpace
Output:
Query 2:
SELECT RTRIM('SQLSERVERBASE.COM is SQL blog site ') as StringWithSpace
Output:
You can notice that first query output is have a single blank space at the beginning of string. And in second query output is trimmed by LTRIM built-in sting function
T-SQL: REVERSE Built-In string Function
- Details
- Written by Ranjeet
REVERSE built-in string function is very common and useful function. It accept one string parameter. It simply reverse the string you pass in parameter.
Please see below example queries:
Query 1:
select REVERSE('SQLSERVER ') as ReverseFunctionOutput
Output:
What is REVERSE built-in string function, what is REVERSE function, How to reverse string in sql server, how to reverse a number string in sql server
T-SQL: What is syntax to write GOTO statement
- Details
- Written by Ranjeet
GOTO function is a programming construct in T-SQL, which use to jump code flow to some different level. Level can be down the code or up side of the code. It is a control flow statement. There are some specific use of GOTO in some scenario. Below is the basic syntax of GOTO function.
-->> Sample test of GOTO statement
Declare @id int
set @id=2
if @id=2
GOTO Level2 -- using GOTO if condition true.
else
GOTO Level1
Level2: -- defining Level2
T-SQL: what operator is used in sql server to get Quotient and Remainder
- Details
- Written by Ranjeet
Being a developer of different language, we sometime stuck remembering the operator of quotient and remainder. In T-SQL Quotient operator is forward slash “/” and remainder operator is percentage sign “%’. Let see this with an example below:
-->> Operator to get Quotient value in divide operations
Declare @Dividend int, @Divisor int, @Quotient int, @Remainder int
SET @Dividend =5
SET @Divisor=2
SELECT @Quotient=@Dividend/@Divisor
PRINT 'Value of Quotient= ' + convert(varchar,@Quotient)
SELECT @Remainder=@Dividend%@Divisor
PRINT 'Value of Remainder= ' + convert(varchar,@Remainder)
OUTPUT:
T-SQL: Query to get percentage backup completion of database
- Details
- Written by Ranjeet
Whenever database backup happens, we always need to monitor/get update that how much percentage backup has been completed and how much it left to go. Below query would help getting the details:
SELECT der.percent_complete as PercentageComplete, dest.text as QueryText, der.command as QueryType,
der.start_time as QueryStartTime,
convert(varchar, datediff(s, start_time, getdate())/(60*60)) + ': ' + convert(varchar, datediff(s, start_time, getdate())%(60*60)/60) + ': ' + convert(varchar, datediff(s, start_time, getdate())%60) as TotalQueryRunningTime,
dateadd(s,estimated_completion_time/1000, getdate()) as EstematedCompletionTime
FROM sys.dm_exec_requests der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest
where der.command in('BACKUP DATABASE', 'BACKUP LOG')
T-SQL: Query to get percentage restore status of database
- Details
- Written by Ranjeet
Whenever database restore goes, we always need to monitor/get update that how much percentage restore completed and how much it left to go. Below query would help getting the details:
SELECT der.percent_complete as PercentageComplete, dest.text as QueryText, der.command as QueryType,
der.start_time as QueryStartTime,
convert(varchar, datediff(s, start_time, getdate())/(60*60)) + ': ' + convert(varchar, datediff(s, start_time, getdate())%(60*60)/60) + ': ' + convert(varchar, datediff(s, start_time, getdate())%60) as TotalQueryRunningTime,
dateadd(s,estimated_completion_time/1000, getdate()) as EstematedCompletionTime
FROM sys.dm_exec_requests der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest
where der.command in('RESTORE DATABASE', 'RESTORE LOG')