Please enable JavaScript.
Coggle requires JavaScript to display documents.
*MS-SQL RDBMS, SQL Server, SSRS, parsename() Rank() Dense_Rank() Row…
*
MS-SQL RDBMS
LANGUAGES
DCL
Grant , revoke
DQL
Select
DML
insert , update , delete,Merge , Locktable ,
TCL
DDL
create , alter ,Drop , Rename , Truncate , comment
Database
Create Database
use SQL Server Management Studio (SSMS)
Restore Database Testdb from disk = 'D:\Backup\Testdb_full_backup.bak
Create database Testdb
Backup Database
use SSMS
Differential/Incremental Backup
Backup database Testdb to disk = 'D:\Testdb_diff.bak' with differential
Full backup
Backup database Testdb to disk = 'D:\Testdb_full_bak'
Transactional Log/Log backup
Backup log Testdb to disk = 'D:\Testdb_log.trn'
System Databases
Master - System level info
Distribution
Tempdb - Hold temp obj, temp tables variables
MSDB - Used by sql server agent for jobs & alerts
Resource - Read only db contains sys obj physically , but logically appear in all DB's
Model - New DB Demo
Select Database
Exec use msdb
select msdb database
Drop Database
Drop database Testdb
Restore Database
use SSMS
Restore database Testdb from disk = 'D:\Testdb_Full.bak' with replace
User DB
Primary Data file .mdf (Master data file)
Log file .ldf (log data file )
.bacpac -> schema + data
.dacpac -> Schema
.dacpac
Extract, Deploy, Upgrade, register, unregister.
Move schema
Table
Select ,Insert , Update , Delete,Truncate
CREATE , ALTER(add, drop , alter column, sp_rename) , DROP
Constraints
Primary KEY
FOREIGN KEY
NOT NULL
CHECK
UNIQUE
DEFAULT
Index (Clustred , non clustred)
Data Types
Num Data
Double
Float
Int
TinyInt
smallint
Bigint
medium int
BIt
Money
real
Char , varchar,nvarchar text,VARBINAY
Table , Cursor , sql_variant , Uniqueidentifier
Date , time ,datetime
User Defined Data and table Types
SQL predefined
Functions
String , math , Date,aggresive/grouping ,conversion
Operatots
Arthematic
+, - , * , / , %
Comparision
= , != , > , < , <>
Bit wise & , | , ^
Compound += , -= , ...
Logical
Set operators
Joins
Inner join
left join
right join
full join
self join
cross join
Equi join
Cross apply
Outer apply
Temporary Tables
Common Table Expression(CTE)
(Temporary result Set)
Recurcive CTE
Table variable @
Local temp Table #
Global Temp Table ##
_
Stroed Procedure
View
scalar value Function
Table value function
.
.
Transaction
ACID
Atomicity , Consistency , Isolation , Durability
Commit
Rollback
Savepoint
Set transaction.
Begin trans , END
Begin try .. End Try
Begin catch .. End catch
Execute as one unit
While
IF
IF ELSE
IFF
IF EXISTS
Case , when , then
Begin break , continue , end
@ variable
Declare
Set
Print
Triggers
DML
After
Instead
DDL
After
Instead
Pivot (row to column) , unpivot
Merge , using , Target ,source ,matched
Grouping Sets , Cube , Rollup ?
SQL Server
Data base engine
SSRS
SSIS
SSAS
SSRS
SSRS DEV V
Report server projrct
shared Data source
shared data set
Report
Build
Deploy in to SSRS
parsename()
Rank()
Dense_Rank()
Row_number()
ROW_NUMBER() attributes a unique value to each row
RANK() attributes the same row number to the same value, leaving "holes", it doesn't give you consecutive integer numbers.
DENSE_RANK() attributes the same row number to the same value, leaving no "holes"
LEAD(col , offset) Over (partistion by order by)
LAG(col , offset) Over (partistion by order by)
because they can refer to data from rows above or below the current row.
.DBMS
.RDBMS
os -> many instance, instance has many DB, DB-> many schemas, schema -> many tb, sp , view
calling:- " instance.database.schema.object name "
schemas EX:- sys, dbo, (user defined),...
Truncate vs Delete
.Logs
.Reseeds identity
.Fast
.where clause
.Firing triggers
.
order of EXECution * SELECT 5 ,FROM 1 , WHERE 2 , GROUP BY 3 , HAVING 4 , ORDERBY 6 , OFFSET - FETCH 7
. Off set ,fetch
Auto increment(Identity)
Len()
Replace()
NweId()
Lower()
Concat()
Charindex()
Right()
Left()
Reverse()
Cast()
Ltrim()
Rtrim()
Distinct()
SCOPE_IDENTITY()
AND
OR
ANY
ALL
BETWEEN
IN
EXISTS
LIKE
NOT
SOME
CASE
NULL
IS NULL
ISNULL(X,Y)
ISNOTNULL
NOT NILL
NULL
ALIAS AS
TOP ,
LIMIT ,
ROWNUM
MIN,
MAX,
COUNT ,
AVG
,SUM
,LIKE,
Sub Query (IN)
Co-related sub query
Injection
Wildcards
Union
UnionALL
Intersect
Except
Set no count
With(Nolock)
Sp Rename
sp_help
On Update Cascade
On delete Cascade
Over (partistion by)
Debug using
Sql server object explorer
Visual studio
ALt +F5 in query
https://www.sqltutorial.org/sql-window-functions/sql-partition-by/
SQL server types
DB engine
SSRS - Generate reports in different format
SSIS - ETL (Extract ,transform, load)
SSAS - Ware house
Auth
SQL server
Windows