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

Do You Deal with UTF8?

Microsoft is adding UTF-8 support in Azure SQL Database, and it will be coming in SQL Server 2019. If you don't know what this is, perhaps you want to read a bit about it, as it can save space if you have the need to use Unicode characters. This format uses a variable number of bytes to encode characters, and this is often used on the web and email. My question today is:

Are you looking to store data in UTF-8?

The way this works with SQL Server can be complex. In fact, not everyone thinks this is really done well, as there are some bugs in the initial versions. As I've watched some people try to work with this, it is a very confusing and complex topic. I thought this might be a simple "SQL Server handles everything" collation, but it doesn't appear that this always works. Calculating space needed for data isn't as simple as I might expect. Not having to prefix strings with N is nice, but I'm not sure that this will actually work in practice.

I've seen some discussions of how this works, and it's complicated. In fact, it's not easy to tell how much storage you might need for characters. The storage differences can be confusing, depending on the code range you work with. Since most of us know that our users will try to add data we would never expect to our database, we might run into issues with not enough space. For those of us specifying space for our columns, we now need to know bytes in use, not characters.

Likely this is easy for those of us that work in the English world and stick with varchar, but maybe not. I'm curious today how many of you will attempt to work with UTF-8 (or are waiting for it). It would also be good to know about any challenges or issues you've had working with the encoding in other systems or languages.

Steve Jones - SSC Editor

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

Redgate University
 
 Featured Contents

Loading partitioned table incrementally using SSIS

shubhankarthatte from SQLServerCentral.com

This article gives an example of loading partitioned tables incrementally using SSIS

Data Analysis of SQL Server Tables using T-SQL for Machine Learning Exploration

Additional Articles from MSSQLTips.com

In this tip we look at a script that you can use to get better insight on your database tables to help you make some decisions about the data and also the structure of the table.

What should you expect from a third-party monitoring tool?

Additional Articles from Redgate

Over 800 SQL Server professionals took part in the 2019 State of SQL Server Monitoring survey. Travis Hoffacker takes a look at the report and advises what is now expected of a third-party monitoring tool based on the responses from the community.

From the SQL Server Central Blogs - Adding SQL Change Automation to Jenkins for Database CI

Steve Jones - SSC Editor from The Voice of the DBA

In a previous post, I explained how to get Jenkins running in a container for your local CI work. In this one, I’ll expand on the process for database...

From the SQL Server Central Blogs - Actual Kubernetes

Steve Jones - SSC Editor from The Voice of the DBA

This post continues looking at my process of learning more about Kubernetes. I’ve been working through the 50 days of Kubernetes (K8s). I completed the first 3 sections (Days...

 

 Question of the Day

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

 

Dropping Synonyms

I run this code: 
CREATE TABLE #songs
(   SongKey     INT
  , SongTitle   VARCHAR(500)
  , ReleaseDate DATE);
GO

CREATE SYNONYM StagingSongs
FOR #songs;
GO
If I drop the temp table, what happens to the synonym?

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)

Filtering the Audit

I have created a Database Audit Specification for tracking activity in my database. I choose the SELECT Audit Action type. Which of the following are ways that I can filter the Object Class to which the audit action applies to?

Answer: Database, Schema, or Object

Explanation: The object class that I can apply this action to is Database, Schema, or Object. Ref: SQL Server Audit Action Groups and Actions - https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?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
SQL Login Issues between replicated VM's - The issue is that this server (BFS-DEV02) in a virtual clone of another server (BFS-DEV01). Within SQL Server, windows login BFS-DEV02\Administrator is being viewed by SQL Server AS BFS-DEV01\Administrator. Here are some screen shots shows what I mean about the logins…I believe what we ultimately want is to remove BFS-DEV01\Administrator and make it BFS-DEV02\Administrator.   […]
Always on - Planing to go with SQL Server 2017/2019 for Biztalk latest version of 2019/2020. So i would like to go with Always as DR option. Do you guys have seen any issues setting up always on for Biztalk DB server ? If yes, what are the issues have you seen for setting up always on with […]
SQL Server 2016 - Administration
Can't drop database in "restoring" state from secondary instance of AG - MSSQL version: 2016 (13.0.5026.0) Situation: Removed a database from an availability group so it could be restored. When I'm ready to add it back to the group, I go to drop the copy on the secondary instance. It's in the "restoring" state, but that's ok, that's what's expected. But when I try to drop the […]
Extended events - I'm trying to teach myself ExtendedEvents (trying to move from profiler). I've attached a configuration and the output. I've got 'statement' selected in event fields and 'sql text' selected in global fields but the output doesn't show the sql statement that has been run and I'm not sure why?  
AOAG Secondary Checkdb - I'm looking into setting up a AOAG with a DR server which will have the option as NO for readable secondary. Under my understanding will mean I will not have to license the DR box. As best practise (Brent Ozar advice) indicates CHECKDB should be preformed all on nodes as they reside on different disks. […]
SQL Server 2016 Archive Strategy advice. - Greetings. I’m after some advice. I’m helping a colleague with a SQL Server 2016 database that is getting quite large and they are considering an archive strategy. The database essentially stores data from a number of sensors on machines that are building stuff. Readings are taken every 1, 5 and 10 seconds from a variety […]
Multiple tables created in CDC - Hello Team, I have enabled CDC for a particular database in SqlServer and enabled it for multiple tables. I see duplicate tables have been created for each of the table that I have enabled CDC. Is it by design or have I done anything wrong in configuration. Thanks
2 core machine with 4 Tempdb files - Just curious if there is a performance hit by having 4 tempdb files with a 2 core machine? running SQL 2016 (SP2 CU7 +GDR)  
User datareader access problem - I went to give a user (via AD account) datareader access to a number of databases. Everything seemed alright, her read permissions show up in server level security, and in each of the assigned databases. However she still cannot view them, getting a 'not accessible' message. If I try viewing tables in these databases 'execute […]
SQL Server 2016 - Development and T-SQL
sp_executesql with multiple parameters - I'm trying to write a stored procedure that will select the entire table if all parameters are null, otherwise select the table filtered by the non-null parameters using an AND condition. Here is some skeleton code: DECLARE @ProjectID VARCHAR(100) = 300; DECLARE @ProjectName VARCHAR(100) = 'Some project name'; DECLARE @sql NVARCHAR(4000) = '' SET @sql […]
Administration - SQL Server 2014
Upgrade to SQL Server 2008 R2 SP2 from SQL Server 2005 SP3 - Hello, I noticed that SQL Server 2005 SP3 is not in the in the supported version to be upgraded to SQL Server 2008 R2 as shown in the link below. Please let me know if there any issues with in place upgrade.   thanks iosman
Data source error - Security protocol not supported - Hi Experts, I have a report that fetches data from a cube .I have an issue with a data source which is returning an error "Security protocol not supported".This is only happening in SSRS reporting server  after deployment but in visual studio the report is working perfect.I have been working on this without no success. […]
Development - SQL Server 2014
How to copy rows from the same table and update the ID column? - I appreciate it is utterly trivial question, and yet... When you have a small amount of columns, there is a simple solution, sort of INSERT INTO myTable SELECT MAX(table_id) + 1, column2, column3 FROM myTable WHERE table_id IN (SELECT list of table id's to be replicated); alas I have circa 200 columns, so I don't want […]
Reporting Services
ReportServer Database upgrade fails - I've tried several times to successfully  migrate a SQL Server 2008R2 ReportServer and ReportServerTempDB to SQL Server 2017.  Every time it fails and one of the log files says that the database upgrade failed. I install SSRS 2017 but don't configure.  I backup the 2008R2 SSRS databases and the encryption key.  This database is joined […]
SQLServerCentral.com Website Issues
Website search - Does SQLServerCentral have any advanced search features, or can anyone share any tips?  Does everyone just use google with site:sqlservercentral.com?
 

 

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

 

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