Download SQL Server 2019

Download SQL Server 2019

Are you looking for a link on how to download SQL Server 2019, well here it is

https://www.microsoft.com/en-us/sql-server/sql-server-2019

You can also download the whitepaper from here : https://info.microsoft.com/ww-landing-SQLDB-Microsoft-SQL-Server-WhitePaper.html

And watch a Webinar from here

https://info.microsoft.com/ww-ondemand-intro-sql-server-2019.html?wt.mc_id=undefined

Find out which SQL Agent Jobs that are currently running

Find out which SQL Agent Jobs that are currently running

If you ever want to find out which SQL Server Agent jobs that are currently running on your server without using Management Studio, just run the code below.

SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
Js.step_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
ON ja.job_id = js.job_id
AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE
ja.session_id = (
SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC
)
AND start_execution_date is not null
AND stop_execution_date is null
order by 2 desc

Has it finished?

Has it finished? is a question you will ask yourself many times when you are running something within SQL Server. If you are backing up a database, restoring a database or you just want to find out how long a task on your server will take to complete, then just run the code below to return information about each request that is executing within SQL Server.

SELECT
dmr.session_id,
dmr.command,
CONVERT(NUMERIC(6,2),dmr.percent_complete)AS [Percent Complete],
CONVERT(VARCHAR(20),DATEADD(ms,dmr.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),dmr.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),dmr.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),dmr.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours]
,CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,dmr.statement_start_offset/2,
CASE WHEN dmr.statement_end_offset = -1 THEN 1000
ELSE (dmr.statement_end_offset-dmr.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)
)
) [sqltxt]
FROM sys.dm_exec_requests dmr
where CONVERT(NUMERIC(6,2),dmr.percent_complete) > 0

Configuring SQL Server 2017

Configuring SQL Server 2017

How that you have installed SQL Server 2017, it is time to get your hands dirty and start to create databases. But before we do that, it is a good idea to configure your SQL Server 2017 environment correctly.

To do this, just Right Click the server and click properties.

Configuring SQL Server 2017
Configuring SQL Server 2017

 

Once you have done that, the following screen should appear.

Configuring SQL Server 2017
Configuring SQL Server 2017

Here is where you can set some properties for your SQL Server 2017 environment that will help you control the performance, administration and security of your databases.

In the General tab, you can see it just shows you general information about your server that you may need to know from time-to-time.

The Memory tab controls how much of the operating system SQL Server will use. Most of the time, the only option to you need to change on this screen is the Maximum Server Memory option. There are many calculators on the internet on how to work out the correct value, but if you just want a good value, subtract 4-8 gig from the total amount of memory you have on your pc/laptop and put in the value i.e.
So If you have 14 gigabytes installed on your hardware, type in ‘10000’ and that will mean SQL Server will use 10 gigabytes and 4 gigabytes will be used for Windows. You might need to type in a less number if you have other memory intensive software on your hardware.

Processor tab, you don’t usually change anything in this tab so just leave things as default.

Security tab, again you don’t change anything in this tab unless you have a security issue that you need to resolve. In a working environment, you might need to audit more processes in your situation so changing some of the settings for security purposes might need to happen. In a testing environment, I usually leave these as default.

The Connection tab governs how many connections you want your SQL Server to handle and if you’re going to allow remote connections to your server.

The Database Settings tab is where you can set where decide files are stored on your hard drive. Databases have two files when they are created, an MDF file and an LDF File. It is good practice to place this files on different hard drives for performance. In this table, you can set the default location on where this files will be created.

For setting up the environment for testing, you don’t need to change the ‘Advanced’ and ‘Permissions’ tabs but future posts, I will go into more details.

This is just a simple guide on how to configure your SQL Server 2017 environment and will go into more details in future blogs. If there is anything you want me to cover in more detail, just leave a comment below.

Constraints in SQL Server

Constraints in SQL Server play a big role in keeping data accurate in a database.

Within SQL Server there are several methods of doing this and one way is using constraints
• NOT NULL – Indicates that a column cannot store a NULL value
• UNIQUE – Ensures that each row for a column must have a unique value
• PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have a unique identity which helps to find a particular record in a table more easily and quickly
• FOREIGN KEY – Ensure the referential integrity of the data in one table to match values in another table
• CHECK – Ensures that the value in a column meets a specific condition
• DEFAULT – Specifies a default value when specified none for this column

This is the code to assign a NOT NULL constraint to a Column. Column1 doesn’t allow NULLS, Column2 allows NULLS.

CREATE TABLE TestTable1
(
PKID int NOT NULL,
Column1 varchar(255) NOT NULL,
Column2 varchar(255)
)

This is the code to assign UNIQUE constraint to a Column

CREATE TABLE TestTable2
(
PKID int NOT NULL UNIQUE,
Column1 varchar(255) NOT NULL,
Column2 varchar(255)
)

This is the code to assign Primary Key constraint to a column

CREATE TABLE TestTable3
(
PKID int PRIMARY KEY,
Column1 varchar(255) NOT NULL,
Column2 varchar(255)
)

If you assign a primary key to a column, it automatically gains a unique constraint. You can have many unique constraints on a table, but you can only have one primary key. Each table should have a primary key and primary keys cant have null values.

This is the code to assign a foreign key constraint to a column
CREATE TABLE TestTable4
(
P_Id int PRIMARY KEY,
PKID2 int NOT NULL,
Column1 int NOT NULL,
CONSTRAINT FK_CON FOREIGN KEY (P_Id)
REFERENCES TestTable3(PKID)
)

A foreign key constraint is used to make sure that data can’t be deleted if it matches data in another table.

This is the code to assign a CHECK constraint to a column

CREATE TABLE TestTable5
(
PKID int NOT NULL,
Column1 INT,
Column2 VARCHAR(50) NOT NULL,
CONSTRAINT chk_Column1 CHECK (Column1> 0 AND Column2=’Test’)

)

This makes sure that the value in Column1 is greater than 0 and Column2 contains the word ‘Test.’

This is the code to assign DEFAULT constraint to a column
CREATE TABLE TestTable6
(
PKID int NOT NULL,
Column1 INT,
Column2 VARCHAR(50) NOT NULL DEFAULT (‘Test’),

)

If a value is not entered in column2, the word ‘Test’ will be inserted.

JOIN and APPLY

CROSS APPLY vs INNER JOIN vs LEFT JOIN vs OUTER APPLY

So you want to improve your SQL skills and have come across some new operators like JOIN and APPLY and are wondering when you should use them.

In this blog, I will focus on the following operators in the simplest terms and show you how to use them.

CROSS APPLY and INNER JOIN are very similar and
OUTER APPLY and LEFT JOIN are very similar.

In certain situations, you can write queries using CROSS APPLY or INNER JOIN and use OUTER APPLY and LEFT JOIN and they could bring back the exact results.

The only real difference is that CROSS APPLY and OUTER APPLY are better than LEFT and INNER JOIN when you are using a function, or you want to write an advanced SQL Command to bring back certain results

Run the following script in a SQL Server Environment to see these operators in action.

You can examine the script to see how they differ and you should be able to play around with the text to fully understand what is going on.
create table Company (

    companyId int identity(1,1)

,   companyName varchar(100)

,   zipcode varchar(10) 

,   constraint PK_Company primary key (companyId)

)

GO

 

create table Person (

    personId int identity(1,1)

,   personName varchar(100)

,   companyId int

,   insertdate DATETIME

,   constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId)

,   constraint PK_Person primary key (personId)

)

GO

 

insert Company

select ‘ABC Company’, ‘19808’ union

select ‘XYZ Company’, ‘08534’ union

select ‘123 Company’, ‘10016’ 

 

insert Company

select ‘No Employees’, ‘11111’ 

 

 

insert Person

select ‘Alan’, 1, ‘20141011 09:00:00’ union

select ‘Bobby’, 1,’20141011 10:00:00′ union

select ‘Chris’, 1, ‘20141011 11:00:00’ union

select ‘Xavier’, 2, ‘20141011 12:00:00’ union

select ‘Yoshi’, 2, ‘20141011 13:00:00’ union

select ‘Zambrano’, 2, ‘20141011 14:00:00’ union

select ‘Player 1’, 3, ‘20141011 15:00:00’ union

select ‘Player 2’, 3, ‘20141011 16:00:00’ union

select ‘Player 3’, 3, ‘20141011 17:00:00’

 

 

/* using CROSS APPLY */

select *

from Person P

cross apply (

    select *

    from Company C

    where p.companyid = c.companyId

) CompanyPerson

 

/* the equivalent query using INNER JOIN */

select *

from Person p

inner join Company c on p.companyid = c.companyId

 

 

/* using CROSS APPLY – brings back the oldest inserted person by company*/

select *

from  Company c 

cross apply (

    select top (1) *

    from Person p

    where p.companyid = c.companyId

 order by insertdate

 

) CompanyPerson

 

 

/* using OUTER APPLY – brings back all the companies and the oldest inserted person by company*/

select *

from  Company c 

outer apply (

    select top (1) *

    from Person p

    where p.companyid = c.companyId

 order by insertdate

 

) CompanyPerson

order by companyName desc

 

/*  brings back all the companies and the people */

select *

from company c

left join person p on p.companyid = c.companyId

order by companyName desc

 

The script produces the following results

CROSS APPLY vs INNER JOIN vs LEFT JOIN vs OUTER APPLY
CROSS APPLY vs INNER JOIN vs LEFT JOIN vs OUTER APPLY

I hope the above examples help you understand the difference. Leave a comment below if you want to leave a comment.

SQL Server Functions

SQL Server Functions

Sometimes you read books on subjects about SQL Server and you just want to learn the basics, well here are the basics of SQL Server Functions

Functions can be Scalar or Table-valued
Scalar returns one value and Table-valued functions (TVF) returns…well a table of results and this are usually found in the FROM clause of a statement.

Functions can be either Deterministic or Nondeterministic
Deterministic = This means they return the same value any time they are called with a specific set of input values.
i.e. SELECT LEN(‘TEST) will always return 4

Nondeterministic = different results every time
i.e SELECT GETDATE()

Example code is taken from the MSDN website

The following example creates the user-defined function (Scalar) ISOweek. This function takes a date argument and calculates the ISO week number. For this function to calculate correctly, SET DATEFIRST 1 must be invoked before the function is called.
IF OBJECT_ID (N’dbo.ISOweek’, N’FN’) IS NOT NULL
DROP FUNCTION dbo.ISOweek;
GO
CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @ISOweek int;
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+’0104′);
–Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+’12’+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
–Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1;
RETURN(@ISOweek);
END;
GO
SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME,’12/26/2004′,101)) AS ‘ISO Week’;

The following example returns an inline table-valued function.
It returns three columns ProductID, Name and the aggregate of
year-to-date totals by the store as YTD Total for each product sold to the store.

IF OBJECT_ID (N’Sales.ufn_SalesByStore’, N’IF’) IS NOT NULL
DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS ‘Total’
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
WHERE C.StoreID = @storeid
GROUP BY P.ProductID, P.Name
);

That is a quick review of SQL Functions, if you have any questions, just leave a comment below.