Identity values jumping by 1000 - IDENTITY_CACHE

Sometimes you will see a large jump of 1000 values in an identity column of a table. This can happen when SQL Server caches 1000 IDs for the table. If the SQL Service is restarted, these cached IDs can be lost, causing SQL Server to begin the next increment at the ID post the previous cache.

From SQL 2017, this behaviour can be turned off by setting IDENTITY_CACHE = OFF

From the SQL Server documentation:

SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

Resources:

Pinal Dave's explanation and examples: https://blog.sqlauthority.com/2018/01/24/sql-server-identity-jumping-1000-identity_cache/

StackOverflow post about the issue, and workarounds if ID gaps are not acceptable: https://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database

SSIS - fix "only administrators have access to the Integration Services service" error

 Scheduling SSIS packages from SQL Agent can produce the following error on a new installation of SQL Server:

“Connecting to the Integration Services service on the computer “…” failed with the following error: "Access is denied". By default only administrators have access to the Integration Services service. On Windows Vista and later the process must be running with administrative privileges in order to connect to the Integration Services service.”

The following article describes the fix (adding permissions for the SQL Agent service to SSIS via DCOM)

https://www.mssqltips.com/sqlservertip/5077/permissions-to-schedule-an-ssis-package-from-sql-server-agent-and-the-file-system/

Split out delimited text within SQL Server

Sometimes splitting delimited text within SQL server is easier than splitting the text on the way in via SSIS etc. The following (all shamelessly stolen from the link below) is an example of splitting the following myAddress column:

into the following columns:

using this SQL:

SELECT 

     REVERSE(PARSENAME(REPLACE(REVERSE(myAddress), ',', '.'), 1)) AS [Street]

   , REVERSE(PARSENAME(REPLACE(REVERSE(myAddress), ',', '.'), 2)) AS [City]

   , REVERSE(PARSENAME(REPLACE(REVERSE(myAddress), ',', '.'), 3)) AS [State]

FROM dbo.custAddress;

GO

Reference:

https://www.mssqltips.com/sqlservertip/6321/split-delimited-string-into-columns-in-sql-server-with-parsename/

Getting started with Python Web Scraping

Getting started with Python on a Mac was fairly straightforward, but I had a few stumbling blocks on Windows. The easiest way to get started with Python, a decent IDE & terminal, and additional libraries, was to install Anaconda on Windows, and use the Spyder IDE.

Using Beautiful Soup for web scraping, the following is a script I wrote to get the current top non fiction audiobooks from Audible:

# Script to get top Audible personal development books  

import requests 
from bs4 import BeautifulSoup 

URL = 'https://www.amazon.co.uk/Best-Sellers-Books-Self-Help-How/zgbs/books/2996349031/ref=zg_bs_nav_b_3_2996114031' 
page = requests.get(URL) 
soup = BeautifulSoup(page.content, 'html.parser') 
results = soup.find('ol', class_ = 'a-ordered-list a-vertical') 

list_elems = results.find_all('li', class_ = 'zg-item-immersion') 
for list_elem in list_elems[:50]: 
    rank_elem = list_elem.find('span', class_ = 'zg-badge-text') 
    title_elem = list_elem.find('div', class_ = 'p13n-sc-truncate p13n-sc-line-clamp-1') 
    author_elem = list_elem.find('span', class_ = 'a-size-small a-color-base') 
  
    title_elem = title_elem.text.replace('  ', '') 
    title_elem = title_elem.replace('\n', '')  

    print(rank_elem.text.replace('#', '') + ' - ' + title_elem + ' - ' + author_elem.text.replace('\t','')) 

Returns the following:

 

SQL Server Migration using dbatools Powershell Module

Migrations between SQL Servers can be laborious, and doing them manually leaves a lot of room for human error. Scripting a migration using the dbatools Powershell module is incredibly simple, quick and robust.

Prerequisites

Powershell with dbatools module installed:

https://dbatools.io/download/

Steps

Run the following Powershell:

    $startDbaMigrationSplat = @{

    Source = 'sourceServer'

    Destination = 'destinationServer'

    BackupRestore = $true

    SharedPath = '\\path\both-servers-can-access'

    }

    Start-DbaMigration @startDbaMigrationSplat -Force | Select * | Out-GridView

This will migrate everything associated with the source instance, to the destination instance (inc logins, linked servers, startup procs...), using a backup and restore method for moving the databases. Use the -Exclude flag (see documentation link below) to exclude bits you don't want to be migrated over

Resources

dbatools migration documentation: https://docs.dbatools.io/#Start-DbaMigration

YouTube vid demo of dbatools migration: https://www.youtube.com/watch?v=hg8tovMRX2k

Fix issues with Powershell: https://stackoverflow.com/questions/4037939/powershell-says-execution-of-scripts-is-disabled-on-this-system

Stack Exchange article on migrations: https://dba.stackexchange.com/questions/258559/side-by-side-upgrade-and-migrate-sql

Primary Key Capacity

Use the following SQL to determine how "full" your primary keys are, based on the number of existing values, and the data type used:

select

       '[' + p.TABLE_SCHEMA + '].[' + p.TABLE_NAME + ']' as [table]

       ,c.COLUMN_NAME

       ,ident_current(p.TABLE_SCHEMA + '.' + p.TABLE_NAME) as MaxID

       ,cls.DATA_TYPE

       ,cast(100 - isnull(ident_current(p.TABLE_SCHEMA + '.' + p.TABLE_NAME), 0) * 100 /

                     case cls.DATA_TYPE

                           when 'int' then 2147483647

                           when 'smallint' then 32767

                           when 'tinyint' then 128

                     end as decimal(4, 1))

       as [% of range left]

from

       INFORMATION_SCHEMA.TABLE_CONSTRAINTS p

       inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

              on c.TABLE_NAME = p.TABLE_NAME

              and c.CONSTRAINT_NAME = p.CONSTRAINT_NAME

       inner join INFORMATION_SCHEMA.COLUMNS cls

              on c.TABLE_NAME = cls.TABLE_NAME

              and c.COLUMN_NAME = cls.COLUMN_NAME

where

       p.CONSTRAINT_TYPE = 'PRIMARY KEY'

       and cls.DATA_TYPE in ('int', 'smallint', 'tinyint')

order by

       5 asc

Importing XML into SQL Server Table

The following is an excellent example of how to import XML into a table within SQL Server, and query the XML once imported:

https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/