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 ...
- 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 ...
- 65.8k
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 ...
- 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.
...
- 393
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 ...
- 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 ...
- 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:
...
- 40.2k
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 ...
- 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 ...
- 176
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 ...
- 161
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 ...
- 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 ...
- 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 ...
- 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 ...
- 65.8k
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 ...
- 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 ...
- 65.8k
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 ...
- 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 ...
- 65.8k
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 ...
- 16k
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 ...
- 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 ...
- 1,359
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 ...
- 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 (...
- 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, ...
- 152k
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 ...
- 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 ...
- 30.3k
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 ...
- 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 ...
- 6,055
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/
- 280
Only top scored, non community-wiki answers of a minimum length are eligible