Hot answers tagged

21 votes
Accepted

What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?

Since you're just looking for changes, you don't need a cryptographic hash function. You could choose from one of the faster non-cryptographic hashes in the open-source Data.HashFunction library by ...
user avatar
  • 71k
17 votes

What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?

I'm not sure if parallelism will be any / significantly better with SQLCLR. However, it is really easy to test since there is a hash function in the Free version of the SQL# SQLCLR library (which I ...
user avatar
12 votes

What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?

This isn't a traditional answer, but I thought it would be helpful to post benchmarks of some of the techniques mentioned so far. I'm testing on a 96 core server with SQL Server 2017 CU9. Many ...
9 votes

Dimensional Modeling and ETL in Redshift

You are definately on the right track with Kimball rather than inmon for Redshift. There are a number of patterns for this, I have used them all in different use cases "ELT" pattern - Load the ...
user avatar
  • 219
9 votes

What is the best way to export/extract varbinary images in a SQL Server database to flat file via t sql, anything better than sp_OA methods?

If you really must do it from T-SQL, and your SQL Server instance version is 2012 or more recent, You could use a FileTable. It relies on the FileStream feature, so you'll have to enable this first. ...
user avatar
7 votes
Accepted

Are there practical scenarios where inserts + deletes are faster than updates? (SQL Server)

There are many reasons that an insert and delete can be faster in practice than an single update that achieves the same end result. I am not even going to attempt to list all the considerations, but ...
user avatar
  • 71k
7 votes

data warehouse automation tool: benefit or bane?

I'll weigh in from the user perspective. I have worked extensively (15 or so projects) with one of the automation tools on a SQL Server backend, and results were mixed. Did the DWA tool greatly ...
user avatar
  • 15.4k
7 votes

What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?

You can probably improve the performance, and perhaps the scalability of all the .NET approaches by pooling and caching any objects created in the function call. EG for Paul White's code above: ...
user avatar
6 votes
Accepted

How to compare data between staging and operational tables?

There are a couple of options if you want to use T-SQL and SSIS. You could compare the key columns on the staging table vs ops table to determine if the row already exists and this way you would ...
user avatar
  • 76
6 votes
Accepted

Azure Data warehouse - User defined function issues

Functions in Azure DW don't support select statements that access tables like in your use case, see CREATE FUNCTION (SQL Data Warehouse): function_body Specifies that a series of Transact-SQL ...
user avatar
6 votes

Azure Data warehouse - User defined function issues

Azure SQL Data Warehouse has limited support for UDFs. It does not yet support the syntax SELECT @var =. Instead you must use DECLARE @var int = or SET @var =. SQL DW UDFs also do not yet support ...
user avatar
6 votes
Accepted

Linked server returned message "Query timeout expired."

This doesn't answer your question with regards to why the query is slow on the 11th day, but hopefully it helps clarify why it fails after 10 minutes. You mention: The remote timeout on the linked ...
user avatar
  • 27.2k
5 votes
Accepted

Introduce or not introduce SSIS to an ETL project

The main strengths of SSIS as I see it are 1) the ability to do things across servers which aren't otherwise linked (eg via linked servers) and 2) the ability to do things in parallel (eg multiple ...
user avatar
  • 10.2k
5 votes
Accepted

SSIS Multicast performance question

SSIS gets its power by being an in-memory transformation engine. The base unit of work within a data flow task is the buffer. If you ever wonder why SSIS is so persnickety about data types, it's ...
user avatar
  • 15.5k
5 votes
Accepted

How do you index alphanumeric foreign keys on large fact tables?

Assuming that your assumption is correct regarding the VARCHAR(50) field using a collation of SQL_Latin1_General_CP1_CI_AS, then you should consider altering those alphanumeric "code" fields in each ...
user avatar
5 votes

data warehouse automation tool: benefit or bane?

Eckerson's list is poorly researched. There is a more comprehensive catalogue of data warehouse automation tools on our web site, at http://ajilius.com/competitors. I'll answer your questions from a ...
user avatar
  • 151
5 votes
Accepted

SQL Server - What backup/restore strategy should I use to accomplish the following?

Given the following info: Our support engineers need to grab database backups from clients. and: The most "valuable" data is "the configuration" of our system. It is contained in about 50 ...
user avatar
5 votes
Accepted

Can you use ODBC to extract data directly from SAP HANA S/4 1610?

Your question is a bit confusing so I hope I'm deciphering it correctly. Technically you can connect to a SAP HANA database using ODBC -- this is helpfully documented in the manual. What you do ...
user avatar
  • 21.6k
5 votes
Accepted

Character Conversion Issue Importing From CSV File

there are various words that are "written" to the database incorrectly. No, the characters are being read incorrectly. They are being written correctly. Or, another way to look at this is: the ...
user avatar
5 votes

SQL Staging Tables: Primary Key Clustered or Heap

Having an identity column doesn’t force you to use it as a clustered index key. You’re right that heaps work well here. I would consider Thomas Kejser to be the authority on the subject, and it’s ...
user avatar
4 votes

How can I load csv data into mongodb and choose the types I'd like to have created for each column?

Ensure you're using mongoimport version 3.4 or higher, and use the --columnsHaveTypes parameter to tell the import what type each column is. See the MongoDB Reference Documentation for details. The ...
user avatar
  • 67.2k
4 votes

is automated data movement via Excel considered ETL?

The process you describe is very extremely techinically ETL, though if I were reviewing qualifications with a candidate during an interview for a position that required what I usually think of as ETL ...
user avatar
4 votes

SQL server Database statistics degrading after execution

Starting at the beginning: SQL Server uses statistics objects to generate cardinality estimates for the result set steps during the optimization phase. Cardinality estimates are used to generate a ...
user avatar
  • 10.4k
4 votes
Accepted

What is the best way to import this non-standard text file into SQL Server?

Based on your sample data it looks like there are 6 columns in total. COL1, COL2, COL4, COL5, COL6 = All fixed width . COL3 = This is a fixed width for a given day, but it is variable day to day (...
user avatar
  • 336
4 votes

PostgreSQL importing thousands of columns as an array

You don't need any auxiliary tools. This works on any platform. COPY to a simple temporary ancillary table: CREATE TEMP TABLE tmp(txt text); COPY tmp FROM '/path/to/your_file.csv' (FORMAT csv, ...
user avatar
4 votes

SQL Server Data Tools for VS 2015 - Anyone using for SSIS development to multiple versions?

Visual studio 2015 SSDT work with SQL Server 2016 and has a backward compatibility to SQL server 2014 and 2012 only. Target version of SQL Server | Development environment for SSIS packages 2016 ...
user avatar
  • 1,212
4 votes

Huge Size (still) with Differential Backup

That sounds perfectly expected. If you are updating most of your data and rebuilding all of your indexes: all the affected extents need to be in the differential backup, there is simply no way around ...
user avatar
4 votes

Is it impossible to force MariaDB users to SSL?

REQUIRE SSL grant option on users is what you need. It works for me as intended: MariaDB [(none)]> CREATE USER testssl@localhost; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> GRANT ...
user avatar
  • 14k
3 votes
Accepted

Clustered Index with nightly ETL process

There are few ways to work around this problem: As you mention, partitioning will help. If you partition on the leading column in the index and make sure all partitions are equally sized, you can ...
user avatar
3 votes

What is a good solution for reliably transferring data between SQL Server and PostgreSQL?

Pentaho have an open-source tool called Kettle that we use for ETL development with Postgres. It is quite good and very comprehensive. http://community.pentaho.com/projects/data-integration/
user avatar
  • 280

Only top scored, non community-wiki answers of a minimum length are eligible