Very interesting article here, about islands and gaps.
S
Author: S
fan of t-sql
some years experience with sql server
Conversation
Today I had a conversation with a person from a partner company related to a piece of code.
About 1 year ago, each company had to prepare/write some piece of code to take some information from a server and send/move to a different server. So, around this period, a year ago, we gather a 3 person team and developed our part of code/server.
Returning to present, the partner company, did not do anything about their code, so they use our code.
Today, I had a discussion about why a particular field is not populated.
Looking into it, we discovered and on their site there were info that were not entered/completed.
And before de conversation ended, was a comment: “it was easier if I received instructions“
I don’t know if it’s about the code we wrote… but it left me with a bitter taste…
S
Home Office
I started to like it.
And not just because the commute time you save , but also because you have the history of conversations . For example, one year ago, we developed an object (a view) and now some one is asking a question about it… I have comments in it, but for this particular question , no , nada, null.
So, I returned to mails and conversations and found that a particular field was put on NULL because was splitting the group by and because some information were not provided and needed clarifies from another department.
If we were in office, probably, this conversation would have been taken in live speech and no history remaining only notes if available.
S
Statistics steps
How many steps , a statistic can have?
200 ?
Online documentation, is saying that 200 steps.
DBCC Show Statistics is saying 200 steps here.
Also, the dm_db_stats_histogram, is saying the same here
But , checking an index, I get: 201 , where first row is having the NULL and additional 200 steps.
S
Select or insert
Imagine you have a dimension table and at some point you receive a value and don’t know if exists and in this case to return the @id of it or don’t exists and need to insert it .
Usually, this table should performed more selects then inserts.
So, the first statement will be to get the @id of it.
If the @id is null , meaning , this value don’t exists, then we need to insert it. So, the next step, is to insert it, and get the @id. This is the ideal case.
But what, another session/transaction/client is doing the same , with same value. And is a little faster, meaning that when you are still in “between” step1 and step2, he is already inserted. Then , a error is risen . See catch block for handle it.
In catch block, for particulars error numbers, the record exists now, and we are getting the @id back to client.
There are a few others way to deal with it, like table hint
CREATE PROCEDURE [dbo].[usp_dimMode_GetAndInsert]
(
@mddMode varchar(100)
)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @mddID as tinyint;
BEGIN TRY
SELECT @mddID = mddID FROM dbo.dimMode WHERE mddMode = @mddMode;
/* @mddID is null, insert*/
IF @mddID IS NULL
BEGIN
INSERT INTO dbo.dimMode(mddMode)
VALUES(@mddMode);
SELECT @mddID = SCOPE_IDENTITY();
END
RETURN @mddID;
END TRY
BEGIN CATCH
-- ignore duplicate key errors, throw the rest.
IF ERROR_NUMBER() IN (2601, 2627)
BEGIN
SELECT @mddID = mddID FROM dbo.dimMode WHERE mddMode = @mddMode;
RETURN @mddID;
END
END CATCH
END
S
NULL DFLT ON/OFF
There are 2 interesting settings :
ANSI_NULL_DFLT_ON
ANSI_NULL_DFLT_OFF
These only affect the nullability of a new column , when nullability is not specified.
When SET ANSI_NULL_DFLT_OFF is ON – new columns are NOT NULL
When SET ANSI_NULL_DFLT_ON is ON – new columns allow null values if the nullability status of the column is not explicitly specified
Both SET ANSI_NULL_DFLT_OFF and SET ANSI_NULL_DFLT_ON cannot be set ON at the same time.
either ANSI_NULL_DFLT_OFF or SET ANSI_NULL_DFLT_ON can be set ON
or both can be set OFF
From BOL :
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-null-dflt-off-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-null-dflt-on-transact-sql
S
IO block Size
I have read an interested blog post about the IO block Size.
Also, important information can be found here (please keep in mind it’s a sql server 2008 version).
The conclusions are:
- transaction log flushes – 512 Bytes UP TO 60KB
- data file IO
- checkpoint – 8KB to 1MB
- lazy writer -8KB to 1MB
- reading – 8*8KB to 1MB
- read ahead – 128KB to 512KB
- bulk loads – 256KB
- Backup/restore – 1MB
S
Mentor
Been a subscriber to the sqlskills newsletter/blog, and seeing that Mr. Paul Randall is volunteering for a new round of mentoring and having the chance to be mentored, to received advices from top leader in sql field, it’s a opportunity that I’m glade to embrace it.
I’m having 10+ years in development side, basic admin experience. I started as a programmer, touching front end (Access Data Project) and back end (Sql Server 2000). Here is where I discover the SQL and a connection was created. Also I need to mention the sql community, which is very open, very helpful. During this 10+years, I’ve make some changes: few companies, positions, made mistakes…
I’m working now on database side (writing queries, optimize queries, configure server, db) but also others areas like help desk, answering phone…but when it’s a problem with a stored procedure/query, it’s when I like to get involve.
I like to be aware with what is new in sql community by reading blogs (20+ sources in my feedly), watching webinars. As personal hobbies: fishing, going in nature, soccer.
What I’m hopping from this :
- how to work with managers, how to convince them
- for example, they hardly accepted to give us a “window” for moving to a newer version, in this case is sql server 2017, and install CUs, after aprox 3-4 months from that request. And if you want to install a CU, for 10-20min, oh boy, then another 3-4 months of waiting till stars are align. All must be up and running, good joke, with no HA, no AG, only backups)
- received guidance in career sector (am I in the right job, in the right company … )
Thank you for your effort, for your implication.
S
Importance of Statistics and Update of it
How important is to have statistics up to date versus fragmentations.
I start with a fresh copy of WideWorldImporters , call it WideWorldImporters_Test, in case of already existing database.
use master
go
restore database WideWorldImporters_Test
from disk = ‘D:\Kituri\SQL Server\WideWorldImporters-Full.bak’
with
move ‘WWI_Primary’ to ‘D:\Databases\DATA\WideWorldImporters_Test.mdf’,
move ‘WWI_UserData’ to ‘D:\Databases\DATA\WideWorldImporters_Test_UserData.ndf’,
move ‘WWI_Log’ to ‘D:\Databases\DATA\WideWorldImporters_Test.ldf’,
move ‘WWI_InMemory_Data_1’ to ‘D:\Databases\DATA\WideWorldImporters_Test_InMemory_Data_1’,
replace, stats=10
Here is a simple query:
select *
from Sales.Orders
where PickedByPersonID = 1
The execution plan is:
Let’s take a look at fragmentation:
select i.name as idxName
,ips.avg_fragmentation_in_percent
,ips.fragment_count
,ips.avg_page_space_used_in_percent
,ips.page_count
,STATS_DATE(o.object_id, i.index_id) as statsDate
from sys.dm_db_index_physical_stats(db_id(‘WideWorldImporters_test’),OBJECT_ID(‘Sales.Orders’),NULL,NULL,’DETAILED’) as ips
inner join sys.objects as o
ON o.object_id = ips.object_id
inner join sys.indexes as i
ON o.object_id = i.object_id
AND ips.index_id = i.index_id
where
i.name =’FK_Sales_Orders_PickedByPersonID’
Output:
So, high fragmentation, and the index (FK_Sales_Orders_PickedByPersonID) is not picked.
Now, let’s update the statistic of this index :
update statistics Sales.Orders (FK_Sales_Orders_PickedByPersonID) With fullScan
Looking, again , at the fragmentation :
Key Question: the execution plan is still the same , or …
select *
from Sales.Orders
where PickedByPersonID = 1
Conclusion:
1 – Have statistics up to date, is the most important thing
2 – Fragmentation is important, but not as statistics.
3 – Rebuilding an index is doing :
- Put in order the pages
- Update statistics with FULL SCAN
So, first do an update statistics with full scan, then run the query. If this doesn’t take you over the cross line, the next step is to rebuild it. And if this doesn’t take you , over the line, maybe you need to rewrite the Query or call someone.
S
Random password
How to generate a six character password.
You have to choose from a list of allowed characters (
(SELECT ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’ AS srcText , 62 as srcLen) AS S
)
select
empID as empID
,p.psw as psw1
,ca2.psw1+ ” +ca2.psw2+ ” +ca2.psw3+ ” +ca2.psw4+ ” +ca2.psw5+ ” +ca2.psw6 as psw2
–,len(ca2.psw1+ ” +ca2.psw2+ ” +ca2.psw3+ ” +ca2.psw4+ ” +ca2.psw5+ ” +ca2.psw6) as psw2Len
–,ca1.pozChar1,ca1.pozChar2,ca1.pozChar3,ca1.pozChar4,ca1.pozChar5,ca1.pozChar6
from dbo.Employees
CROSS APPLY
(SELECT ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’ AS srcText , 62 as srcLen) AS S
CROSS APPLY
(SELECT CAST(ROUND( (s.srcLen-1 – 0 ) *RAND(CHECKSUM(NEWID())) + 1,0) as int) as pozChar1,
CAST(ROUND( (s.srcLen-1 – 0 ) *RAND(CHECKSUM(NEWID())) + 1,0) as int) as pozChar2,
CAST(ROUND( (s.srcLen-1 – 0 ) *RAND(CHECKSUM(NEWID())) + 1,0) as int) as pozChar3,
CAST(ROUND( (s.srcLen-1 – 0 ) *RAND(CHECKSUM(NEWID())) + 1,0) as int) as pozChar4,
CAST(ROUND( (s.srcLen-1 – 0 ) *RAND(CHECKSUM(NEWID())) + 1,0) as int) as pozChar5,
CAST(ROUND( (s.srcLen-1 – 0 ) *RAND(CHECKSUM(NEWID())) + 1,0) as int) as pozChar6) as ca1
CROSS APPLY
(SELECT SUBSTRING(s.srcText,ca1.pozChar1,1) as psw1,
SUBSTRING(s.srcText,ca1.pozChar2,1) as psw2,
SUBSTRING(s.srcText,ca1.pozChar3,1) as psw3,
SUBSTRING(s.srcText,ca1.pozChar4,1) as psw4,
SUBSTRING(s.srcText,ca1.pozChar5,1) as psw5,
SUBSTRING(s.srcText,ca1.pozChar6,1) as psw6) as ca2
CROSS APPLY
(
SELECT CHAR(ROUND( (122-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) + '' +
CHAR(ROUND( (122-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) + '' +
CHAR(ROUND( (122-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) + '' +
CHAR(ROUND( (122-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) + '' +
CHAR(ROUND( (122-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) + '' +
CHAR(ROUND( (122-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) + '' +
CHAR(ROUND( ( 57-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) as psw
)p
Some very interesting script is here : https://stackoverflow.com/a/15038572/3992746
S
Sql Server 