data-konzept

'While in an ideal world each column in a database table has a suitably chosen data type, in this non-ideal place we live in, having stored dates in a wrong format, is a problem that the majority of those who wrote SQL has faced. This is a common consequence when different applications use the same data in different ways than the one you originally defined.

Handling columns with wrong data types can become cumbersome easily, as it does not allow you to perform comparisons or logical operations the way you may wish. So the need of casting columns into different type than the one currently defined arises.

Luckily for us, the majority of the top used databases have implemented helpful functions that one can use to perform this type of tasks easily. Unfortunately, these functions we are talking about, are not exactly the same across all database systems.

Having created a neat cheatsheet of the majority of the cases one may face, can save a lot of time and effort from searching in long documentation pages.

In this post, we are going to present the more common castings one may wish to perform along with the SQL code to do so, when using Amazon Redshift, PostgreSQL, Google BigQuery or SQL Server.'...

https://www.blendo.co/blog/how-to-sql-type-casting/

'The script first creates the “RunningTotals” database. Notice that you first need to create the “C:\RunningTotals” folder, because this is where SQL Server will try to store the database files. Alternatively, you can modify the CREATE DATABASE statement in the script to store the files in a different folder. Then the script creates the “Billing.Transactions” table, and populates it with test data. The table includes the “Balance” column, which is set to NULL before each method is being used. The goal is to populate the “Balance” column with the balance in each account after each transaction.

Here are the methods that I demonstrate in the script in descending order of execution time:

 1. Cursor with an UPDATE statement in each iteration

 2. Cursor with a temporary table and an INSERT statement in each iteration

 3. Correlated sub-query

 4. Window Function – SUM () OVER…

 5. In-place UPDATE with ORDER BY

 6. In-place UPDATE with an index hint'...

http://www.madeiradata.com/calculate-running-totals-sql-server/?utm_source=DBW&utm_medium=pubemail

'...

http://www.sqlservercentral.com/scripts/Security/164696/?utm_source=SSC&utm_medium=pubemail

'We are excited to announce that SQL Operations Studio is now available in preview. SQL Operations Studio is a free, light-weight tool for modern database development and operations for SQL Server on Windows, Linux and Docker, Azure SQL Database and Azure SQL Data Warehouse on Windows, Mac or Linux machines.

Download SQL Operations Studio to get started.

It’s easy to connect to Microsoft SQL Server with SQL Operations Studio and perform routine database operations—overall lowering the learning curve for non-professional database administrators who have responsibility for maintaining their organization’s SQL-based data assets....

https://blogs.technet.microsoft.com/dataplatforminsider/2017/11/15/announcing-sql-operations-studio-for-preview/?MC=SQL&MC=DataMgmt&MC=MSAzure

'Accelerate your transition to the cloud

• Use a simple, self-guided migration process

• Get comprehensive assessments detailing pre-migration steps

• Migrate at scale from multiple sources to your target database'...

https://azure.microsoft.com/en-us/services/database-migration/?MC=MSAzure&MC=OfficeO365&MC=SecSys&MC=Virtual&MC=DataMgmt

'SQL Server 2017 brings us some new T-SQL functions. They are very simple to use, and can also help us to simplify our T-SQL code. I’ll be talking about them in this article.'...

https://www.red-gate.com/simple-talk/blogs/new-t-sql-functions-sql-server-2017/

'Let’s face it: column enlargement is a very sensitive topic. I get thousands of emails every month on this particular topic, although most of them end up in my spam folder. Go figure…

The inconvenient truth is that enlarging a fixed size column is a long and painful operation, that will make you wish there was a magic lotion or pill to use on your column to enlarge it on the spot.

Unfortunately, there is no such magic pill, but turns out you can use some SQL Server features to make the column enlargement less painful for your users.

First, let’s create a table with one smallint column, that we will try to enlarge later.'...

http://www.sqlservercentral.com/blogs/spaghettidba/2017/11/15/how-to-enlarge-your-columns-with-no-downtime/

'If you've ever received the message "login failed for 'nt authority\anonymous logon' while working with SQL you know how frustrating it can be. I'm going to throw a troubleshooting tip that doesn't get much attention into the bag of tricks you might want to try.'...

http://www.sqlservercentral.com/blogs/don_halloran/2017/11/13/login-failed-for-nt-authority-anonymous-logon-the-fix-you-probably-haven-t-tried-yet/

'One of the cool new features in SQL Server 2017 (and currently also in public preview in Azure SQL Database) is the option to pause and resume an online index rebuild operation.

This option can be useful for several use cases. Imagine you have a very large index, which takes approximately 5 hours to rebuild online. A lot of things can happen during those 5 hours.

For a start, you might not have enough time within your maintenance window to complete the operation. Now you can configure the operation to run for 3 hours and then pause automatically. You can then resume the operation during the next maintenance window. Of course, this means that SQL Server will have to maintain both old and new indexes until the new index is completely ready. But depending on your workload and policy, this might be a better option than to continue to rebuild the index outside of the maintenance window.'...

http://www.sqlservercentral.com/blogs/guy-glantser/2017/11/16/can-i-pause-or-resume-an-index-rebuild-operation/?utm_source=DBW&utm_medium=pubemail

'The query execution plan is a map of work which SQL Server is going to perform to execute a query. It provides a lot of useful information like operators, cost, estimated rows, actual rows, predicate details, parallelism, and much more information to troubleshoot the performance issue.

During the troubleshooting of the performance issues, I would strongly request you to check one of the vital information which is “Parameter Complied Value”. There are two terms which we need to get familiar;

1) Input Parameter

The real power of stored procedures (SP) comes when it can handle the differing requests. It means it should allow you to pass different-different value so that it works with differing requests. The parameter value you supply to run a stored procedure called “Input Parameter”.

2) Parameter Compiled Value

When we initially execute a query, the values that we supply for that first execution; will be used by SQL Server to create and compile the execution plan. That value is called “Parameter Complied Value”.

If you analyze the reasons for the compilation of execution plan, you will find one of the usual reasons – SQL Server generates one plan with one set of input values, and then a different plan with a different set of input values because the query is a parameter sensitive query. This is the reason we hear from the end user that a query sometimes it executes quickly, and other times it’s slow. To understand the issue more, you need the parameter complied value. Let’s explore the different-different way to get the parameter compiled value.'...

http://www.sqlservercentral.com/blogs/sql-geek/2017/11/11/finding-input-parameters-in-a-execution-plan/