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

Small Experiments in Data

This editorial was originally published on Mar 17, 2015. It is being republished as Steve is at SQL in the City - Austin.

Amsterdam is trying to become a smart city, with technology and investments being made in ways that will (hopefully) improve the city living experience for residents. There are a lot of items mentioned in the article, but one of the main items that I think underpins the entire experiment is data.

The piece notes that people and organizations can contribute to public data, which is then accessible by anyone that wants to build software or applications. The idea that groups can add to, and consume, data, is important. We need lots of data to better understand, and work with, our environment. We also need to ensure that the data is accessible widely otherwise it can be stifling to the innovations that people may dream about.

Amsterdam is participating in the CitySDK, a project that tries to standarize APIs for cities. That's important to avoid every environment having to reinvent their own APIs. This also means that software applications written by talented developers in one place might be easily ported to other locations. Ultimately, we might find that not only do our cell phones easily work in all locations, but specific applications that we have (perhaps for something like buses or grocers), might also easily port to other places.

I also think this provides a lot of opportunity for those of us working in software. I can see that there would be lots of organizations that have ideas for applications, but lack programming skills. There may be lots of opportunities in the future for developers to work with those that dream of new systems by bringing an idea to life. Even if you donated your time, you could polish your development skills, your communication skills with non-technical people, and dramatically improve the impression your resume makes with potential employers.

Steve Jones - SSC Editor

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

 
Redgate SQL Provision
 Featured Contents
Stairway to T-SQL DML

Stairway to T-SQL DML Level 4: The Mathematics of SQL: Part 1

Greg Larsen from SQLServerCentral.com

A relational database contains tables that relate to each other by key values. When querying data from these related tables you may choose to select data from a single table or many tables. If you select data from many tables, you normally join those tables together using specified join criteria. The concepts of selecting data from tables and joining tables together is all about managing and manipulating sets of data. In Level 4 of this Stairway we will explore the concepts of set theory and mathematical operators to join, merge, and return data from multiple SQL Server tables.

SQL Server Subquery in Select Statement with Where Clause

Additional Articles from SimpleTalk

In this part of the temporary data store tip series we look at using subqueries to select data as well as how to use for inserts, updates and deletes.

Monitoring your servers and databases with SCOM and SQL Monitor

Additional Articles from Redgate

SCOM is good at monitoring the status of your servers. SQL Monitor give you a detailed view of your SQL Server instances, and databases, right across your network, however they are hosted. By using tools appropriately, for the tasks they do well, you benefit from a simpler and more comprehensive overall strategy.

Free eBook: SQL Server Backup and Restore

Press Release from Redgate

In this free eBook Shawn McGehee offers advice on query tuning, cutting stored procedures, and system process design and implementation for high availability. Discover how to perform backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Redgate's SQL Backup tool.

From the SQL Server Central Blogs - Life behind a proxy server: downloading content for SSMS Help Viewer

spaghettidba from SQLServerCentral

Life behind a proxy server can be problematic. Not every software out there is tested correctly for proxy interaction and oftentimes the experience for the corporate user is a...

From the SQL Server Central Blogs - db_owner in msdb == sysadmin

Kenneth.Fisher from SQLServerCentral

This doesn’t require much in the way discussion. This isn’t exactly a huge issue since I don’t think granting db_owner ... Continue reading

 

 Question of the Day

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

 

Getting the Max

I've loaded a dataframe in R with some procedure execution times from SQL Server. The dataframe looks like this: 
> head(procs)
                ProcName cputime   reads
1           GetCustomers   27784  312165
2        GetCustomerList   43170  627272
3 CalculateMonthlyTotals   48868 2705414
4 CalculateMonthlyTotals   25868 2922859
5 CalculateMonthlyTotals   38206  862347
6           GetCustomers   49426 3449650
>
I now want to get the maximum values for cputime and reads, in order to decide which procs might need tuning. How can I do that?

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)

The Datatimeoffset Value

It's 5:00 in the afternoon in London during in May. We can represent this time as:

DECLARE @T DATETIMEOFFSET = '2019-05-15 17:00:00 +01:00';

I want to represent this exact same time in Colorado, seven time zones away from London in May. How would I show this time in Colorado in a datetimeoffset variable?

Answer: DECLARE @Colorado DATETIMEOFFSET = '2019-05-21 10:00:00 -06:00';

Explanation: The answer is '2019-05-21 10:00:00 -06:00'. This depends on your knowing a few things. First, in May, London is on Daylight Savings time and is UTC+1. This means that the time zone representation for 5pm in London is 4pm in UTC time. Colorado is 7 time zones from London, but that's 6 time zones from UTC, therefore, we use -06:00 as the offset. For the time, this would be 10:00am in Colorado. We use that time, with -6:00 as the offset to show this is the time in CO, 6 time zones removed from UTC. Ref: Datetimeoffset - https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql?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 - Development
Finding the starting ID in log table - Thanks if you can help. A logged process starts and then perform additional actions. Then the process starts again. I want to group the starting log message and the subsequent messages associated with it so that each message can tie back to it's 'Starting' message ID. The last select shows my expected results in column […]
What is the best way to test data type conversions? - Is there a way where I can use a debugger to see the value of a variable at each execution step? Is there a way to see how much space in memory the value is taking up? When would a person do an explicit conversion?  If you know that you will need to do an […]
SQL Server 2016 - Administration
Migrating from (mostly) 2K8 to 2016 - HI all, About to start this project and would be grateful for any good comprehensive articles rated highly by the good folks here at SSC TIA  
DBCC15 Result File - I had a database on a drive that failed. It got corrupted, but only 3 tables. It was moved to a new drive. Then due to choices that were not mine to make, it was decided not to restore from an untainted backup and to instead rebuild these three tables. I did this and everything […]
Endopoint stop alwayson during X time - Hello everyone , I just discovered that the databases in my Alwayson cluster are in NON SYNCHRONIZED status I tested Endpoint I found it in status stoped I launched Endpoint to start it again it has gone well and the endpoint has started well i would like to have now during the period where the […]
SQL Server 2016 - Development and T-SQL
Organizing and cleaning up AS400 DB2 data in SQL server - I've been dealing with data migration out of ancient AS400 piece by piece through linked server connection and I'm having hard time figuring out the best way to structure everything into more or less decent way.  I'm joining multiple tables into views to get a piece of data I'm looking for and then creating tables […]
Administration - SQL Server 2014
Unique Permissions for User - We have a financial application (Dynamics GP) with a SQL 2014 back-end.  Users of GP are in a security group that has write/edit permissions.  Ownership is asking me to give one user, who is a member of that group, read-only access to the SQL tables so he can connect via MS Access and ODBC.   SQL […]
High Availability - Backup Secondaries - Hi I have Enterprise 2014 with HA and also one local secondary and one remote secondary. I would like to backup the two secondaries locally so that if any of the three go down I would be able to restore from a full bak (& diffs & trn logs) But looking into it - it […]
Development - SQL Server 2014
DML performance : Unpartioned Table with Index vs Partitioned table with Index - Hi My take on Partitioning is, it facilitates large chunks of data into/out of a main table, limiting its downtime, due to locking.  Other than that, its of no specific use. It may also facilitate occasional DBA maintenance activities like compression etc.  But, its of little use in terms of day to day DML operations, […]
find duplicates that is not exactly duplicates - Hi All, Need a query to find the duplicates which is not exactly duplicate- Have a person table which got some duplicate names even though the Primary is distinct. The rows are as flows- PersonID      FName       Lname 1                      Tom   […]
Amazon AWS and other cloud vendors
increase size instance RDS - Hello everyone I would like to create an RDS instance I will create my instance with an initial size of 20G0 if tomorrow I will need to increase space dedicate will it be possible to thanks  
Integration Services
SSIS Job Stopped Working After Change to Office 365 on Server - We have a SSIS job that archives reports, by scraping the attachment from the email and saving it to the appropriate folder. I recently had to update my password, as part of our security policy. After I updated the password, I logged onto the server and updated the password in Task Scheduler. When I tried […]
SQLServerCentral.com Announcements
Status Update 17 May 2019 - It's been a week, and a hectic one away from SSC, so I've delayed posting until I could check on a few things. We have had full development staff this week, but we lose one today as we ramp down and catch up on things. Highlight of things fixed this week: resources migrated from old […]
SQL Server Agent
Unable to stop SQL Server Agent Job showing "In Progress" status - I am using SQL Server 2014 and I have a job (let's call it JN5) scheduled to run at a specific time on a daily basis. The job runs an SSIS package and has 11 steps in it. Looking at the Job History in SSMS, the job seems to be stuck at Step 9. It […]
SQL Job failed - Hello Team, I am getting the below error while running the new job.Help me to  resolve  this issue. Error Message : Starting copy... SQLState = 22001, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation. Using below bcp command when  i am trying to run the job : bcp PatsDB.dbo.Sharepoint in Sharepoint.txt […]
 

 

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

 

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