1 Databases 1.1 System Databases 1.1.1 Master - All metadata about mdf, ldf, replication data, databases, db objects, logins. 1.1.2 Model - Used as template to create new db 1.1.3 msdb - Schedule info – Replication, backups, distribution db stores here 1.1.4 tempdb - For internal operations. When SQL Server restarts new tempdb will be created 1.1.5 ResourceDB - mssqlsystemresource.mdf and .ldf - SELECT * FROM sys.sysaltfiles WHERE DBID = 32767 1.2 User Databases 1.2.1 State Options ??? 1.2.1.1 Online/Offline 1.2.1.2 Read only / Read write 1.2.1.3 Single user/Multiuser / Restricted user 1.2.2 Properties 1.2.2.1 General - A - - sp_changedbowner will be removed – use ALTER AUTHORIZATION 1.2.2.2 Files - We can add new files here - To change the file name – we can change from GUI or Script - ALTER DATABASE DBNAME MODIFY FILE (Name = ‘oldname’, Newname =’newname’) - 1.2.2.3 File Groups - We can add new filegroups here - 1.2.2.4 Options 1.2.2.4.1 Overall - A - SQL SERVER ComLvl 2005 90 2008/R2 100 2012 110 2014 120 2016 130 2017 140 - 1.2.2.4.2 Automatic - ALTER DATABASE DBNAME SET AUTO-CLOSE OFF - ALTER DATABASE DBNAME SET AUTO-SHRINK ON - Etc., - 1.2.2.4.3 Containment - A - 1.2.2.4.4 Cursor - A - 1.2.2.4.5 Database Scoped Configurations - A - 1.2.2.4.6 FILESTREAM - A - 1.2.2.4.7 Miscellaneous - A - 1.2.2.4.8 Recovery - Page Verify o CHECKSUM - condition will compare before and after transform page o TORN PAGE DETECTION - - 1.2.2.4.9 Service Broker - A
1.2.2.4.10 State - A - 1.2.2.5 Change Tracking - A - 1.2.2.6 Permissions ??? add more users or roles and check permissions for – what are the permissions – from where we can get the details? - Need more information - 1.2.2.7 Extended Properties ??? - A - 1.2.2.8 Mirroring - A - 1.2.2.9 Transaction Log Shipping - A - 1.2.2.10 Query Store - A - 1.2.3 Useful Info Queries - sys.databases - DATABASEPROPERTYEX.sql 1.2.3.1 Create DB /* CREATE DB WITH DIFFERENT FILE GROUP, FILES CREATE DATABASE WWI02 ON PRIMARY ( NAME = 'WWI02_DB', FILENAME = 'C:\IBMTP\L4_Notes\Practice\WWI02\Data\WWI02.mdf', SIZE = 8 MB, MAXSIZE = 10000 MB, FILEGROWTH = 10 MB ), FILEGROUP FG_APPLICATION ( NAME = 'Application_DataFile', FILENAME = 'C:\IBMTP\L4_Notes\Practice\WWI02\Data\Application_DataFile.ndf', SIZE = 50 MB, MAXSIZE = 10000 MB, FILEGROWTH = 10 MB ), FILEGROUP FG_Purchasing ( NAME = 'Purchasing_DataFile', FILENAME = 'C:\IBMTP\L4_Notes\Practice\WWI02\Data\Purchasing_DataFile.ndf', SIZE = 50 MB, MAXSIZE = 10000 MB, FILEGROWTH = 10 MB ), ( NAME = 'Sales_DataFile', FILENAME = 'C:\IBMTP\L4_Notes\Practice\WWI02\Data\Sales_DataFile.ndf' ), ( NAME = 'Warehouse_DataFile', FILENAME = 'C:\IBMTP\L4_Notes\Practice\WWI02\Data\Warehouse_DataFile.ndf' ) LOG ON ( NAME = 'WWI02_LogFile', FILENAME = 'C:\IBMTP\L4_Notes\Practice\WWI02\Log\WWI02.ldf', SIZE = 30 MB ) */ GO 1.2.3.2 Alter DB 1.2.3.3 Drop DB 1.3 Tables 1.3.1 Properties 1.3.1.1 General - A - 1.3.1.2 Permissions - A - 1.3.1.3 Change Tracking - A - 1.3.1.4 Storage - A - 1.3.1.5 Secured Predicates - A - 1.3.1.6 Extended Properties - A - 1.3.2 Columns 1.3.2.1 Properties 1.3.2.1.1 General - A - 1.3.2.1.2 Extended Properties - A - 1.4 Triggers - CREATE OWN SCRIPT FOR ALL CONDITIONS - 1.4.1 Overview - Triggers are bound to specific tables to perform some action for automatic execution whenever users try to do execute data modification commands 1.4.1.1 Magic Tables - Two tables >> “Inserted” and “Deleted” Action Inserted Deleted Insert Table contains all the inserted rows Table contains no row Delete Table contains no rows Table contains all the deleted rows Update Table contains rows after update Table contains all the rows before update 1.4.2 Types of Triggers 1.4.2.1 After Trigger 1.4.2.2 Instead of Trigger - Instead of trigger is used when we want to perform another action instead of the action which causes the trigger to fire. Instead of trigger can be defined in case of Insert, Delete and Update. 1.4.3 DDL Triggers For Alter_table, Create_table, Drop_Table - these triggers are not created for a particular table, but they are applicable to all the tables on the database. - DDL type event like Alter command, Drop command and Create commands 1.4.3.1 How it is useful? - 1) To prevent any changes to the database Schema - 2) If we want to store the records of all the events, which change the database schema. - EVENTDATA() - Eventdata() is a functions which returns information about the server or database events.It returns value of XML type - 1.4.3.2 Example Create TRIGGER DDL_DropTable ON database FOR Drop_table AS Begin PRINT 'Table cannot be dropped.' INSERT into command_log(commandtext) Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/ CommandText)[1]','nvarchar(1000)') Rollback; end 1.4.4 Nested Triggers - Nested Trigger: – In Sql Server, triggers are said to be nested when the action of one trigger initiates another trigger that may be on the same table or on the different table. - We can also stop the execution of nested triggers through the following SQL Command: - sp_CONFIGURE 'nested_triggers',0 GO - RECONFIGURE - GO o 1.4.5 Recursive triggers - In SQL Server, we can have the recursive triggers where the action of a trigger can initiate itself again. - Recursive trigger can only be possible when the recursive trigger option is set. o ALTER DATABASE databasename o SET RECURSIVE_TRIGGERS ON | OFF 1.4.5.1 Direct recursion - In Direct recursion, action of a trigger initiates the trigger itself again which results in trigger calling itself recursively. 1.4.5.2 Indirect recursion - In Indirect recursion, action on a trigger initiates another trigger and the execution of that trigger again calls the original trigger, and this happen recursively. Both the triggers can be on the same table or created on the different tables. 1.4.6 Useful Queries 1.4.6.1 Find out all the triggers in Database select o1.name as 'Trigger' , o2.name as 'Table' from sys.objects o1 inner join sys.objects o2 on o1.parent_object_id=o2.object_id and o1.type_desc = 'sql_trigger' 1.4.6.2 Finding all the triggers defined on a particular table sp_helptrigger Tablename example:- sp_helptrigger 'Customer' 1.4.6.3 Finding the definition of a trigger sp_helptext triggername For example:- sp_helptext 'trig_custadd' 1.4.7 Others How to Disable a trigger DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL } ON { object_name | DATABASE | ALL SERVER } [ ; ] Disabling a DML trigger on a table DISABLE TRIGGER 'trig_custadd' ON Customer; Disabling a DDL trigger DISABLE TRIGGER 'DDL_Createtable' ON DATABASE; Disabling all triggers that were defined with the same scope DISABLE Trigger ALL ON ALL SERVER; How to enable a trigger Enabling a DML trigger on a table ENABLE Trigger 'trig_custadd' ON Customer; Enabling a DDL trigger ENABLE TRIGGER 'DDL_Createtable' ON DATABASE; Enabling all triggers that were defined with the same scope ENABLE Trigger ALL ON ALL SERVER; How to drop a trigger Dropping a DML trigger :- DROP TRIGGER trig_custadd ; Dropping a DDL trigger DROP TRIGGER DDL_Createtable ON DATABASE; 1.5 Data Types
Exact Numeric Bigint, int, smallint, tinyint, decimal, numeric, money, smallmoney Approximate Numeric Float, real Date & Time Date, time, datetime, datetime2, datetimeoffset, smalldatetime Character Strings Char, varchar, text Unicode Character Strings Nchar, nvarchar, ntext Binary Strings Binary, varbinary, image Other Data Types Cursor, timestamp, hierarchyid, uniqueidentifier, sql_variant, xml, table, Spatial Types 1.6 Indexes - A - Any time a SQL index is created, stats are automatically generated to store the distribution of the data within that column. 1.6.1.1 Mechanism of Index - Indexes follows B-Tree structure - Root Level / Intermediate Level / Leaf Level - CLUSTERED - The Leaf Nodes of clustered index contains original value. - NON-CLUSTERED – The leaf nodes contains pointer that pointing to the original data - 1.6.2 Fragmentation - DBCC show contig(‘tablename’, ‘Indexname’) 1.6.3 Types of Indexes 1.6.3.1 Clustered - determines how the data is written to the disk - a clustered index is automatically created with a primary key - If a table doesn’t have any clustered index that is called “Heap Table” 1.6.3.2 Nonclustered - SQL Server 2016 as high as 999 per table - It maintains only pointer pointing original value. It depends on clustered index. 1.6.3.3 Columnstore ??? - They are the standard for storing and querying large data warehousing fact tables 1.6.3.4 Spatial - geometry and geography. 1.6.3.5 XML - primary and secondary. A primary index is a requirement in order to create secondary 1.6.3.6 Full-text - provides efficient support for sophisticated word searches and English language queries in character string data. 1.7 Stored Procedure - The maximum number of parameters that can be passed to a SQL Server stored procedure is 2,100. - store a set of SQL statements and accompanying programming statements within the database and run them later - Stored procedure has INPUT, EXECUTION and OUTPUT - Advantages are Maintanability, Encapsulate Business Logic, Stronger Security, Efficiency - So if you have sensitive queries, you can avoid having users actually see the query code that is run. They can only call the stored procedure but they can’t actually see what SQL makes up the stored procedure - Create with prefrix usp – sp for system sps - Use named parameters while calling / executing - There are several ways to return values from a stored procedure, through the result of a SELECT statement, using output parameters, or return command. - Parameters are o INPUT o OUTPUT - 1.7.1 table-valued parameters - The maximum size that a table-valued parameter can have is equal to the maximum memory size of the database server. 1.8 Q&A Unique 1.8.1 Indexes Vs Unique Constraints - We can enforce uniqueness of values in specific column(s) in a SQL Server table by creating a unique constraint or a unique index on those column(s). |
