Techaray

Shailendra Saxena's Technical Blog

Restrictions on Views

clock May 8, 2014 10:14 by author ShailendraSaxena

 

Views have a number of restrictions, such as the following:

  •  You cannot add an ORDER BY to the SELECT statement in a view. A view must appear

just like a table, and tables in a relational database contain sets of rows. Sets by themselves

are not ordered, although you can apply an order to a result set using ORDER

BY. Similarly, tables and views in SQL Server do not have a logical order to their rows,

though you can apply one by adding an ORDER BY to the outermost SELECT statement

when you access the view.

  • You cannot pass parameters to views.
  • Similarly, a view cannot reference a variable inside the SELECT statement. See the

section “Inline Functions” for information on how to use functions to simulate passing

parameters to a view.

  •  A view cannot create a table, whether permanent or temporary. In other words, you

cannot use the SELECT/INTO syntax in a view.

  •  A view can reference only permanent tables; a view cannot reference a temporary

table.



Derived Table V/S Sub Query V/S CTE

clock May 7, 2014 08:41 by author ShailendraSaxena

 

Derived Table

Sub Query

CTE

derived tables are used in the FROM clause

 

sub queries are used in the WHERE clause, but can also

be used to select from one table and insert into

another as we showed above

 

A common table expression (CTE) is a similar concept to a derived table in the sense that it’s

a named table expression that is visible only to the statement that defines it. Like a query

against a derived table, a query against a CTE involves three main parts:

■ The inner query

■ The name you assign to the query and its columns

■ The outer query

select employee_name

from employee

where employee_salary >

 (select avg(employee_salary)

        from employee)

 

select max(age)

from (

select age from table

) as Age  

 

 

With C As

(

SELECT empid, firstname, lastname, country, region, city

FROM HR.Employees

WHERE country = 'USA'

)

Select empid, firstname, lastname, country from c

 

 

 

 

 

 



SQL SERVER String Functions

clock May 6, 2014 09:50 by author ShailendraSaxena

 

SUBSTRING Function

The SQL Server (Transact-SQL) SUBSTRING functions allows you to extract a substring from a string.

EX. SELECT SUBSTRING('Techaray.com', 1, 4);

Result: 'Tech'

LEFT

LEFT function starts BEFORE the left-most character of a string and moves to the right.

SELECT LEFT('Techaray.com', 3)

Result :’Tec’

RIGHT

RIGHT function starts AFTER the right-most character and moves inwards to the left.

SELECT RIGHT('Techaray.com', 3)

Result: ‘com’

CHARINDEX

Searches an expression for another expression and returns its starting position if found.

SELECT CHARINDEX('aray', 'Techaray.com');

Result :5

LEN

The LEN function returns the length of an input string in terms of the number of characters.

SELECT LEN('XYZ')

Result:3

DATALENGTH

The DATALENGTH function returns the length of the input in terms of number of bytes.

Result:6

REPLACE

REPLACE function, you can replace in an input string provided as the first argument

all occurrences of the string provided as the second argument, with the string provided

as the third argument. For example, the expression REPLACE('.1.2.3.', '.', '/') substitutes all occurrences

of a dot (.) with a slash (/), returning the string '/1/2/3/'.

 

REPLICATE

The REPLICATE function allows you to replicate an input string a requested number of

times. For example, the expression REPLICATE('0', 10) replicates the string '0' ten times, returning

'0000000000'.

STUFF

The STUFF function operates on an input string provided as the first argument; then, from

the character position indicated as the second argument, deletes the number of characters

indicated by the third argument. Then it inserts in that position the string specified as the

fourth argument. For example, the expression STUFF(',x,y,z', 1, 1, '') removes the first character

from the input string, returning 'x,y,z'.



Functions That Modify Date and Time Values

clock May 6, 2014 08:52 by author ShailendraSaxena

 

Function

Syntax

Return value

Return data type

Determinism

DATEADD

DATEADD (datepart , number , date )

Returns a new datetime value by adding an interval to the specified datepart of the specified date.

The data type of the date argument

Deterministic

EOMONTH

EOMONTH ( start_date [, month_to_add ] )

Returns the last day of the month that contains the specified date, with an optional offset.

Return type is the type of start_date or datetime2(7).

Deterministic

SWITCHOFFSET

SWITCHOFFSET (DATETIMEOFFSET , time_zone)

SWITCHOFFSET changes the time zone offset of a DATETIMEOFFSET value and preserves the UTC value.

datetimeoffset with the fractional precision of the DATETIMEOFFSET

Deterministic

TODATETIMEOFFSET

TODATETIMEOFFSET (expression , time_zone)

TODATETIMEOFFSET transforms a datetime2 value into a datetimeoffset value. The datetime2 value is interpreted in local time for the specified time_zone.

datetimeoffset with the fractional precision of the datetime argument

Deterministic



Functions That Get Date and Time Parts

clock May 6, 2014 08:45 by author ShailendraSaxena

 

Function

Syntax

Return value

Return data type

Determinism

DATENAME

DATENAME ( datepart , date )

Returns a character string that represents the specified datepart of the specified date.

nvarchar

Nondeterministic

DATEPART

DATEPART ( datepart , date )

Returns an integer that represents the specified datepart of the specified date.

int

Nondeterministic

DAY

DAY ( date )

Returns an integer that represents the day day part of the specified date.

int

Deterministic

MONTH

MONTH ( date )

Returns an integer that represents the month part of a specified date.

int

Deterministic

YEAR

YEAR ( date )

Returns an integer that represents the year part of a specified date.

int

Deterministic



DateTime,DateTime2 and DatetimeOffSet

clock May 6, 2014 08:34 by author ShailendraSaxena

Below are the differences.

 

 

Use the time, date, datetime2 and datetimeoffset data types for designing new database. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. Datetimeoffset provides time zone support for globally deployed applications.

DATETIME2 has a date range of "0001 / 01 / 01" through "9999 / 12 / 31" while the DATETIME type only supports year 1753-9999.

Also, if you need to, DATETIME2 can be more precise in terms of time; DATETIME is limited to 3 1/3 milliseconds, while DATETIME2 can be accurate down to 100ns.



What is the logical query processing order of the various query clauses?

clock May 6, 2014 00:41 by author ShailendraSaxena

Logical query processing starts with the FROM clause, and then moves on
to WHERE, GROUP BY, HAVING, SELECT, and ORDER BY.

1. Evaluate the FROM Clause
2. Filter Rows Based on the WHERE Clause
3. Group Rows Based on the GROUP BY Clause
4. Filter Rows Based on the HAVING Clause
5. Process the SELECT Clause
6. Handle Presentation Ordering



Using Common Table Expressions

clock February 7, 2013 02:32 by author ShailendraSaxena

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

·  Create a recursive query.

·  Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

·  Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

·  Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

Structure of a CTE

 

A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE. After a CTE is defined, it can be referenced like a table or view can in a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

The statement to run the CTE is:

SELECT <column_list>

FROM expression_name;

Example

The following example shows the components of the CTE structure: expression name, column list, and query. The CTE expression Sales_CTE has three columns (SalesPersonID, SalesOrderID, and OrderDate) and is defined as the total number of sales orders per year for each salesperson.

Transact-SQL

USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO


How to Restore SQL Server 2005 Suspect Database

clock July 31, 2012 12:02 by author ShailendraSaxena

How to Restore SQL Server 2005 Suspect Database

 
EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER


Search

Calendar

<<  June 2017  >>
SuMoTuWeThFrSa
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678

Archive

Tags

Categories


Visitors

FlagCounter

Flag Counter

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2017

Sign In