Category: SQL-General

Download free eBook – Introducing Microsoft SQL Server 2012

SQL, SQL-General, Varinder Sandhu

Microsoft has released one second draft of this book. PART I   DATABASE ADMINISTRATION   (Ross’s part) Denali Editions and Enhancements High Availability and Disaster Recovery Enhancements Scalability and Performance Security Enhancements Beyond Relational PART II   BUSINESS INTELLIGENCE DEVELOPMENT   (Stacia’s part) Integration Services Data Quality Services Master Data Services Analysis Services and PowerPivot Reporting Services This release is available in PDF only. Click here for download

» 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 – 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
,

SQL Server – User Defined Group

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

I had requirement to make the User Defined Group in Reports (i.e. Crystal Reports) Example: CREATE TABLE test_custom_group ( id INT, dept VARCHAR(20), detail VARCHAR(30), salary NUMERIC(5,2) ) Created a Function CREATE FUNCTION test_fn ( ) RETURNS @temp_table TABLE ( sno int, dept varchar(20), grp varchar(10) ) AS BEGIN INSERT @temp_table(sno,dept,grp ) SELECT 1,'C','A' UNION all SELECT 2,'A','A' UNION all SELECT 3,'D','B' UNION all SELECT 4,'E','B' UNION all SELECT 5,'B','B' RETURN END Created a Store Procedure USE test_db IF EXISTS

» Read more
, ,

SQL Server – User Defined Sort Order

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

I had requirement to get the values with Use Defined Sort order. Here is simple way to get User Defined Sort order Example: Created a table as : CREATE TABLE sort_order ( id INT, dept VARCHAR(20), detail VARCHAR(30) ) Inserted few rows in table as Requirement: Now we want to change the sort order and new sorted output should be: REQUIRED OUTPUT: For this created a function: CREATE FUNCTION test_fn ( ) RETURNS @temp_table TABLE ( sno int, dept varchar(20)

» Read more
, , ,

SQL SERVER – The Self Join

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

A self-join is joining a table to itself. This is done by using table name aliases to give each instance of the table a separate name. Self Join is very useful when you want to compare values in a column to other values in the same column. Self join and its example is very common question in the interview. Let us take a look to example

» Read more
, ,

SQL Server Denali – New Logical Functions

SQL, SQL-Advanced, SQL-General, SQL-Server 2011, SQL-Tips and Tricks, Varinder Sandhu

There are two new Logical functions are introduced in Denali. Let‘s have a look IFF CHOOSE IFF It Returns one of two values, depending on whether the Boolean expression evaluates to true or false. IIF is a shorthand way for writing a CASE statement. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true_value is returned if the Boolean expression

» Read more
, , ,

SQL Server – Ranking Functions – RANK, DENSE_RANK, ROW_NUMBER, NTILE

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

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic. There are four functions RANK DENSE_RANK ROW_NUMBER NTILE RANK   Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question. DENSE_RANK Returns the rank of

» Read more
, , , , ,

SQL Server 2008 – Object Explorer Details and SQL Server Object Search

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

My one friend calls me and asks where I find the Object detail in SQL Server 2008. This is very simple but may be some people are in the same situation and finding the Object Explorer Details in SQL Server 2008. That‘s why here sharing Object Explorer Details and SQL Server Object Search. SQL Server Object Search is useful for developer to find the some specific object in the database or across SQL Instance. Object Explorer Details, a component of

» Read more
, , ,

SQL Server – PARSE() and TRY_PARSE() conversion functions

SQL, SQL-Advanced, SQL-General, SQL-Server 2011, SQL-Tips and Tricks, Varinder Sandhu

PARSE() and TRY_PARSE() are new conversion function introduced in SQL Server Denali. TRY_PARSE() tries to translate the result of an expression to specified data type if the translation is possible, otherwise, it returns NULL. PARSE() tries to translate the result of an expression to specified data type if the translation is possible, otherwise, it returns error. Also, we can also specify the CULTURE, during this conversion.  CULTURE, means a language which will used by SQL Server to interpret data. If

» Read more
, , ,

SQL Server code name ‘Denali’ – Community Technology Preview 3 (CTP 3)

SQL, SQL-Advanced, SQL-General, SQL-Server 2011, Varinder Sandhu

Microsoft SQL Server code name ‘Denali’ enables a cloud-ready information platform that will help organizations unlock breakthrough insights across the organization as well as quickly build solutions and extend data across on-premises and public cloud backed by capabilities for mission critical confidence. SQL Server code name ‘Denali’ enables a cloud-ready information platform that will help organizations unlock breakthrough insights across the organization as well as quickly build solutions and extend data across on-premises and public cloud backed by capabilities for

» Read more
, , ,
1 2 3 4 … 6