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

Experiment First

Not too long ago, I had a sales engineer contact me about a script that a customer needed. It was similar to others we have for various Redgate tools, but also different enough that the customer needed help. In fact, I also needed some help.

I asked a question on Twitter, mostly because I was busy and wanted to know if someone had solved the (partial) problem that I had. I got a number of responses, none of which were direct solutions, but many of them contained some hints. At the time, I filed these away because I didn't have time to work on anything, but I knew that asking the question gave me a few starting points.

A day later, I started to experiment, but ran into a few issues. The PoSh script wasn't doing what I expected, which is always frustrating. I almost started to ask questions, but then I decided to work through the process. After all, I'd already interrupted friends on Twitter with my original question, and I ought to experiment a bit before I asked others to do my work for me.

This was a script designed to find the latest files and build a restore. My first problem was that no data came back from the backup set. I double-checked my data, but didn't see anything. I then set a variable for the parameter and another to get results. I started to get some information, which led me to review my work again. I realized I'd mis-typed something as a direct parameter, but had corrected it in the variable assignment. First lesson, really proofread carefully.

The data I first got back didn't quite make sense, so ran this through Get-Member. I didn't find any property that looks like what I wanted, at least not intuitively. Rather than ask or post a question, I started just checking properties. Eventually I realized the data was there, but not under the name I'd have used or expected. Sometimes just digging through the properties and methods is the best way to learn.

Lastly, with a bunch of data in my object, I needed the top 1 item. Stack Overflow helped point me in the right direction, and I solved my own issue by reading a few different things people had done. With a little research and experimentation, started off by a few questions of others, I had a script that I could give to a customer.

While our management might expect us to know everything about our jobs, often they know this isn't the case. Technology and platforms change quickly and expand widely to the point that few of us know everything about anything. What management and others really expect is that we can learn to solve our own problems, with some help from others, but also some ability to learn and experiment.

At SQLServerCentral, we have great forums for SQL Server questions. Stack Overflow is a great spot for lots of general technology queries. Plenty of people are willing to help, but often with some sketch of a solution, expecting you to do some work to mock a scenario and then take answer and adapt them to your environment.

There are lots of people in the world willing to help you, but they don't want to do your job. Ask questions when you don't know, but also plan to spend your own time experimenting and learning about how and why an answer might work. Before you post a followup question, be sure you experiment and see what you can teach yourself. You will likely teach yourself something and maybe next time you'll be the one answering a question for someone else.

Steve Jones - SSC Editor

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

Redgate University
 
  Featured Contents
Stairway to Exploring Database Metadata

Exploring Facts About SQL Server Tables: Stairway to Exploring Database Metadata Level 5

Phil Factor from SQLServerCentral.com

Now that we've explored, in preceding levels, some of the information that is available about indexes, triggers, keys and distribution statistics, we can concentrate on the tables themselves and their columns.

Find SQL Server Stored Procedure Create, Modified, Last Execution Date and Code

Additional Articles from MSSQLTips.com

In this tip we look at different scripts you can use to find when a SQL Server stored procedure was created, modified, last executed and to also return the code for the stored procedure.

Free eBook: Inside the SQL Server Query Optimizer

Press Release from SQLServerCentral.com

This free eBook from Redgate Software will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way.

From the SQL Server Central Blogs - Azure Fundamentals 12th December

GRE (Gethyn Ellis) from The SQL DBA in the UK

Certified Microsoft Azure Fundamentals  - AZ-900  1 day Web based Course - 12th December 2019
Its been a big few of weeks here at gethynellis.com. We've been accepted onto the UK...

From the SQL Server Central Blogs - Upcoming SQLpassion Online Trainings

Klaus Aschenbrenner from Klaus Aschenbrenner

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance...

 

  Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Creating a Skeleton Data Frame

I want to create a data frame for scheduling SQL Saturday sessions with just column names. My idea is to set these columns: SessionTitle, SessionTime, SpeakerName, RoomAssignment. I plan to put this in the variable MySchedule. Which code will create 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)

More Computed Column Indexes

I have a table with a column defined like this:

SalePrice FLOAT

I then created a computed column on this field:

ALTER TABLE dbo.MonthlySales ADD EstSalePrice AS ROUND(SalePrice, 0)

I want to index this column with the following code.

CREATE INDEX MonthlySales_EstSalePrice ON dbo.MonthlySales (EstSalePrice)

What happens when I run this?

Answer: An error is returned because you cannot index a float field used in a computed column

Explanation: You can create the computed column, but the float datatype cannot be used in a computed colunn in any way if there is an index. Ref: Indexes on Computed Columns - https://docs.microsoft.com/en-us/sql/relational-databases/indexes/indexes-on-computed-columns?view=sql-server-2017

Discuss this question and answer on the forums

 

 

 

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
2012 - 17 Performance Issue -   Good afternoon everyone, This may sound familiar, but we are yet to find a solution. We are in the middle of a platform upgrade from 2012 - 2017 utilising some vastly superior hardware. However we have found that performance has degraded, rather than the expected improvement on the tests so far. For example, when […]
SQL Server 2017 - Development
Datetime data migration. - Hi All, We are loading data from one of our source table in that table createddate column datatype is char(22)  and value is 2012-06-03 04:11:49:16. And in my target table the column datatype is datetime and we are using convert datetime function to convert char to datetime. But the date is converting to 2012-06-03 04:11:49:016. […]
Always Encrypted with SQL Server Data Project - Hi, We have a small database (just 3 tables) where we wish to use Always Encrypted to encrypt the data in a few of the fields in each table.  I've managed to successfully encrypt the data using the SSMS wizard and powershell scripts,  and I can read/write from our application. I'm now looking for some […]
SQL Server 2016 - Administration
Change SQL Agent service account and password on 2 node active-active cluster - What are the steps to change SQL Server agent service account and password on 2 node active -active. Thanks in advance.
SQL Server 2016 - Development and T-SQL
insert into permanent table using cte - Good morning all I would like to add the result of a multiple query in a table the goal is to have this formathere is my request ;with cte as ( blablablab ) INSERT INTO newtable select * cte here is my request with echantillontemp as ( select Ref_Contrat = v.Contract_ID + '-' + format('0'+ […]
CHOOSE function returns NULL when it shouldn't - Greetings, We are using the T-SQL CHOOSE function for a test fixture.  The idea is that we want to randomly select one of 3 different values and have the CHOOSE function do it's thing and pick the correct value out of the array we pass in. However, sometimes it returns NULL and we can't figure […]
Administration - SQL Server 2014
assist needed for the SQL Server dump - Hi Guys, I got 14 dumps in last two days, I tried to use WinDbg to check what is going, but I don't get a clear information from the output, can anyone help pinpoint the cause? I know it is very likely failed at sqlmin!CChangeTrackingDatabase::CleanupNextTable+711, but not sure is it failed at specific database, or […]
Development - SQL Server 2014
A bit of mathematics... - Hi all, I'm just doing some work on my own bank statements imported from CSV.   Two salient columns, MoneyIn (int), MoneyOut (Int). This is the formula I'm trying to calculate: ((Revenue - Outgoings) * .81) - Dividends Paid = Result I've been able to Select (Select Sum ([Money In]) As 'Revenue' from SmytheData.dbo.Transactions3) - […]
Need Help: On Query with Case Statement - Hi, As part of development task I have to write a SQL logic for the below 6 scenarios, I have achieved desired result in my query using Case Statement but I want to know is there any other options(ways) where I can write query without using Case Statements to achieve the same result using sub-query […]
SQL Server 2012 - T-SQL
Question on triggers - Hi, I have, as most triggers are, a single row trigger; that is it will only update one row, and that works fine. However, someone now runs a SQL Query that updates multiple rows at once, and I have not been able to see how to get this to do both. Here is the trigger […]
transactions(Try, commit and rollback) - Ignore post
Reporting Services
Pull dynamic reportpath of RDL into SQL inside the RDL. - Hello, Long story of not best practice that I have inherited and am slowly trying to change. (No judgement please). I have a set of approx. 1000 report server reports that have SQL embedded in them (Insert sad face). Some of the reports are locking each other and sometimes taking down databases. My idea is […]
SSRS Range chat display hours not working - Hi, I created a dataset with columns: Title varchar,startdate(datetime),endDate(datetime),diference(hours),startpoint(dif in hours int),endpoint(dif in hour int).. the range of my data(min of startdate and max(enddate)) is lower than 1 day. So i calculated dif in hours..but char takes hours like days and sum them.. pej(calculated in hours): start date 2019-10-04 13:00 end date 2019-10-04 14:00 startpoint […]
SSDT
Value of extended property in T4 script returns a quoted, escaped string? -   Hi, I have a T4 script which reads extended properties. The value of these properties seems to be escaped. Example: I have a table person with an extended property called propertyName that has the value propertyValue. When I read this value I get: N'propertyValue'. The value that appears in the T4 script is the escaped value. […]
Integration Services
how to check if file contains only column headers without data - Hi All, i have a task to check file (excel & text) before loading to a table. checking condition: if file only contains header(columns) without any data then fail the file. I have filename & source file path variables already - any idea how to handle this in script task? much appreciate!  
 

 

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

 

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