TODO
What is the guest account.
Apparently, the SQL-SMO API allows programmers to administer SQL Server in their applications.
Show definition of a procedure: exec sp_helptext 'proc_name'.
What is the sysdiagrams table? Why does it have to be excluded manually when selecting from information_schema.tables?
How do I loop over a result set in a query (for r in (select … ) loop)?
Setting columns to not null:
alter table TAB alter column COL integer not null;
How do I extend the length of the returned value in
object_definition()? The returned string seems truncated at times.
The Microsoft Container Registry at mcr.microsoft.com is the official container registry for the distribution of Microsoft product containers.
How do I comment a table or a column? Apparently with sp_addextendedproperty:
execute sp_addextendedproperty 'MS_Description',
'This is my table comment',
'user', @CurrentUser, 'table', 'TABLE_1'
go
And how will I then get the comment from the
sys tables?
if object_id('dbo.my_procedure', 'P') is not null
drop procedure dbo.my_procedure
go
Apparently, in
sqlcmd, commands starting with a colon (such as
:listvar, are executed immediately, rather than stored in the execution buffer (which is executed with the next
GO):
select
:listvar
@@servername
go
c:\> sqlcmd
:setvar name Rene
:setvar bla moreBla
exec dbo.my_procedure $(name), $(bla)
go
convert(data-type, expression [, format])
Automatic filling of primary key:
create table tq84 (
id bigint IDENTITY(1, 1),
…
);
With ansi_nulls off -> null = null is true.
From Oracle to SQL Server
Create database
create database tq84_db;
create database another_db;
go
use tq84_db
go
use another_db
go
Determine current database name:
select db_name()
Collation
A database can be created in a specific
collation:
create database tq84_db
collate SQL_Latin1_General_CP1_CI_AS;
select databasepropertyex('tq84_db', 'collation');
VB Script
set oServer = createObject("SQLDmo.SqlServer")
oServer.loginSecure = true
oServer.connect "(local)"
…
oServer.disconnect
set oServer = nothing
Apparently obsoleted in next release.
Declaring variables
declare @var_01 bigint
declare @var_02 int
declare @var_03 int = 42
declare @var_04 decimal (18, 6);
declare @var_05 nvarchar ( max ) = 'abc';
declare @var_true bit = 1 ;
declare cur_ab cursor for select foo, bar from tab;
declare @tab table (col_nm int, col_dt datetime); insert into @tab values (…); select * from @tab;
Assigning values to variables
An assignment requires set (or select):
declare @foo int
declare @bar varchar(10)
set @foo = 42
select @bar = 'baz'
Scope of variables
go apparently deletes or removes previously declared variables.
Concatenation of strings
@string_one + 'string two'
If one of the concatenated elements is null, then the entire resulting string will be null, too. (concat might help here)
Creating an index if it does not exist
if not exists (
select *
from sys.indexes
where
name=’IX_TQ84_TAB’ AND
object_id = OBJECT_ID('[dbo].[TQ84_TAB]')
)
begin
create nonclustered index IX_TQ84_TAB
on dbo.TQ84_TAB(COL_1, COL_2)
end
date datatype
set dateformat dmy
Use exec sp_helplanguage to determine available date formats.
cast as date:
select * from tab
where
cast(col_dt as date) = '2017-08-28'
Apparently, where col_dt = '20170828' works also.
select
convert(nvarchar(10), getdate(), 112) as iso_date,
convert(nvarchar(10), getdate(), 108) as hour_minute_second,
format (@someDate, 'd', 'en-us' ) date_en_us,
format (@someDate, 'd', 'de-de' ) date_de_de,
format (@someDate, 'yyyy-MM-dd hh:mm:ss') yyyy_MM_dd__hh_mm_ss
…
T-SQL blocks
begin
declare
@someVar = 42
…
end
if
if @a = @b
stmt
if @a = @b
begin
stmtOne
stmtTwo
stmtThree
end
begin try
…
end try
begin catch
select
error_number (),
error_severity (),
error_state (),
error_procedure(),
error_line (),
error_message ()
…
end catch
Service accounts
SQL Server Express 2012 seemed to install or use the following accounts
| Service | Account |
| SQL Server Database Engine | NT Service\MSSQL$SQLEXPRESS |
| SQL Server Browser | NT AUTHORITY\LOCAL SERVICE |
Data Directories
Data directories apparently can be specified for
- System database
- User databases
- User database logs
- Temp databases
- Temp database logs
- backups
SMO - SQL Server Management Objects
SQL Server Management Objects allow to manage an SQL Server programatically. Thus, these object complement the functionality of
Management Studio.
Schemas
Schemas can be owned by any database principal.
A principal can own multiple schemas
By default, objects in a schema are owned by the security principal that owns the schema. However, ownership can be transferred with alter authorization ….
There are ten predefined schemas. They have the same name as the built-in database users.
Of these ten predefined schemas, the following four cannot be deleted:
Users, roles, princicpals etc.
Roles are either
- fixed server roles
- fixed database roles
- user-defined database roles
The fixed server roles are
- sysadmin
- serveradmin
- securityadmin
- processadmin
- setupadmin
- bulkadmin
- diskadmin
- dbcreator
- public
Everyone belongs to the public fixed server role and receives any permission assigned there.
Security
Extensible Key Management (EKM) module holds symmetric or asymmetric keys outside of SQL Server.
Transparent Data Encryption (TDE) encrypts an entire
database with a symmetric key, the so-called
database encryption key (DEK). TDE secures against the threat of someone having direct access to the media on which the senstitive data is stroed.
The database encryption key itself is protected by
- a certificate protected by the database master key of the master database, or
- by an asymmetric key stored in an EKM.
Database master keys are protected by the
Service Master Key. The
Service Master Key is created when SQL Server starts up and is encrypted with the
Windows Data Protection API (DPAPI).
Components
SQL Server compenents are
Features that support High Availability
Features that support HA include
- Log shipping
- Database mirroring
- Backup compression
- Database snapshot
- Always On failover cluster instances
- Always On availability groups
- Basic availability groups
- Automatic read write connection re-routing
- Online page and file restore
- Online index create and rebuild
- Resumable online index rebuilds
- Online schema change
- Fast recovery
- Accelerated database recovery
- Mirrored backups
- Hot add memory and CPU
- Database recovery advisor
- Encrypted backup
- Hybrid backup to Windows Azure (backup to URL)
- Cluster-less availability group
- Failover servers for disaster recovery
- Failover servers for high availability
- Failover servers for disaster recovery in Azure
Scalability and performance feeatures
Scalability and
performance features include
- Columnstore
- Large object binaries in clustered columnstore indexes
- Online non-clustered columnstore index rebuild
- In-Memory Database: In-Memory OLTP
- In-Memory Database: hybrid buffer pool
- In-Memory Database: memory-optimized tempdb metadata
- In-Memory Database: persistent memory support
- Stretch database
- Multi-instance support
- Table and index partitioning
- Data compression
- Resource governor
- Partitioned table parallelism
- Multiple filestream containers
- NUMA aware and large page memory and buffer array allocation
- Buffer pool extension
- IO resource governance
- Read-ahead
- Advanced scanning
- Delayed durability
- Intelligent Database: automatic tuning
- Bulk insert improvements
Security features
Security features include
- Row-level security
- Always Encrypted
- Always Encrypted with Secure Enclaves
- Dynamic data masking
- Server audit
- Database audit
- Transparent database encryption
- Extensible key management
- User-defined roles
- Contained databases
- Encryption for backups
- Data classification and auditing
Replication features
Replication features include
- Heterogeneous subscribers
- Merge replication
- Oracle publishing
- Peer to peer transactional replication
- Snapshot replication
- SQL Server change tracking
- Transactional replication
- Transactional replication to Azure
- Transactional replication updateable subscription
Management tools
Management tools include
- SQL Management Objects (SMO)
- SQL Assessment API
- SQL Vulnerability Assessment
- SQL Configuration Manager
- SQL CMD (Command Prompt tool)
- Distributed Replay - Admin Tool
- Distribute Replay - Client
- Distributed Replay - Controller
- SQL Profiler
- SQL Server Agent
- Microsoft System Center Operations Manager Management Pack
- Database Tuning Advisor (DTA)
RDBMS manageability
RDBMS manageability features include
- User instances
- LocalDB
- Dedicated admin connection
- SysPrep support 1
- PowerShell scripting support2
- Support for data-tier application component operations - extract, deploy, upgrade, delete
- Policy automation (check on schedule and change)
- Performance data collector
- Able to enroll as a managed instance in multi-instance management
- Standard performance reports
- Plan guides and plan freezing for plan guides
- Direct query of indexed views (using NOEXPAND hint)
- Direct query SQL Server Analysis Services
- Automatic indexed views maintenance
- Distributed partitioned views
- Parallel indexed operations
- Automatic use of indexed view by query optimizer
- Parallel consistency check
- SQL Server Utility Control Point
- Buffer pool extension
- Master instance for big data cluster
- Compatibility certification
Development tools
Development tools include
