Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
Featured Script
Redgate Data Masker
The Voice of the DBA
 

Waking Up to No Support

When you wake up on July 10, SQL Server 2008 and 2008 R2 will be out of support. Microsoft has been talking about this for some time, there's been a Migration Tour from PASS with live events in quite a few cities, webinars, and more to let you know that you might want to upgrade to a supported version.

While I typically think that systems that run are fine and don't necessarily need support, there are compliance and security concerns here. If you work in a regulated industry, you might need support to stay compliant. If that's the case, you might want to ensure you have plans to move to SQL Server 2017 (or 2019) soon.

The same goes in this era of attacks and potential security issues. While SQL Server has relatively few patches for security, you never know. I haven't worried about this in the past, often with internal servers, but I do get more concerned over time as we seem to constantly find more complex bugs in hardware and software.

SQL Server 2008 support is over, but you have options. Microsoft will continue to support you if you move your servers to Azure. That's not a bad option if you need to move quickly, but don't forget to really think through what this means for your applications. Moving to Azure isn't necessarily as smooth or simple as you might think. Authentication and authorization need some attention to detail to get working well. It's not hard, but it is work.

When SQL Server 2005 went out of support, it didn't seem there was as much emphasis on migrating. Certainly lots of people ran SQL Server 2000 instances for many years without support. You can do it, but the world feels more dangerous, and there are more implications with regard to regulation, security, insurance, and other complex topics that can impact your business.

Think carefully about the implications of migrating and not migrating. You'll have to make the decision, and it can be hard, but it's something you should spend quality time thinking carefully about.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

Redgate SQL Source Control
 
  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

More »

 

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 […]
 

 

RSS FeedTwitter

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.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -