SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

A Strange View

Featured Script

Missing index script

The Voice of the DBA

Daily Coping Tip

Find positive stories in the news and share with others.

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

How Much AI Do We Need?

I like to cook and bake. The act of putting something together relaxes me, and I've certainly had plenty of time in 2020 to do this. In fact, I've been doing so much that my wife and daughter ordered me a new stand mixer while I was making them cookies this past weekend. They thought it would make it easier for me to continue to prepare more meals when life returns to some sort of normalcy.

I'm not a chef, and I certainly struggle through some recipes, but I enjoy the time working through the act of assembling something. I also struggle with fitting in the time to prepare things around an otherwise busy life. When I saw this article on smart appliances, I stopped to think that some of these might be nice, but perhaps they're also reduce some of the skills I've built and perhaps make my time in the kitchen less enjoyable. Would I become dependent on them, entitled, or even less excited about the task of cooking?

It's something I hadn't considered too often, especially in other areas where AI and "smart" items are becoming more commonplace. I think some of advances in the data platform are good, and certainly some of the cloud services handle tasks that are fairly simple and they do them well. Backups happening in many of the PaaS services are mostly something we don't need to configure or worry about, though we ought to be sure we know how to actually perform a restore. I have high hopes for some of the index automation and other changes, though there is a ways to go.

Is the use of more AI in systems better? In some sense I think this can help us cope with busier and more complex lives, and allow us time to focus on the things we want to focus on. At the same time, I suspect these systems aren't as mature as the marketing suggests, and the options certainly command a premium, which is something that might benefit the vendor more than the consumer.

Like any advance, this could be helpful or hurtful for any of us. I like to assemble things from scratch more often than not, and I'm not sure if I think the AI gadgetry is something I'd enjoy. For now, I'm taking a small step, with a little mechanical assistance that might let me less time on the physical activity of combining things and more time planning my next steps or chatting with the audience that is often sitting at the counter.

Steve Jones - SSC Editor

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

Featured Contents

A Data Science Approach in Product Analytics

siddharthray123 from SQLServerCentral

A significant part of product development is A/B testing. Simply put, this is where companies and product managers test to see a new version of their product versus the older one to make sure it’s worth publishing their features to the entire user base. In order to do this, an A/B test needs to be set […]

Getting Started with CQRS – Part 3

Additional Articles from SQLServerCentral

Diogo Souza completes his series on CQRS. He demonstrates Event Sourcing to capture the data as it flows through the system which can then be analyzed without affecting the source.

From the SQL Server Central Blogs - Has this login been used recently?

Kenneth.Fisher from SQLStudies

I get asked this every now and again, along with the companion When was the last time this login was ... Continue reading

From the SQL Server Central Blogs - Make Database Code Reusable in SQL Source Control with Deployment Filters

Chad Crawford from LittleKendra

When I first began working with databases, I was lucky to land a job at a little start-up which had solid development and operations processes: all our code, including...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	A Strange View
I have a table that is defined like this: CREATE TABLE [dbo].[OrderLineItem] ( [OrderLineItemKey] [int] NOT NULL, [OrderID] [int] NOT NULL, [OrderLinenumber] [smallint] NULL, [qty] [int] NULL, [unitcost] [money] NULL, [linetotal] AS ([qty]*[unitcost]) ) ON [PRIMARY] GO I want to create a view of just a few of these columns, and I type this: CREATE VIEW dbo.MyView AS SELECT OrderLineItemKey , OrderID , OrderLinenumber , qty , unitcost linetotal FROM dbo.OrderLineItem AS oli GO What happens with this view?
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)

RANKX Ties

What values can I use for the RANKX() "ties" parameter?

Answer: Dense and Skip

Explanation: The two values are Skip and Dense. This determines if the number ties are counted when determining subsequent values or if all ties are counted as a single value. Ref: RANKX() - https://docs.microsoft.com/en-us/dax/rankx-function-dax

Discuss this question and answer on the forums

Featured Script

Missing index script

dawaller from SQLServerCentral

This script on missing indexes that SQL had found. SQL will track queries and provide suggestions on indexes it feels will provide improvements. You should not take this information and create all the indexes. Performance tuning can be an art and takes practice. This information should be reviewed and as a DBA implemented a little […]

-- Missing Index Script
-- Original Author: David Waller
-- Date: 4/2020
SELECT db.[name] AS [DatabaseName]
,OBJECT_NAME(id.[object_id], db.[database_id]) AS [ObjectName]
,id.[statement] AS [FullyQualifiedObjectName]
,id.[equality_columns] AS [EqualityColumns]
,id.[inequality_columns] AS [InEqualityColumns]
,id.[included_columns] AS [IncludedColumns]
,gs.[unique_compiles] AS [UniqueCompiles]
,gs.[user_seeks] AS [UserSeeks]
,gs.[user_scans] AS [UserScans]
,gs.[last_user_seek] AS [LastUserSeekTime]
,gs.[last_user_scan] AS [LastUserScanTime]
,gs.[avg_total_user_cost] AS [AvgTotalUserCost] -- Average cost of the user queries that could be reduced by the index in the group.
,gs.[avg_user_impact] AS [AvgUserImpact] -- The value means that the query cost would on average drop by this percentage if this missing index group was implemented.
,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
,'CREATE INDEX [IX_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
WHEN id.[equality_columns] IS NOT NULL
AND id.[inequality_columns] IS NOT NULL
THEN '_'
ELSE ''
END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
WHEN id.[equality_columns] IS NOT NULL
AND id.[inequality_columns] IS NOT NULL
THEN ','
ELSE ''
END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK) ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK) ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK) ON db.[database_id] = id.[database_id]
WHERE db.[database_id] = DB_ID()
ORDER BY ObjectName, [IndexAdvantage] DESC
OPTION (RECOMPILE);

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

Replicatio DB issue - Hi Team , We have Prod publisher database and subscription database using the transaction replication .Last week one of prod application were completely slow due to bad query .So we recreate the query and tested in publisher , it was taking 14 sec meanwhile our developer has executed same query on Subscription database , […]

SQL Server 2017 - Development

Column total using group by rollup - I'm trying to total all the rows but one in a column using group by rollup in SQL Server. I'm getting the below which is correct for all the columns but I'd like to removed the RTF Total-224549.33. Is there a way to have a case statement in the group by rollup or what other […]

Integration service and OLE DB provider Microsoft.ace.oledb.16.0 is not register - Hi to all. I'm in trouble. I want to create an Integration Service project in Visual Studio 2017. I've already installed Microsoft Access Database Engine 2016 Redistributable x64. So when I add a new Excel source type with an OLEDB connector Visual Studio tells me "OLE DB provider Microsoft.ace.oledb.16.0 is not registered". I tried to […]

How create a report based on column value change - I have been asked to create a report based on when a column gets updated with the row and date it was updated. what is the best way to create the report. Table has about 3 million rows and the column report is created on is INT. Thanks in advance.

SQL Server 2016 - Administration

TempDB full - Hello, When the temp db is full ( due to a temp tbl) my learning has been you can drop the temp tbl by going to the tempdb - temp tbls - right click - dropNow, this was done to resolve the issue by dropping the Temp table. I was wondering if dropping the temp […]

SQL Server 2016 - Development and T-SQL

Question regarding decimal field - Dear All I have a question regarding decimal field, i have set field to DECIMAL (18,2) and it looks like this, is there a way to separate thousands with dot(.), so that number looks like 9.090,00 in column. It is a pretty big table and when i got the results after SUM i get unclear […]

Administration - SQL Server 2014

Tracking Connections To a Database - I'm trying to come up with a way to track the history of connections made to one database without setting up a trace or having a job run every minute. So far I've been focusing on the sys.dm_exec_sessions and sys.dm_exec_connections DMVs and each offers some of what I'm looking for (ID, host name, time) but […]

SQL Server 2019 - Administration

How will one setup a SQL FCI on WSFC with Windows Core ? - Multi Subnet WSFC of 4 Nodes 2 Nodes in primary DC and other 2 in DR Center. Idea is to set up failover cluster instance here in Primary DC and another at DR Center . and Setup an AOAG in async mode. Please criticize it if there are any gotchas. Please guide me here. Even […]

rows Issu - Hello Team, I have 2 Tables A and Tables B.In Table A i have 3 rows(1,2,3) and Table B i have 3 rows(4,5,6).I need to get all the rows but i need to have single cloumn.Please advise Thanks in advance RK

How do I use cascade delete with SQL Server? - I have 2 tables: T1 and T2, they are existing tables with data. We have a one to many relationship between T1 and T2. How do I alter the table definitions to perform cascading delete in SQL Server when a record from T1 is deleted, all associated records in T2 also deleted. The foreign constraint […]

SQL Server 2019 - Development

sum of sales 2 dates - if object_id( 'tempdb..#anchorj') is not null begin drop table #anchorj end create table #anchorj ( ID varchar(10),anchor sales date date) User ID Sales Date S12 3/12/2015 S13 4/5/2017 S14 2/15/2016 S15 1/1/2017 S16 5/4/2014 S17 3/24/2017 S18 2/6/2015 S19 4/18/2017 S20 6/2/2015 S21 7/5/2016 S22 8/2/2015 S23 8/2/2018 Select Distinct User ID, sum ( sales_amt) […]

Sync data between Two databases - Hi All, This is sampath i am new to sqlservercentral community,i am begginer in sql related queries please help me to achieve. I have two databases in same server assume Realtime database and Backup database these two databases contains 25+ tables. i have done insert/delete/update in Realtime database automatically Backup database should be updated. Is […]

SSRS 2016

Font renders correctly online, but not when exported to PDF or Excel - Hello, I'm having a problem with Wingdings/Wingdings 2 in SSRS reports exported to PDF or Excel (haven't tried other export formats). In my report I set the cell value and FontFamily from the dataset, i.e.; Value FontFamily * Wingdings P Wingdings 2 and it renders correctly online (*/Wingdings = envelope, P/Wingdings 2 = check mark); […]

Analysis Services

The 'Tabular View' feature is not included in the '64 Bit Standard' edition of - Hi , I am getting the error below when i want to process the cube.Please help me if anyone came across this before. "Errors related to feature availability and configuration: The 'Tabular View' feature is not included in the '64 Bit Standard' edition of Analysis Services" I am running sql 2014 standard and visual studio […]

COVID-19 Pandemic

Daily Coping 16 Apr 2020 - Today's tip is to put your worries into perspective and try to let them go. No easy to do, but here are my thoughts: http://voiceofthedba.com/2020/04/16/daily-coping-16-apr-2020/

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.