Skip to main content
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 ...
Paul White's user avatar
  • 86.4k
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 ...
Solomon Rutzky's user avatar
16 votes
Accepted

Copying a table (and all of its data) from one server to another?

Create a new DB on the same server. Use SELECT INTO to clone the table into that DB. Backup that now-tiny DB. Now you have a backup containing the data that you want, and nothing else, so it can't be ...
Brondahl's user avatar
  • 286
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 ...
12 votes

Copying a table (and all of its data) from one server to another?

What I've used a lot is native SQL Server Import and Export Wizard. In SSMS's Object Explorer -> Source SQL Server -> Right click database -> Tasks -> Export Data Choose a Data Source: ...
Aleksey Vitsko's user avatar
10 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 ...
Jon Scott's user avatar
  • 244
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. ...
Eric Prévost's user avatar
9 votes

Copying a table (and all of its data) from one server to another?

One option is to use copy-dbadbtabledata from https://docs.dbatools.io/ powershell module. copy-dbadbTableData -SqlInstance sourceserver -Database sourcedatabase -Table dbo.sometablename -Destination ...
Bob Klimes's user avatar
  • 3,254
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: ...
David Browne - Microsoft's user avatar
7 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 ...
Josh Darnell's user avatar
  • 29.4k
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 ...
Sonya Marshall's user avatar
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 ...
Jovan MSFT's user avatar
6 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 ...
Solomon Rutzky's user avatar
6 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 good ...
Rob Farley's user avatar
  • 16.2k
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 ...
Solomon Rutzky's 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 ...
mustaccio's user avatar
  • 26.3k
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 (...
cjsommer's user avatar
  • 336
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 ...
Hannah Vernon's user avatar
  • 70.2k
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, ...
Erwin Brandstetter's 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 ...
Hadi's user avatar
  • 1,222
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 ...
jynus's user avatar
  • 14.9k
4 votes
Accepted

Window Function to get pairs of records and compute the time difference

You need something like -- get previous status, use -1 value if no previous record (because NULL value needs more complex condition in cte2) WITH cte1 AS (SELECT Event_Type, ...
Akina's user avatar
  • 20k
3 votes
Accepted

SSISDB Transaction Log Backup

Change the recovery model to SIMPLE and be done with it since this is a Development server and you mentioned you don't care about the history. For more information on recovery models and transaction ...
Tara Kizer's user avatar
  • 5,966
3 votes
Accepted

How can I DRY up a series of upserts of the same fields from multiple tables?

You could use UNION in the CTE. There is still some duplication of code but less than before: with etl (storenumber, filename, last_extracted) as ( ( select distinct on (storenumber) ...
ypercubeᵀᴹ's user avatar
3 votes

Query "last" records in a table with non-sequential key fields

You can use ORA_ROWSCN pseudocolumn to find rows that have been changed since last update. By default it tracks changes by database block, not by row, but it can be used to limit the amount of data ...
sjk's user avatar
  • 751
3 votes

PostgreSQL importing thousands of columns as an array

Couldn't resist :-) <data.txt perl -pe 's/((.*?,){2})(.*)/\1"{\3}"/' X764_130520,0,"{0.35,9.68,0.11,0.04,0.03,0,32.54,0.13,49.73,33.34,0.77,..}" X800_130701,0,"{2.24,32.04,0.13,0.34,0.04,0,39.47,...
David דודו Markovitz's user avatar
3 votes
Accepted

Optimize SSIS package for fewer queries

You can achieve this without using Foreach enumerator, just use one data flow task to achieve this. Building the Package First of all, add a Data Flow Task to the control Flow 1. OLEDB Sources In ...
Hadi's user avatar
  • 1,222
3 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 ...
David Spillett's user avatar
3 votes

Is ELT appropriate for life critcal applications such as Medical Systems when converting to a new application?

ELT is fine for a critical system. Your problem is predicated on this faulty assumption: "Because the data is not cleaned up like in ETL..." You can do most any cleanup with ELT as you do with ETL. ...
CalZ's user avatar
  • 924
3 votes
Accepted

Does ELT data end up in an RDBMS?

I think this will be easier with an example: Database A has table C. Database B has table D. C and D are very similar but not identical and the data needs to be cleaned up before being loaded in to ...
indiri's user avatar
  • 2,211

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