1.
Microsoft recommends that when you refer to objects in your code you always use the twopart
object names.
The examples here use a schema named dbo that is created automatically in every database and is
also used as the default schema for users who are not explicitly associated with a different schema.
2. Note that a query that uses OFFSET-FETCH must have an ORDER BY clause.
SELECT TOP (5) WITH TIES ...
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate, orderid
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
3.
SELECT orderid, custid, val,
ROW_NUMBER() OVER(PARTITION BY custid
ORDER BY val) AS rownum
FROM Sales.OrderValues
ORDER BY custid, val;
4.
Notice the use of the letter N to prefix the string ‘D%’; it stands for National and is used to denote
that a character string is of a Unicode data type (NCHAR or NVARCHAR), as opposed to a
regular
character data type (CHAR or VARCHAR).
5.
SQL Server's identity is the counter part for Oracle's sequence
keycol INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_T1 PRIMARY KEY,
SET @new_key = SCOPE_IDENTITY(); --to get a newly generated identity value
The sequence object is a feature that was added in SQL Server 2012 as an alternative key-generating
mechanism for identity.
6.
SQL Server 2008 and SQL Server 2012 support a statement called MERGE that allows you to modify
data,
7.
BEGIN TRAN;
INSERT INTO dbo.T1(keycol, col1, col2) VALUES(4, 101, 'C');
INSERT INTO dbo.T2(keycol, col1, col2) VALUES(4, 201, 'X');
COMMIT TRAN;
8.
The GO command is not really a T-SQL command; it’s actually a command used by SQL Server’s client
tools, such as SSMS, to denote the end of a batch.
run the following code to suppress the default output produced by DML statements that indicates how
many rows were affected.
SET NOCOUNT ON;
9.
The sp_executesql stored procedure was introduced after the EXEC command. It is more secure and
more flexible in the sense that it has an interface; that is, it supports input and output parameters.
Note
that unlike EXEC, sp_executesql supports only Unicode character strings as the input batch of code.
Each instance of SQL Server can contain multiple databases like System databases master, model, tempdb, msdb and Resource.
model: The model database is used as a template for new databases. Every new database
that you create is initially created as a copy of model
tempdb: this database is destroyed and recreated as a copy of the model database every time
you restart the instance of SQL Server.
You can define a property called collation at the database level that will determine language
support, case sensitivity, and sort order for character data in that database.
In terms of security, to be able to connect to a SQL Server instance, the database administrator
(DBA) must create a logon for you. The DBA needs to map your logon to a database user in each database that you are supposed to
have access to.
Although SQL Server can write to multiple data files in parallel, it can write to only one log file at a
time, in a sequential manner. Therefore, unlike with data files, having multiple log files does not result
in a performance benefit.
a database contains schemas, and schemas contain objects. You can think of a schema as a
container of objects such as tables, views, stored procedures, and others.
Microsoft recommends that when you refer to objects in your code you always use the twopart
object names.
select user_name()
select @@VERSION
