Book: SQL Server 2012 T-SQL Fundamentals
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.
DECLARE @empname AS NVARCHAR(31);--or DECLARE @empname AS NVARCHAR(31); SET @empname = 'Peter';
SET @empname='Peter';
select @empname as empname;
select user_name()
select @@VERSION
select * FROM sys.tables;
select * FROM sys.columns
select * FROM INFORMATION_SCHEMA.TABLES
select * FROM INFORMATION_SCHEMA.COLUMNS
select *
from information_schema.routines
where routine_type = 'PROCEDURE'
The sp_tables stored procedure returns a list of objects (such as tables and views) that can be queried in the current database:
EXEC sys.sp_tables;
The sp_help procedure accepts an object name as input and returns multiple result sets with
general information about the object, and also information about columns, indexes, constraints, and more:
EXEC sys.sp_help
@objname = 'Queue.WorkItemSessions';
EXEC sys.sp_columns
@table_name = N'Orders',
@table_owner = N'Sales';
EXEC sys.sp_helpconstraint
@objname = N'Sales.Orders';
SELECT TOP (5) WITH TIES orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;
From SQL server 2012 (For Oracle 12c):
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate, orderid
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
