SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Stairway to SSAS Tabular Level 7: Time Intelligence

Site Owners from SQLServerCentral

Time Intelligence is a common methodology in Dimensional Modeling that allows for calculations of measures based on relative dates. These include “To-Date” measures like Year to Date or Quarter to Date as well as Previous Period comparisons. Once these are created within a model, other DAX functions can be used for metrics like Year over […]

SQL Server Reporting Services: What I wish I knew when I started?

Additional Articles from MSSQLTips.com

In this article Scott Murray covers some lessons learned over the years when working with SQL Server Reporting Services that he wish he knew when he started with SSRS.

Simple Steps in SQL Change Automation Scripting

Additional Articles from Redgate

Phil Factor demonstrates the bare essentials of SCA PowerShell scripts that can form the basis for an automated process for database delivery or help improve your current process.

Free eBook: Understanding SQL Server Concurrency

Press Release from Redgate

When you can’t get to your data because another application has it locked, a thorough knowledge of SQL Server concurrency will give you the confidence to decide what to do.

From the SQL Server Central Blogs - Database File Changes

SQLRNNR from SQL RNNR

Data professionals around the globe are frequently finding themselves occupied with figuring out why and when a file (data or log) for a database has changed in size. Whether...

From the SQL Server Central Blogs - Multiple Identity Inserts

Bert Wagner from Bert Wagner

Watch this week’s video on YouTube. This week I want to share something that surprised me about using SQL Server’s SET IDENTITY_INSERT statement. I started with two tables with...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Making a DataFrame
I have two lists in Python: >>> a ['Ford', 'Chevrolet', 'BMW', 'Audi', 'GMC', 'Porsche', 'Toyota', 'Honda'] >>> b ['F150', 'Suburban', 'X5', 'Q5', 'Tahoe', 'Cayenne', 'RAV4', 'CRV'] I want to combine these in a dataframe, with the columns for make and model labeled correctly. Which of these will do this?
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by Steve Jones - SSC Editor)

New statistics

I create a table and add some data with this code:

CREATE TABLE dbo.Ages (AGE INT)
;
INSERT INTO dbo.Ages (AGE)
SELECT TOP 188 ABS(CHECKSUM(NEWID())) % 10 FROM sys.objects
;
SELECT AGE FROM dbo.Ages
;

Auto create statistics is enabled, as is auto update statistics. What happens when I run this code?

SELECT
      object_id
    , name
    , stats_id
    , auto_created
    , user_created
    , no_recompute
    , has_filter
    , filter_definition
    , is_temporary
    , is_incremental
FROM  sys.stats
WHERE object_id = OBJECT_ID(N'dbo.ages', N'U')
;

Answer: I get no rows back

Explanation: When I create a table and add data, no statistics are created. If I added an index or queried the table, I should have statistics created. Ref: Introduction to Statistics - https://www.sqlservercentral.com/articles/introduction-to-statistics Statistics - https://docs.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-2017

Discuss this question and answer on the forums

Featured Script

Add working days but avoid holidays

steve 14359 from SQLServerCentral

A function to add or subtract working days taking into account weekends and using a table of non-working days.

CREATE FUNCTION [dbo].[fns_AddWorkingDays]
(
@StartDate datetime,
@N INT
)
RETURNS datetime
AS
BEGIN
-- This ensures that however the server is configured for dates
-- the function will know the DATEPART(DW values for Saturday
-- and Sunday
declare @SaturdayDW int
declare @SundayDW int
set @SaturdayDW = DATEPART(DW,CONVERT(datetime,'2019 January 5')) -- A Saturday
set @SundayDW = DATEPART(DW,CONVERT(datetime,'2019 January 6')) -- A Sunday
-----------------------------------------------------------------
-- If @N is zero then reduce the date by 1
-- and try adding one day
if @N=0
begin
set @N=1
set @StartDate=DATEADD(DAY,-1,@StartDate)
end
----------------------------------------------------------------
-- If @N GTE 0 then increment dates while counting
-- If @N LT 0 then decrement dates while counting
declare @increment int
if @n>=0 set @increment = 1 else set @increment = -1
----------------------------------------------------------------
declare @CountDays int
set @CountDays=0
declare @LoopDate datetime
set @LoopDate = @StartDate
while @CountDays<ABS(@N) begin set @LoopDate=DATEADD(DAY,@increment,@LoopDate) while exists(select HolidayID from tblHoliday where HolidayDate=@LoopDate) or DATEPART(DW,@LoopDate)= @SaturdayDW or DATEPART(DW,@LoopDate)= @SundayDW begin set @LoopDate=DATEADD(DAY,@increment,@LoopDate) end set @CountDays=@CountDays+1 end return @LoopDate END

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 - Administration

Need connect string to connect from Linux to SQL Server using an A/D group name - ISO way to setup a connection string to connect from Linux to SQL Server using an A/D group name. (we do NOT want to use a SQL Login!) - this link details the setup for a Linux connection to a SQL Server database: https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Linux - one of the final instructions during setup is to add […]

SQL Server 2017 - Development

How to split time into hourly slot using SQL (can use view,or stored proc or fun - I have data in table which has start date, end date and duration. I want to show hourly time slot. **logic**: - Condition 1. If start date =9:00 and end date = 11:00 then show the date as 09:00-10:00 10:00-11:00 It should repeat 2 times and all related column data will also repeat 2 times. […]

SQL Server 2016 - Administration

SQL Cluster - I am looking at a Cluster that has been built by a person that during the SQL Cluster installation he did not select all CSV's that related to the instance. This has meant that the storage fails over independently which is not ideal. there has been a case that two of the required volumes were […]

Admin puzzler - ok so Friday off I get a call from user doing posting taking over an hour( should take 5 minutes). No blocking do deadlock no high CPU. Active sessions show higher reads than normal. No open trans. Go to mgmt. studio and when I try to open objects on main pharmacy database it times out. […]

Debug is not visible in sql management studio - Hi, we have installed sql server 2016 standard edition. developers want to debug the stored procedure. In the management studio we didn't see 'Debug' option at all. I tried add remove button but nothing working. Please help me to add the debug option in sql server 2016 management studio. Thanks, Jo

SQL Server 2016 - Development and T-SQL

Problem to Display Field With OPENXML - Hello community, I am trying to Importing and Processing data from XML files into SQL Server tables, following the example post on this site : https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/ by Arshad Ali. This is the first time i use this technique, then i put only one filed to display, but i dont know why the result is always […]

Administration - SQL Server 2014

Auto Shrink on live DB - Hello, On one of our customers' live server there are both live and UAT DBs. The latter occasionally replicated from the live DB upon customer's request. All has been done and maintained by DBA's. When such a copy generated I am normally required to truncate several of our biggest tables (some over 100 MBs), which […]

SQL Server 2005 Installation After 2014 as Default Instance on Win Server 2012 - Greetings! Did anyone notice the anomaly as in below screenshot? When tried to install SQL Server 2005(bare version 9.0 - No SP) after SQL Server 2014(version 12.0) is already installed, the SQL 2005 threw error showing SQL 2014 installation as SQL 2000. OS : Windows Server 2012 X64 SQL Server 2014: Developer X86 SQL Server […]

Development - SQL Server 2014

Stored Procedure with Parameters in Excel - Long Run time - I have a stored procedure with two date variables, Start date, End date. If I run this in excel, it takes forever and eventually deadlocks. ALTER PROCEDURE [dbo].[lost_sales_v2] @SD datetime, @ED datetime If I take the variables out of the procedure name and embed a set date range in the query, it returns in less […]

SQL 2012 - General

Using Correlated Queries with OPENQUERY - Good day everyone, I'm new to this forum and was hoping I could get some help with what I'm trying to accomplish. I'm trying to delete old users from each respective database from a list I have. Now I'm using SQL Server 2012 to do this, but the databases i'm connecting to are in […]

SQL Server 2012 - T-SQL

Index Size Question - I'm trying to track down storage usage on our SQL Server 2012 instance. I have a table with 11,703,018 rows and 10 indexes (9 non-clustered + PK) on it. The PK is a clustered uniqueidentifier (I know not the greatest but i'm stuck with it for now) that only has the uniqueidentifier column included (size […]

Find time different based on Type by making group - I have data like attached picture, I want to make group each event when it starts from 1 and end till its max of eventId = 14 and calculate the time difference ... as shown in picture. If there are 8 EventType with 1 then it should have max of 8 eventType with EventType 14. […]

SQL Server 2008 - General

Unable to load text data in using BULK INsert with XML Format file - I have the following table IF OBJECT_ID('[TempDB]..[#LoadData]') IS NOT NULL DROP TABLE [dbo].[#LoadData]; CREATE TABLE [dbo].[#LoadData] ( [RTOPartyID] INT NULL , [Prefix] NVARCHAR(255) NULL , [FirstNames] NVARCHAR(255) NULL , [LastName] NVARCHAR(255) NULL , [Suffix] NVARCHAR(255) NULL , [PartyType] NVARCHAR(255) NULL , [PlaceOfBirth] NVARCHAR(255) NULL , [TaxIdentificationNumber] NVARCHAR(50) NULL , [CrownServant] BIT NULL , [DateOfBirth] […]

Replication DB name - Hi, Supposing we would like to replicate a copy of a DB, called DB1 to another server. Does the database need to be called DB1 on the subscribers or does that not matter?

Powershell

Something akin to GROUP BY in SQL - So I have an array: $array = @() $Props = [ordered]@{Table="Table1"; Col1=1; Col2=2} $array += New-Object psobject -Property $Props $Props = [ordered]@{Table="Table2"; Col1=4; Col2=5} $array += New-Object psobject -Property $Props $Props = [ordered]@{Table="Table1"; Col1=3; Col2=7} $array += New-Object psobject -Property $Props $Props = [ordered]@{Table="Table2"; Col1=2; Col2=6} $array += New-Object psobject -Property $Props I want to […]

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.