Category: SQL-Tips and Tricks

SQL SERVER – 2008 – Inline Variable Assignment – Declare and Assign Variable

SQL, SQL-General, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Inline variable assignment is available in SQL Server 2008 or higher version. Beside this is very simple but very useful feature. Many of the programming languages have this feature. But now SQL Server 2008 or higher version has this feature. Example: —- SQL Server 2005 or earlier versions DECLARE @var1 INT SET @var1 = 100 SELECT @var1 AS myVar GO —- SQL Server 2008 or Higher Version (Inline Variable Assignment) DECLARE @var1 INT = 100 SELECT @var1 AS myVar GO

» Read more
, , ,

SQL Server 2008 – Database Backup Compression

SQL, SQL-Advanced, SQL-Backup & Restore, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Backup compression was introduced in SQL Server 2008 Enterprise. At installation, backup compression default is set to 0, which makes backup compression off by default. To change the default to COMPRESSION, set backup compression default to 1. To revert the default to NO_COMPRESSION, set backup compression default back to 0. Backup Compress at Server Level (Thru Script) USE master; GO EXEC sp_configure 'show advanced option', '1'; RECONFIGURE GO EXEC sp_configure 'backup compression default', '1'; RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure

» Read more
, ,

SQL Server 2008 – Get the List of Stored Procedures update/modify in last N number days

SQL, SQL-Advanced, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

With the help of the given procedure one can get the list of Stored Procedures update or modify in last N number days. Script: — Get the List of Stored Procedures update/modify in last N number days CREATE PROCEDURE GetListModifySP ( @days INT ) AS SELECT name AS SP_Name, modify_date as Modfiy_Date FROM sys.objects WHERE TYPE = 'P' AND DATEDIFF(DD,modify_date, GETDATE()) < @days –Excecute the GetListModifySP –You can give the N of days as per requirement EXEC GetListModifySP 10 Hope

» Read more
,

SQL Server – Last Execution Date/Time of a Stored Procedure

SQL, SQL-Advanced, SQL-Performance, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

If you want to get the  Last Execution Date/Time of a Stored Procedure then here is solution. Sys.dm_exec_procedure_stats it’s a system dynamic view that returns aggregate performance statistics for cached stored procedures. This view has been introduced from SQL Server 2008. The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is

» Read more
,

SQL Server 2012 – EOMONTH – Find Last Day of Any Month

SQL, SQL-Server 2012, SQL-Tips and Tricks, Varinder Sandhu

In SQL Server 2012, seven new datetime functions have been introduced. EOMONTH is one of them.  This is really useful function because many times we need to find the last date of month. Syntax: EOMONTH ( start_date [, month_to_add ] ) Find Last Day of Current Month Example: SELECT EOMONTH (GETDATE()) Last_Date_Month Result: Date ————– 2012-04-30 Find Last Day of Next Month Example: SELECT EOMONTH (GETDATE(),1) Last_Date_Next_Month Result: Date ————– 2012-05-31 Find Last Day of Previous Month Example: SELECT EOMONTH

» Read more
,

SQL Server 2012 – DATEFROMPARTS

SQL, SQL-General, SQL-Tips and Tricks, Varinder Sandhu

In SQL Server 2012, seven new datetime functions have been introduced. DATEFROMPARTS is one of them. Syntax: DATEFROMPARTS (year, month, day) DATEFROMPARTS returns a date value with the date portion set to the specified year, month and day, and the time portion set to the default. If the arguments are not valid, then an error is raised. If required arguments are null, then null is returned. Example: SELECT DATEFROMPARTS (2012, 04, 11) AS Date; Result: Date ————– 2012-04-11

» Read more
,

SQL Server 2012 – New DateTime Functions

SQL, SQL-General, SQL-Server 2012, SQL-Tips and Tricks, Varinder Sandhu

There are seven new datetime functions are introduced in SQL Server 2012 as motioned below DATEFROMPARTS ( year, month, day) DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds ) DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision ) DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision ) SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute ) TIMEFROMPARTS ( hour, minute, seconds, fractions, precision ) EOMONTH ()

» Read more
,

SQL Server 2008R2 – Database Restore – failed 38 (reached the end of the file)

SQL, SQL-Backup & Restore, SQL-Tips and Tricks, Varinder Sandhu

While restoring the SQL database error occurred… Through UI following error occurred Through Script following error occurred Script: RESTORE DATABASE mydb FROM DISK = 'D:\ DB Backup\mydb.bak' WITH MOVE 'mydb_Data' TO 'E:\Database\mydb.mdf', MOVE 'mydb_Log' TO 'E:\Database\mydb.ldf', REPLACE GO Msg 3203, Level 16, State 1, Line 1 Read on “D:\ DB Backup\mydb.bak” failed: 38(Reached the end of the file.) Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. I have taken the other backup file then it

» Read more
,

SQL Server 2008 R2- Find Database Collation

SQL, SQL-Advanced, SQL-Tips and Tricks, Varinder Sandhu

How to find collation of current database? There are two different ways to find out SQL Server database collation. Using T-SQL SELECT DATABASEPROPERTYEX('Database_Name', 'Collation') as SQLCollation; Using SQL Server Management Studio (SSMS) Right Click on the database Go to Properties You will get the dialog box as shown in the figure   You can execute the system function fn_helpcollations to retrieve a list of all the valid collation names for Windows collations and SQL Server collations: SELECT * FROM fn_helpcollations()

» Read more
,

SQL Server – Error 3154: The backup set holds a backup of a database other than the existing database

SQL, SQL-Backup & Restore, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Sometime we faced the following error message while restoring the database Error 3154: The backup set holds a backup of a database other than the existing database. Solution: Use WITH REPLACE while using the restore script as Script 1 RESTORE DATABASE YourDB FROM DISK = 'C:\YourDB.bak' WITH REPLACE  Script 2 — Get the Logical file name of the database from backup. RESTORE FILELISTONLY FROM DISK = 'C:\YourDB.bak' GO RESTORE DATABASE YourDB FROM DISK = 'C\:YourDB.bak' WITH MOVE 'YourDB_Data' TO 'C:\Database\YourDB.mdf',

» Read more
, ,

T-SQL vs. PL-SQL

Oracle, Oracle-Tips and Tricks, SQL, SQL-Tips and Tricks, Varinder Sandhu

T-SQL TSQL is a proprietary procedural language used by Microsoft in SQL Server. PL-SQL PL/SQL is a proprietary procedural language used by Oracle Microsoft’s T-SQL is similar to Oracle’s PL-SQL to some extent, sharing with some basic difference as below Difference in data type, There are many different data type. Sharing few as below … T-SQL PL-SQL Integer, SmallInt etc Number Varchar Varchar2 DATETIME, SMALL-DATETIME Date   The default date format of PL-SQL does not include time part. In T-SQL,

» Read more
,

SQL Server – SQL Utility Command GO

SQL, SQL-General, SQL-Tips and Tricks, SQL-Utilities, Varinder Sandhu

GO is not a Transact-SQL statement, even it used in the Transact-SQL. It is a SQL Utility command.  SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.  A Transact-SQL statement cannot occupy the

» Read more
,
1 2 3 4 5 … 8