Wednesday, October 18, 2017

IDENTITY Column - and the steps to remove from your table

Issue:


Your table has a column identified as an IDENTITY (autonumber) column, and you need to

1) keep the column and its contents
2) remove the IDENTITY attribute for the column.

Approach:


As of 2017, SQL Server does not have an ALTER TABLE command that allows you to remove the IDENTITY property. The basic steps to remove an IDENTITY property are as follows:

- add a new column, and copy contents into the new column
- drop any indexes and constraints using the TO BE dropped column
- drop the old column
- rename the new column with the old name
- rebuild indexes and constraints

Here is a sample set up scripts to get the job done:

The first part creates a test table, the second part removes the IDENTITY property.

-------------------------------------------------------
-- setup:

use work;
go

create table work.dbo.testidentity
(  tblid     int identity(1,1) not null
  ,somevalue varchar(20) null
  );

alter table work.dbo.testidentity add constraint pk_tblid  primary key clustered ( tblID);

  insert into  work.dbo.testidentity (somevalue)
  values ( 'one' )
,( 'two' )
,( 'three' )
,( 'four' );

select * from work.dbo.testidentity ;

-------------------------------------------------------
-- the process to remove:

alter table work.dbo.testidentity add temp_tblID int null;
go
select * from work.dbo.testidentity ;
go

update work.dbo.testidentity set temp_tblID = tblid;
go
alter table work.dbo.testidentity alter column temp_tblID int not null;
go
select * from work.dbo.testidentity ;
go
alter table work.dbo.testidentity drop  constraint pk_tblid;
go
alter table work.dbo.testidentity  drop column tblid ;
go
select * from work.dbo.testidentity ;
go
EXEC sp_rename 'dbo.testidentity.temp_tblID' , 'tblID', 'COLUMN';
go
select * from work.dbo.testidentity ;
go

alter table work.dbo.testidentity add constraint pk_tblid  primary key clustered ( tblID);
go

Sunday, October 8, 2017

Fix Windows 10 - 100% Disk Usage in Task Manager

A colleague directed me to the URl below with a list of tips to fix the 100% disk usage problem I was having with Windows 10.   I also disabled all the Google Chrome Sync "on - custom settings"

You can find the site here:  Windows 10 100% disk usage in Task Manager.

I only needed to do three steps:


  1. disable windows search
  2. disable SuperFetch service
  3. disable all Google Chrome:  Sync "on - custom settings"

Monday, September 18, 2017

CONCAT different Rows into a single attribute value

Problem:


Using T-SQL, you need to concatenate values from different rows into a single attribute value.  The ideal solution would a function that allows you to CONCAT the values from a GROUP BY, much like a SUM(0) function might perform.

Solution


Let's say we want a single row, that lists out all the distinct Titles in the person.person table in the AdventureWorks2012 database. All we need is a string SUM() or CONCAT() command for a group.  We could imagine the command might look something like this:

       
with t as 
( select Title = isnull(p.Title,'n/a'), count(*) as TitleCnt
 from [Person].[Person] as p 
 group by p.Title
) 
Select AllTitles = MYROWCONCATFUNCTION(t.Title), sum(TitleCnt) as TitleCount from t
 
 

SQL Server 2017?
If you are using SQL Serer 2017 or Azure SQL Database, you can use the STRING_AGG function.

Not using the latest version?
For now, the most straight forward approach is to use FOR XML PATH. One key downside might be that for some, this command is considered a blackbox that might not be reliable in the longer term. So, if you are not comfortable using FOR XML PATH, go to the ProjectMX write-up on all the ways you can approach row concatenation using T-SQL:  ProjectMX: Concatenating row values in Transact-SQL.

FOR XML PATH

For a simple approach to concatenate the values from multiple rows is to setup a SubQuery using FOR XML PATH .
       
with t as 
( select Title = isnull(p.Title,'n/a'), count(*) as TitleCnt
 from [Person].[Person] as p 
 group by p.Title
) 
Select  
 sum(TitleCnt) as TitleCount
,(SELECT ',' + t.title + ' ' FROM t where (t.Title = t.Title) FOR XML PATH ('')) AS AllTitles
 from t;
 
 

With the output looking like this:

         TitleCount AllTitles
         ----------          ---------
         19972 ,Sr. ,Mrs. ,n/a ,Sra. ,Ms. ,Ms ,Mr.

To remove the leading comma, we can add a SUBSTRING function:

       
with t as 
( select Title = isnull(p.Title,'n/a'), count(*) as TitleCnt
 from [Person].[Person] as p 
 group by p.Title
) 
Select  sum(TitleCnt) as TitleCount
  ,SUBSTRING((SELECT  ',' + t.title + ' ' from t where (t.Title = t.Title)
  FOR XML PATH ('')),2,9999) AS AllTitles
  from t;
 
 

With the output looking like this:

         TitleCount AllTitles
         ----------          ---------
         19972 Sr. ,Mrs. ,n/a ,Sra. ,Ms. ,Ms ,Mr.


Need more information on FOR XML? See: m FOR XML (docs.microsoft.com)

Monday, August 14, 2017

Expert Beginner a.k.a "Sophomore" or the "wise fool"

Erik Dietrich has an excellent write-up of what he calls the "Expert Beginner."  As you read it, I'm sure you'll recognize the situation in a current or former organization. In the broadest terms, the academic phrase "sophomore" is well suited.

You can find it here: How Developers Stop Learning: Rise of the Expert Beginner

Another interesting post:  How to hire a senior developer

Using the definition provided by Wikipedia, would get:

The term is derived from Greek σόφισμα (sophisma), 'acquired skill, clever device, method', altered to resemble a compound of the Greek words σοφός (sophos), 'wise' and μωρός (moros), 'foolish, dull'.



AI and the demise of the DBA

Are DBA's soon to be the way of the buggy whip?

I've long held that the need for a full time DBA to tune a database was past its prime. In an age where database vendors like Microsoft hire brilliant database theorists, it makes far more sense for them to work out the algorithms to manage the database than having a full-time database mechanic (DBA) on staff to monitor and adjust the system.

With the release of SQL Server 2017, a new feature, called Automatic Tuning (SQL Server 2017) has been introduced.

While SQL Server has made progress in this area, the open source world has made advances as well. OtterTune has made some initial advances tuning and optimizing two open source database flatforms:  MySQL and Postgres.

Here are key links to learn more:

AI topics - OtterTune

A detailed look into OtterTune by Adrian Colyer

Research Paper - Automatic Database Management System Tuning Through Large-scale Machine Learning

Wednesday, August 9, 2017

Forecast vs. Predict - what's the difference?

We get ask this frequently.

Fortunately, the team at PowerPivotPro has provided a nice write-up. So, rather than re-create the wheel, here it is:

The Difference Between Forecasting & Predictive Analytics


Thursday, May 25, 2017

SSRS support for Power BI Desktop - preview download available!

A preview of the on-premises SSRS with support for Power BI is now in preview.  We'll technically, it's called "Power BI Report Server" preview.  But under the covers, as far as we know, it is an enhanced version of SSRS.  For the SQL Server 2016 release, Microsoft completely rebuilt the SSRS engine, with full support for HTML5, and now Power BI Desktop reports.

About a year ago, we reported that both the SSRS and Power BI teams indicated future Power BI support as part of SSRS. And earlier in the year, we did an initial review of the Azure based VM that included the enhanced SSRS engine. That review found that SSRS did indeed do a nice job with Power BI Desktop reports.

The preview download is available here:  Microsoft Power BI Report Server - May 2017 Preview.