Demonstrate between fn_dblog() and fn_dump_dblog()

fn_dblog()

This table-valued function (which was DBCC Log prior to SQL Server 2005) allows you to view the entries in online transaction log. This procedure accepts 2 parameters, the start and the end LSN. To see all available entries NULL can be passed for both parameters, and all the entries in the active portion of the online log will be displayed.

  SELECT * FROM fn_dblog(NULL,NULL)

fn_dump_dblog()

This function reads both the online log and log backups and accepts 68 parameters. All the parameters need to be specified in order to execute the statement.

SELECT * FROM fn_dump_dblog
(NULL, NULL, N'DISK', 1,
N'D:\Backup\New folder\ReadingDBLog_201503022236.trn', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT)WHERE Operation = 'LOP_DELETE_ROWS' GO

Parameter	Description
@start	The start LSN
@end	The end LSN
@devtype	This is the backup device type. The default value is DISK. Other valid values are NULL(DISK) | DISK | TAPE | VIRTUAL_DEVICE
@seqnum	This indicates which backup to read from a backup device if there is more than one backup in a backup file. The default value is 1.
@fname1	This parameter accepts the path to the backupfile.
@fname2 to @fname64	These parameters are used to specify additional backup files, if the media set has multiple media families.

ParseName in SQL Server

PARSENAME (Transact-SQL) Returns the specified part of an object name. The parts of an object that can be retrieved are the object name, owner name, database name, and server name. The PARSENAME function does not indicate whether an object by the specified name exists.

use YOUR_DB

Declare @Sample Table

(MachineName varchar(max))

Insert into @Sample

values 

('Ab bb zecos mm'),('a Zeng')

select * from @Sample

  SELECT 

  Reverse(ParseName(Replace(Reverse(MachineName), ' ', '.'), 1)) As [M1]

 , Reverse(ParseName(Replace(Reverse(MachineName), ' ', '.'), 2)) As [M2]

 , Reverse(ParseName(Replace(Reverse(MachineName), ' ', '.'), 3)) As [M3]

 ,Reverse(ParseName(Replace(Reverse(MachineName), ' ', '.'), 4)) As [M4]

  FROM  (Select MachineName from @Sample

  ) As [x] 

In figure first one is Input table and second one is Output Table  :

SQL Server Question must know by a DBA.

1. What are the Edition of SQL server ?

Enterprise

SQL Server Enterprise Edition includes both the core database engine and add-on services, with a range of tools for creating and managing a SQL Server cluster. It can manage databases as large as 524 petabytes and address 12 terabytes of memory and supports 640 logical processors (cpu cores).

Standard

SQL Server Standard edition includes the core database engine, along with the stand-alone services. It differs from Enterprise edition in that it supports fewer active instances (number of nodes in a cluster) and does not include some high-availability functions such as hot-add memory (allowing memory to be added while the server is still running), and parallel indexes.

Web

SQL Server Web Edition is a low-TCO option for Web hosting.

Business Intelligence

Introduced in SQL Server 2012 and focusing on Self Service and Corporate Business Intelligence. It includes the Standard Edition capabilities and Business Intelligence tools: Power Pivot, Power View, the BI Semantic Model, Master Data Services, Data Quality Services and x Velocity in-memory analytics.

Workgroup

SQL Server Workgroup Edition includes the core database functionality but does not include the additional services. Note that this edition has been retired in SQL Server 2012.^[9]

Express

SQL Server Express Edition is a scaled down, free edition of SQL Server, which includes the core database engine. While there are no limitations on the number of databases or users supported, it is limited to using one processor, 1 GB memory and 10 GB database files (4 GB database files prior to SQL Server Express 2008 R2).^[10] It is intended as a replacement for MSDE. Two additional editions provide a super set of features not in the original Express Edition. The first is SQL Server Express with Tools, which includes SQL Server Management Studio Basic. SQL Server Express with Advanced Services adds full-text search capability and reporting services.

2. What is default size of the SQL database ?

On a separate note, with the default installation of SQL Server the mdf file of model database is 3 MB and ldf is about 1 MB.

3. What is default TCP/IP port for SQL SERVER ?

By default, named instances (including SQL Server Express) use dynamic ports. That means that every time that the Database Engine starts, it identifies an available port and uses that port number. If the named instance is the only instance of the Database Engine installed, it will probably use TCP port 1433. we can choice our own port also.

4. What are the SQL Server system Database ?

SQL Server System Databases

• Master
  • Purpose - Core system database to manage the SQL Server instance.  In SQL Server 2005, the Master database is the logical repository for the system objects residing in the sys schema.  In SQL Server 2000 and previous editions of SQL Server, the Master database physically stored all of the system objects.
  • Prominent Functionality
    • Per instance configurations
    • Databases residing on the instance
    • Files for each database
    • Logins
    • Linked\Remote servers
    • Endpoints
  • Additional Information
    • The first database in the SQL Server startup process
    • In SQL Server 2005, needs to reside in the same directory as the Resource database
    • Sources for Database Information - SQL Server 2000 to 2005 Crosswalk
    • SQL 2000 to 2005 Crosswalk - Startup Parameters
• Resource
  • Purpose - The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.
  • Prominent Functionality
    • System object definition
  • Additional Information
    • Introduced in SQL Server 2005 to help manage the upgrade and rollback of system objects
    • Prior to SQL Server 2005 the system related data was stored in the master database
    • Read-only database that is not accessible via the SQL Server 2005 tool set
    • The database ID for the Resource database is 32767
    • The Resource database does not have an entry in master.sys.databases
• TempDB
  • Purpose - Temporary database to store temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc. Each time the SQL Server instance is restarted all objects in this database are destroyed, so permanent objects cannot be created in this database.
  • Prominent Functionality
    • Manage temporary objects listed in the purpose above
  • Additional Information
    • Properly Sizing the TempDB Database
    • Each time a SQL Server instance is rebooted, the TempDB database is reset to its original state
• Model
  • Purpose - Template database for all user defined databases
  • Prominent Functionality
    • Objects
    • Columns
    • Users
  • Additional Information
    • User defined tables, stored procedures, user defined data types, etc can be created in the Model database and will exist in all future user defined databases
    • The database configurations such as the recovery model for the Model database are applied to future user defined databases
• MSDB
  • Purpose - Primary database to manage the SQL Server Agent configurations
  • Prominent Functionality
    • SQL Server Agent Jobs, Operators and Alerts
    • DTS Package storage in SQL Server 7.0 and 2000
    • SSIS Package storage in SQL Server 2005
  • Additional Information
    • Provides some of the configurations for the SQL Server Agent service
    • For the SQL Server 2005 Express edition installations, even though the SQL Server Agent service does not exist, the instance still has the MSDB database
    • Missing SQL Server Agent History
    • MSSQLTips Category - SQL Server Agent
• Distribution
  • Purpose - Primary data to support SQL Server replication
  • Prominent Functionality
    • Database responsible for the replication meta data
    • Supports the data for transaction replication between the publisher and subscriber(s)
  • Additional Information
    • MSSQLTips Category - Replication
• ReportServer
  • Purpose - Primary database for Reporting Services to store the meta data and object definitions
  • Prominent Functionality
    • Reports security
    • Job schedules and running jobs
    • Report notifications
    • Report execution history
  • Additional Information
    • MSSQLTips Category - Reporting Services
• ReportServerTempDB
  • Purpose - Temporary storage for Reporting Services
  • Prominent Functionality
    • Session information
    • Cache
  • Additional Information
    • MSSQLTips Category - Reporting Services

5. What are the authentication modes in SQL Server ? How can it be changed ?

Please check this Link :     Authentication in SQL SERVER

6. What is Query execution Plan in SQL SERVER ?

The query execution plan is a useful tools offered by SQL Server. It describes the steps and order of a SQL query during executions. After that a DBA can decide which part of the query is slow. SQL Server can create execution plans in two ways :

Actual execution Plan : (CTRL+M) :-
It is created after execution of the query and contains the steps that were performed.

Estimate Execution Plan : (CTRL+L) :-
It is created without executing the query.

7. SQL Server Replication ?

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.

Restore in SQL Server

To recover a SQL Server database from a failure, a database administrator has to restore a set of SQL Server backups in a logically correct and meaningful restore sequence. SQL Server restore and recovery supports restoring data from backups of a whole database, a data file, or a data pages.

Restore Backup taken using the Backup command. Without backup we will not able to restore data.
The restore may be happen with following scenario.

• complete restore: Restore an entire database from a full database backup.
• partial restore: Restore part of a database.
• file restore: Restore specific files or filegroups to a database.
• page restore: Restore specific pages to a database.
• transaction log restore: Restore a transaction log onto a database.
• Revert a database to the point in time captured by a database snapshot.
• Online Restore : Online restore is allowed only in Enterprise edition of SQL Server. For more information about online restore visit. Online Restoer

Additional Considerations About RESTORE Options

Discontinued RESTORE Keywords

The following keywords were discontinued in SQL Server 2008:

Date of Birth Calculation

use YOUR_DB

DECLARE @FromDate DATETIME = '1992-01-26 23:59:59.000',

        @ToDate   DATETIME = '2016-12-19 00:00:00.000',

        @Years INT, @Months INT, @Days INT, @tmpFromDate DATETIME

SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)

 - (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate),

          @FromDate) > @ToDate THEN 1 ELSE 0 END)

SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate)

SET @Months =  DATEDIFF(MONTH, @tmpFromDate, @ToDate)

 - (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate),

          @tmpFromDate) > @ToDate THEN 1 ELSE 0 END)  

SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate)

SET @Days =  DATEDIFF(DAY, @tmpFromDate, @ToDate)

 - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate),

          @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) 

SELECT @FromDate FromDate, @ToDate ToDate,

       @Years Years,  @Months Months, @Days Days

Output : -

ACID Properties :-

In computer science, ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties of database transactions. In the context of databases, a single logical operation on the data is called a transaction. For example, a transfer of funds from one bank account to another, even involving multiple changes such as debiting one account and crediting another, is a single transaction.

Jim Gray defined these properties of a reliable transaction system in the late 1970s and developed technologies to achieve them automatically.

In 1983, Andreas Reuter and Theo Härder coined the acronym ACID to describe them.

Atomicity : -

if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged.

Example :-

a transaction to transfer funds from one account to another account. Making a withdrawal operation from the first account and a deposit operation on the second. If the deposit operation failed, you were not able to withdraw amount from the first account.

Logical Example :-

Suppose you are updating a table with 1000 number of rows. In middle you have cancel your query due to some reason. 

So the whole transaction will remain same and it will not effect to the table till the full transaction were completed.

Consistency :-

any transaction brings the database from one valid state(unique check numbers) to another.

a database tracking a checking account may only allow unique check numbers to exist for each transaction on what time transaction has performed.

Example :

Customer CIF number can give all tracking information about your account details. what when and what transaction you had performed.

Isolation : -

the execution of concurrent transactions brings the database to a state as if the transactions were executed serially, one by one

a customer looking up a balance must be isolated from a concurrent transaction involving a withdrawal from the same account. Only when the withdrawal transaction commits successfully and the teller looks at the balance again will the new balance be reported.

Example :-

After transaction successfully completed it should trigger new and current available balance in your both account (transfer account and withdrawal account).

Durability :-

once committed, the transaction remain so, even in the case of errors, power loss, or crashes.

A system crash or any other failure must not be allowed to lose the results of a transaction or the contents of the database. 

Example :-

Between the performance if your system get shutdown or crash it will not effect to the database when you are performing on production.

Create an unique Cluster INDEX over multiple columns

Unique Cluster Index on multiple columns is very helpful when we are trying to insert same values on that columns.

Suppose we have four columns and every time we want to insert some unique combination values on the tables .

If the user inserting same  data then it should through an error.

Below check the Example :

create table Test

(

i int not null, j int not null, s

varchar(10) not null, t varchar(10) not null

)

go

alter table Test add constraint pk primary key (i, j,s,t)

select * from Test

Now trying to insert some values :

insert into Test values(1,1,'c','d')--row inserted

insert into Test values(1,1,'c','d') --error (same data)

insert into Test values(1,1,'c','c') --row inserted

insert into Test values(1,1,'d','c') --row inserted

insert into Test values(1,1,'d','c') --error (same data)

insert into Test values(1,2,'d','c') --row inserted

SQL Server Query Store (SQL SERVER 2016)

Among that all the features of SQL server below i will discuss only Query Store in SQL SERVER 2016:-

Query Store is a new SQL Server component that captures queries, query plans, run time statistics, and

more in a persistent store inside the database. It is a database-scoped persistent store of query workload

history. You can think of it as a flight recorder, or black box, for your database. It can also enforce policies

to direct the SQL Server Query Processor to compile queries to be executed in a specific manner, such as

forcing plans.

Query Store primarily targets administrative scenarios for performance troubleshooting and identifying

regressed workloads. It also collects query texts and all relevant properties, as well as query plan choices

and performance metrics. This collection process works across restarts or upgrades of the server and

across recompilation of indexes, providing many configurable options for customization. Query Store

integrates with existing query execution statistics, plan forcing, and manageability tools. It is a dedicated

store for query workload performance data and captures the history of plans for each query. It also

captures the performance of each plan over time and persists the data to disk (works across restarts,

upgrades, and recompiled). Query Store enables you to:

==> Quickly find and fix a plan performance regression by forcing the previous query plan. It can fix

queries that have recently regressed in performance due to execution plan changes.

==> Determine the number of times a query was executed in a given time window and assist a DBA in troubleshooting performance resource problems.

==> Identify costly queries (by execution time, memory consumption, and so forth) in the past “x”

hours, and audit the history of query plans for a given query.

==> Analyze the resource (CPU, I/O, and Memory) usage patterns for a particular database.

==> Maintain performance stability during upgrade to SQL Server 2016.

==> Perform A/B testing and back up changes with performance data.

The Query Store can be viewed and managed through Management Studio or by using views and

procedures. There are seven Query Store Catalog Views that can present information about the Query

Store. There are also various procedures to follow when configuring the Query Store:

• sys.database_query_store_options: Returns query store options for a given database.
• sys.query_context_settings: Contains information about the semantics affecting context settings associated with a query.
• sys.query_store_plan: Contains information about each execution plan associated with a query.
• sys.query_store_query: Contains information about the query and its associated overall aggregated run time execution statistics.
• sys.query_store_query_text: Contains the Transact-SQL text and the SQL handle of the query.
• sys.query_store_runtime_stats: Contains information about the run time execution statistics information for the query.
• sys.query_store_runtime_stats_interval: Contains information about the start and end times of each interval over which run time execution statistics information for a query has been collected.

Live Query Statistics :

Before SQL Server 2016, developers and DBAs would have to troubleshoot query performance with

Show plan at query run time. This run time execution plan, often referred to as query execution statistics,

enables you to collect metrics about the query that occurred during its execution (such as its execution

time and actual cost) after the query finishes running.

SQL Server 2016 has a new feature—Live Query Statistics (LQS)—that allows you to view what is

happening during the query execution. LQS lets you view a list of active queries and associated

statistics, such as current CPU/memory usage, execution time, query progress, and so on. This enables

rapid identification of potential bottlenecks for troubleshooting query performance issues. LQS also allows

users to drill down into a query plan of an active query and view live operator-level statistics, such as the

number of generated rows, elapsed time, operator progress, and live warnings. This facilitates in-depth

troubleshooting of query performance issues without forcing you to wait for query completion, so you

can watch the statistics change during the query execution in real-time.

The live execution plan can also be accessed from the Activity Monitor :

Temporal database support :-

Data is rarely static. Seeing how data has evolved over time—or querying data as of a particular point in

time—can be very valuable. SQL Server 2016 promises to simplify this process with a new feature called

Temporal Tables. With this feature, one can specify that the data history for a table be stored

How does it work?

A temporal table is also referred to as a system-versioned table. Each temporal table (or

“system-versioned” temporal table) consists of two tables: one for current data, and the other for

historical data. Within each of these tables, two additional DateTime (DateTime2 datatype) columns are

used to define the period of validity for each record: a system start time (SysStartTime) column, and a

system end time (SysEndTime) column. The current table contains the current value for each record. The

history table contains previous values for each record, if any, and the start and end times for the period

for which it was valid.

INSERTS: On an INSERT, the system sets the value for the SysStartTime column to the UTC time

(coordinated universal time) of the current transaction based on the system clock, and it assigns

the value for the SysEndTime column to the maximum 9999-12-31. This marks the record as open.

UPDATES: On an UPDATE, the system stores the previous value of the record in the history table

and sets the value for the SysEndTime column to the UTC time of the current transaction based

on the system clock. This marks the record as closed, with a period recorded for which the record

was valid. In the current table, the record is updated with its new value, and the system sets the

value for the SysStartTime column to the UTC time for the transaction based on the system clock.

The value for the updated record in the current table for the SysEndTime column remains the

maximum value of 9999-12-31.

DELETES: On a DELETE, the system stores the previous value of the record in the history table and

sets the value for the SysEndTime column to the UTC time of the current transaction based on the

system clock. This marks the record as closed, with a period recorded to indicate when the record

was valid. In the current table, the record is removed. Queries of the current table will not return

this value. Only queries that deal with history data return data when a record is closed.

MERGE: On a MERGE, MERGE behaves as an INSERT, an UPDATE, or a DELETE based on the

condition for each record.