Purpose of system databases in MS #SQL Server

 Purpose of system databases in MS #SQL Server.

https://youtu.be/Kx7xFDDjtcU

Learn #sql, #dba, #dataScience, #powerBI technologies.

Crystalspiders Institute

contactus@crystalspiders.com

+91 7338457517

SQL query to get the persons whose date of birthday falls in next specified no of days

-- SQL Query to pull the persons whose date of birth falls in the next specified no of days.

Here is the sample query for the same.

create table #empdata (eid int identity(1,1), dob date)
go
insert into #empdata (dob) select '2000-12-30'
insert into #empdata (dob) select '2000-12-31'
insert into #empdata (dob) select '2000-12-29'
insert into #empdata (dob) select '2000-12-29'
insert into #empdata (dob) select '2000-01-01'
insert into #empdata (dob) select '2000-01-02'
insert into #empdata (dob) select '2000-12-03'
insert into #empdata (dob) select '2000-12-30'
insert into #empdata (dob) select '2000-12-26'
go
declare @today date = '2019-12-28'
declare @noofdays int = 10
declare @enddate date = dateadd(dd,@noofdays,@today)
select * from (
select *
, datediff(dd 
           ,@today 
       , dateadd(yy
,iif(year(@today) <> year(@enddate) and month(dob)=1
         ,datediff(yy,dob,@today)+1
     ,datediff(yy,dob,@today))
             ,dob)
  ) as [noOfDaysToGo]
from #empdata
) as persons where noOfDaysToGo BETWEEN 0 AND @noofdays
go


Results:
---------
eid dob noOfDaysToGo
1 2000-12-30 2
2 2000-12-31 3
3 2000-12-29 1
4 2000-12-29 1
5 2000-01-01 4
6 2000-01-02 5
8 2000-12-30 2

States of database files

Online: Files will be online if the DB is in online/offline/emergency/read-only state.

Restoring: If the db/file being in the middle of restore process (DB Restore with norecovery mode),                     then the DB files shows RESTORING state.

Suspect: File will go on suspect state if it is failed during the recovery process.

• If the file in primary file group is failed during the recovery process, then the entire database will go on suspect state.
• If the file is not part of primary file group, then only that file will go on suspect state.
• If the db/file is in suspect state, then do DB/File restore or CHECKDB with allow data loss option.

Recovery pending: File went into this state, if the recovery process postponed due to piecemeal restore process.

         A database can also shows recovery pending state, if any of files (t-log) are missed out while

         recovery process or lack of system resources (disk space, cpu, memory...).. these system

         resources may not allow the DB to come to online.

         Solutions:

         i. Try to take db offline and bring it back to online, in case your database went into recovery

             pending due to lack of resources.

         ii. Set the database to emergency mode, then Detach the DB, attach the DB without any log                     files (use the script and remove the log file details in the script), it will add a new log file and               DB will come back to online. Use this method in case of missing/corrupted t-log file.

             We can't detach the database if it is in recovery pending mode. Put that db into emergency

             mode before detach.

             OR else

            Set the database to offline, move the data and t-log files to new paths, and alter the database                in order to add those new paths to database (don't add the t-log file(s), just alter only data                    files path), try to set the database to online. In this case, the DB will come to online by

            creating a new log file in the default location. 

            OR else

            Simply put the database to offline and set it back to online.. When we are setting it back to

            online, the missing t-log file will be automatically added to default path.

          iii. In case, the t-log/data file got corrupted and unable recover the database. set the db to

           emergency, run the CHECKDB with allow data loss, set the db into online.

SQL Server DBA Daily Activities

Daily Activities
----------------
Ensure server Availability. Check any of the SQL servers are down
Check any of the disks running on out of space
Monitor the maintenance plans - Backup, Rebuild/Reorganize the index, update stats, Clean up the history
Monitoring TempDB database files and their Sizes
Regular Jobs (If any errors follow up with leads)
Sql Services Monitoring
Monitoring Event Logs and Error Logs when ever required
Activity Monitor to check the performance of server
Long Running Jobs.
Check for Blocking sessions
Check for any deadlocks
Check the Monitoring tool (SpotLight) for any job failures and work with associate team
Review the Incident/Change/Request tickets
Attend team meeting every day
Fill the daily status report ( How many tickets are completed, in-progress, active/open)

Weekly Activities
-----------------
Check if any tables are created in system database and drop them
Check if the space availability in the servers. Delete the unnecessary files from C:, D:, E: drives. Raise a request to add more storage if any drive running out of space
Check the growth of databases in all the servers
Fill the timesheets on time

Difference between TOP, OFFSET & OFFSET with FETCH

The TOP will help to display the no.of records

The example query to use TOP. The following query will display only top 10 records.

SELECT TOP(10) SalesOrderID AS SOID, SalesOrderDetailID AS SODID,

OrderQty AS [orderQuantity], UnitPrice as pricePerUnit

  FROM [AdventureWorks2012].[Sales].[SalesOrderDetail]

  WHERE SalesOrderDetailID < 10000

  order by SODID desc

Logical query processing when we are using TOP with ORDER BY:

  First FROM clause will execute

  Second WHERE clause will execute 

  Third SELECT clause will execute

  Fourth ORDER BY clause will execute

  Fifth TOP will execute 

  

-- How to skip the top no.of records from the result set?

We can use OFFSET to skip the records.

How to execute a sql statement on multiple SQL Server instances at the same time?

I assume that you are able to connect to all the SQL Server Instances.

Step 1: Open the SQL Server Management Studio, Go to View in main menu and select 'Registered Servers'. Then will display a Registered Servers window as shown in below images.

How to restore a database (TDE Enabled) from one SQL Server instance to another SQL Server instance

I assume that you are taking backup and restoring the database on the same version and edition of SQL Server Instances.

We will get the following errors while doing restoration of a database (TDE Enabled) from one SQL Server Instance to another SQL Server Instance.

Connect to the SQL Server Default and Named Instances

Connect to the SQL Server Default and Named Instances:

A system can run multiple SQL Server instances. Only one default instance whose name is "MSSQLSERVER" and more than one named instances whose name was given at the time of instance installation.

First, find out what are all the instances that you are running in your system. Go to start, all programs, open the installation folder of "Microsoft SQL Server 2012", go to Configuration Tools, click on "SQL Server Configuration Manager". Shows the same steps below.

Setup an Account as Administrator in Windows Operating System

Setup an Account as Administrator in Windows Operating System:

The following post assumes that you are using Windows 7 operating system.

First, Go to Control Panel. Click on "Add or remove user accounts" as shown in below screen.

If you already set an account as Administrator with a password, then you don't need to proceed further. Example: the user "Chreddy" in below image.

How to Install SQL Server 2012

Installation of SQL Server 2012:

First, setup an administrator account with a password in your system. Click here to see how to set an account as administrator and set the password in windows operating systems.

Second, download the SQL Server 2012 Enterprise Evolution Edition. Click here to see how to download the SQL Server 2012 Enterprise Evaluation Edition.

Once you have the extracted files, you will see an installation file (setup.exe) as shown in below image.

Double click on setup file to open installation, click 'yes' to open the window of SQL Server Installation centre as shown in below image.

Download SQL Server 2012 Enterprise Evaluation Edition

Download SQL Server 2012 Enterprise Evaluation Edition:

We can download the SQL Server 2012 Enterprise Evaluation Edition from the link below.

Go to the following site and Click on the Download as shown in below image.
https://www.microsoft.com/en-in/download/details.aspx?id=29066

Integrity and Manipulation Components in Relational Model

What is the Integrity and Manipulation in Relational Model?

Integrity: -- It forces the business rules and uniqueness of data.

1. Candidate Key - It is an attribute, which helps to identify an individual tuple in a relation.

2. Primary Key - It is an attribute (one or more attributes), that help to identify an individual tuple in a relation. We can also call this attribute as candidate key. But this attribute will not allow duplicate data values and NULL (unknown data). This is the entity integrity rule.

3. Foreign Key - This is the key (child relation) which stores data that should be available in one of the attribute (primary key) in another relation (parent relation). This is the referential integrity rule.

Manipulation: -- The manipulation defines reading (SELECT) and Writing (INSERT, UPDATE & DELETE) the data on a relation. Two parts in the manipulation.

1. Relational Algebra: It is a collection of operators which can be applied among the relations.

2. Relational Assignment Operator: It will take the output of "Relational Algebra" and assign it to another relation.

Relational Algebra: -- Restrict data; Project data, Product, Union; Intersect & Difference --> These operators will produce a relation as output.

Restrict: - Fetch the specific tuples from a relation. It is nothing but applying WHERE condition on a relation. It returns a relation with tuples which satisfy the particular condition.

Project: -- Defines what attributes that we have to display. It returns a relation with all the tuples with specified attributes.

Product: -- It returns a relation that contains all possible tuples with the combinations from two or more relations. This operation is known as Cartesian product (Cross Product, Cross Join and Cartesian Join).

Union: -- It returns a relation with all tuples that appear in either or both relations. It avoids the duplicate tuples.

Intersect: -- It returns a relation with all tuples that appear in both relations.

Difference: -- It returns a relation with all the tuples in first relation which are not available in second relation

Relational Assignment Operator: The below example shows who to assign a relation to another relation.

SELECT * INTO #RelationProducedFromHelloRelation from Hello JOIN Hello1 ON Hello.id = Hello1.id

Generate the INSERT scripts from a table in PostgreSQL

Question: I want to generate INSERT scripts from a table data.

Answer: We can use the following query to pull the existing data and prepare the INSERT statements.

SELECT 'INSERT INTO schemaName.tableName SELECT
'''||name||''',
'''||value||''';
' from schemaName.tableName ;

1. Change the schema and table name.
2. Add every column as shown in query. The name and value are two columns.

ERROR: Record "OLD" is not assigned yet or ERROR: Record "NEW" is not assigned yet

The following error will get while calling the trigger function by a trigger in postgresql. The trigger function may be using one of these variables OLD and/or NEW.

Error: record "old" is not assigned yet
Error: records "new" is not assigned yet

Solution: Use the "FOR EACH ROW" in your trigger. The OLD and NEW variables are only available for UPDATE operation. Only "NEW" variable is available for INSERT operation. Only "OLD" variable is available for DELETE operation.

How to create a trigger in postgresql or plpgsql

How to create a trigger in postgresql or plpgsql?

    1. Create two tables as shown below. The dbo.emp table will store the sample user data where as dbo.emp_audit will store the data which is triggered from the trigger function.

CREATE TABLE dbo.emp (ID INT, NAME VARCHAR(10), SALARY INTEGER);

CREATE TABLE dbo.emp_audit (ID INT, NAME VARCHAR(10), SALARY INTEGER, CHANGED_DATE TIMESTAMP WITHOUT TIME ZONE, DESCRIPTION TEXT);

    2. Create a trigger function to run the actual PLPGSQL statements when a trigger fires. That means when an INSERT, DELETE or UPDATE operation occurred on dbo.emp table, then the corresponding trigger will execute the following function dbo.tfn_emp_insert.

CREATE OR REPLACE FUNCTION dbo.tfn_emp_insert()

RETURNS TRIGGER AS

$BODY$

BEGIN

INSERT INTO dbo.emp_audit SELECT NEW.ID, NEW.NAME, NEW.SALARY, NOW()::TIMESTAMP, ‘INSERT’;

RETURN NEW;

END

$BODY$

LANGUAGE PLPGSQL;

    3. Create a trigger on the table emp as shown below. The following trigger will execute the function tfn_emp_insert when an insert operation occurs in the dbo.emp table.

CREATE TRIGGER emp_inst

AFTER INSERT

ON dbo.emp

FOR EACH ROW

EXECUTE PROCEDURE dbo.fn_emp_insert();

    4. Let’s test the trigger functionality by inserting few records in dbo.emp table.

INSERT INTO dbo.emp SELECT 1,’one1’,1000;

INSERT INTO dbo.emp SELECT 2,’two2’,2000;

INSERT INTO dbo.emp SELECT 3,’three3’,3000;

    5. Verify the both table’s data to see whether the trigger is working as expected.

SELECT * FROM dbo.emp;

SELECT * FROM dbo.emp_audit;

Notes: If you want to catch the each and every row by using OLD and NEW variables, then use the “FOR EACH ROW” statement in the trigger script.

What is the clustered index and how it will arrange the key values in sql server?

What is the difference between count(1) and count(*) in SQL Server?

The count(1) refers the count of records in the first column. If there are any null values in the first column, it won't count that record.
The count(*) refers the count of records on overall the columns. It will display the no.of records.

Thanks

Authorization Process in SQL Server | Best SQL Training in Banglaore

#Authentication and #Authorisation Process in #SQL Server..!

#sqldba, #msbi #tsql #ssis #ssrs #ssas #freshers #dba #SQLServer#powershell #dbdesign #databasemodelling
#training