@CadeRoux Is it common, in your experience, to require cherry picking PRs into specific branches and then using those branches to run builds, instead of using a common main branch (and using the PRs to merge into the main branch from individual branches)?
@user346760 that type of language is not acceptable on the Stack Exchange network. Please remember that this is supposed to be a professional environment; you can review the Code of Conduct for a refresher on how users are supposed to interact here.
@miracle173 SQL Server has hash union just FYI. It works pretty much as you describe for the hash table build, but separate duplicate removal is necessary on the probe side of the union. That can also be done with a hash aggregate, of course. dbfiddle.uk/Z88KrIGd — Paul White ♦6 hours ago
@DavidSpillett I don't think that UNION DISTINCT "essentially" forces a sort. I am pretty sure one can efficiently eliminate duplicates by using a hash table, at least in special situation, e.g. one has a lot of values but most of them are repeating. I don't know if there is a database that does actually use such an algorithm, but that is not relevant here. — miracle1736 hours ago
David also answers a lot of SQL Server questions (he has the gold badge)
It's a pointless side-discussion of course. It won't be around long.
Random question, does anyone know if the SSMS Results window modifies how it represents VARBINARY results in any way?...or it's displayed exactly how it's actually stored?
@J.D. It's a string representation. The binary is stored as bits. Exactly how depends on the storage type. Are you asking because you have something that looks different from what you expected? You're not converting a different type to binary first?
Yea apologies my question was a little ambiguous. To get straight to the point, I've noticed if I copy and paste the string representation of a VARBINARY value from the results window in SSMS, it doesn't act the same as consuming the column directly.
@SeanGallardy Well, we use Team City, and without filtering, it's going to run builds off of every branch (from the repo it's using - which obviously is the GitHub main repo)! What I have to do because our Team City configuration also needs to fork on major versions usually is that a particular set of configurations is tied to a particular main branch - so development or release/7.0SU3 or release/8.0SU2
@PaulWhite To elaborate, for example, when the column holds the bytes of an image, and consuming it in a C# app to display said image, copying the string value from SSMS results in a broken image.
We have a table in our database for storing photos, the datatype of the actual photo data is varbinary(max).
When inserting a photo from the application, the data is correctly stored and the photo can be viewed as normal.
However, I am updating another record to have the same photo and when I u...
lol re: regex I do like the idea of it, but only simple expressions. At a previous job, FinTech, email parsing, all kinds of crazy regex, maybe that's where I lost some of my puzzle pieces. 🧩
So I have a whole set of TeamCity configurations for Development which sticks with the development branch in GitHub. Then that gets copied to a release 10.0 set of configurations which points at release/10.0 branch in GitHub. Which is usually a branch off of development but where it came from really doesn't matter. And the root parameters for the TeamCity configurations sets the DefaultBranch to release/10.0
If someone makes a feature release branch features/nice, that's not going to get built in any of my TeamCity builds. Which is fine. The entire build and testing cycle takes like 4 hours because it runs thousands of what are basically end-to-end system tests, putting data in the OLTP and ensuring that the data warehouse representation is correct.
I had to learn RegEx once, in a decent amount of detail, in order to pass a Microsoft certification exam. I have forgotten almost everything about it since then.
All this is for @SeanGallardy - we have a small team and it's basically me for dev, my boss for XSLT and definition, a dashboard/business analyst for SSRS/Tableau/PowerBI/requirements gathering and an informaticist for interfacing with the clinical cardiovascular knowledgebase and domain experts.
So we really use GitHub less like a real distributed system and the main repo is more like a traditional TFS/SVN central system that drives the builds - I personally run local builds several times a day, and my boss could if he needed to, but he doesn't and probably would struggle to get his running the first time.
@PaulWhite Yea, I've set the limit to the max for Text results, and I have 2 rows with VARBINARY values. One is much longer than the other. So I have to believe the shorter one isn't truncated. But it's still "broken" only when copied directly out of SSMS.
I think if our product was not so database dependent (each branch needs a matching set of OLTP and analytic databases to run the install and test), we could figure out how to run adhoc installs of every branch pushed to the central repo, but there would be a lot of databases needing to be cleaned up. (The build needs to point at a fully installed prior version, because we have an automated release notes that analyzes key differences in our rules engine)
@PaulWhite I've noticed this happen a couple of different ways. But most recently, we have a C# app that renders an image from it. If the app pulls the value from the Table directly by making a data connection to the database and takes it in as a byte array ultimately. If I copy the string directly from SSMS and input it as a byte array in the C# code directly, the image doesn't render.
I have always have problems with SSMS and large columns (but not dealt with binary much). In fact, I came across one of my very old questions about this from years and years ago just the other day that had newer activity on it.
When I have a result set in the grid like:
SELECT 'line 1
line 2
line 3'
or
SELECT 'line 1' + CHAR(13) + CHAR(10) + 'line 2' + CHAR(13) + CHAR(10) + 'line 3'
With embedded CRLF, the display in the grid appears to replace them with spaces (I guess so that they will display all the data).
Th...
@PaulWhite Cheers! I should be back at my desk in about 10 minutes. Will verify everything you said. I appreciate it! Regarding your question if it's odd numbered hex digits, is that to determine if it's being padded or not essentially?
@J.D. It's just something odd I remembered from the Q & A
Your binary representation has an odd number of digits. It takes two hex digits to specify a single byte, hence the padding. Are you really trying to store half a byte? That would seem to be an odd (ha!) thing to do. e.g. DECLARE @v varbinary(max) = 0x123; SELECT @v; — Paul White ♦Jun 12, 2015 at 11:08
lol nah tried that already. Also I guess if it was padded it would start with 0x0... but it doesn't start with 0x0 (from what I recall) so I guess it's not padded and therefore I'm assuming it's an even number of hex digits.
@PaulWhite Yeah, partner status at the consulting firm I worked for. All of us that worked there were encouraged to get certs. "Encouraged" in that sense that is was part of our annual performance review 😅
Reading the SQL Server books online front to cover exposed me to a breadth of stuff, but my pea brain only allowed so much of it to stick. But you run into some one-offs of interesting things you rarely hear talked about such as sparse columns, or things that are actually useful like you mentioned, GROUPING SETS and NTILE etc.
@ErikDarling GROUPING SETS is usually only useful for those idiotic "we must do the whole report in T-SQL, no apps allowed" situations. But I once used it in anger for an app which needed to show various levels of grouping, and it was far easier to do using GROUPING SETS. I reduced the original proc down from 4 separate queries to 1.
The rest of those are either "meh" or not recommended. Sparse columns looks mildly useful in specific situations, as does CHECK OPTION.
Indexed views was something I came across lateish and have found it very useful.
We have a table in our database for storing photos, the datatype of the actual photo data is varbinary(max).
When inserting a photo from the application, the data is correctly stored and the photo can be viewed as normal.
However, I am updating another record to have the same photo and when I u...
When I have a result set in the grid like:
SELECT 'line 1
line 2
line 3'
or
SELECT 'line 1' + CHAR(13) + CHAR(10) + 'line 2' + CHAR(13) + CHAR(10) + 'line 3'
With embedded CRLF, the display in the grid appears to replace them with spaces (I guess so that they will display all the data).
Th...![Wait, forgot to escape a space. Wheeeeee[taptaptap]eeeeee.](https://imgs.xkcd.com/comics/regular_expressions.png "Wait, forgot to escape a space. Wheeeeee[taptaptap]eeeeee.")
