A Fine Slice Of SQL Server
That seems to work easily. Maybe that’s the easiest way at the end of the day (?).
One problem that may arise – besides building on proprietary code that may change without notice – is that Excel may have problems reading a UTF8 csv, as explained here.
Read on for more info on what has become the de facto web standard for text.
Comments closedHanjo Oden wraps up a series on training models on AWS using H2O in R:
To generate these, you can log into your AWS dashboard, go to the IAM (Identity and Access Management) dashboard and select the
Userstab. On theUserstab, add a user and also the administration rights that you want the user to have.Remember to restart R once you have filled in the access key information in the .Renviron file for it to take effect.At this point, those familiar with
cloudyrsuite is probably asking – “This is exactly the same aslibrary(aws.ec2), so why use boto3?“. Well, to be honest, I was usingaws.ec2for a while, but I find spot-instances, which the current version ofaws.ec2does not support. In addition I found thatboto3has some other functionalitue – which I prefer. For a full list ofboto3functions to interact with an EC2 instance, have a look at the reference manual.
It’s pretty good stuff; check it out.
Comments closedKoen Verbeeck notes that Snowflake DB aliasing is a bit more robust than SQL Server’s:
That’s right. I defined an expression – a concatenation of two fields – and used the alias of that expression in another expression. Furthermore, I used the alias of the second expression in the WHERE clause. Gasp.
My workaround is to use CROSS APPLY and define calculations in blocks there. This doesn’t work for aggregation operations, but in cases like Koen’s example, it does simplify the SELECT and WHERE clauses. This is a nicer solution, though.
Max Vernon has a helpful script to tell you if, for example, ANSI_NULLS is on:
SQL Server can be configured to provide certain behavior to client sessions, via the
SET OPTIONS=command, or via thesys.sp_configure 'user options'system stored procedure. TheSET OPTIONS=command only affects the current session, whereas thesys.sp_configure 'user options'system stored procedure configures the default values for all future user sessions.
Click through for the script, which even contains a quick description of each option.
Comments closedKenneth Fisher has a few notes on disabling indexes:
Indexes are probably the number one tool we have to improve performance. That said, there are times when we want to put that index on hold. While indexes dramatically improve read performance they do cause a slight dip in write performance. This isn’t significant most of the time but when doing a large load it can frequently be faster to get rid of the existing indexes and then put them back when you are done.
I don’t think that I’ve ever regularly disabled indexes, even during bulk loading. It’s good to know that the option exists, however.
Comments closedA while back I promised I’d write about what allows SQL Server to perform two seeks rather than a seek with a residual predicate.
More recently, a post touched a bit on predicate selectivity in index design, and how missing index requests don’t factor that in when requesting indexes.
This post should tie the two together a bit. Maybe. Hopefully. We’ll see where it goes, eh?
Also apropos: missing index hints return results in alphabetical order, not in selectivity order or what would be best for queries. In other words, just because the green text in SSMS says it’s the index you want doesn’t mean it’s the index you need.
Comments closedJeff Iannucci explains the benefits of Central Management Servers:
If you have more than a handful of instances, you really owe it to yourself to take a few minutes and set up your own CMS. The hard part is getting a list of all the instances, but you need to do that anyways, right?
Once you have that list pick ONE SQL Server instance to create your CMS. You want this to be something with solid up-time that is accessible to other team members, but not an instance that’s already spending CPU cycles on something critical.
The lack of CMS compatibility is one of my larger pain points with Azure Data Studio and an issue which hopefully gets fixed this year.
Comments closedHamish Watson explains that Windows authentication is not available with Azure Managed Instances:
Azure SQL Managed Instances do not utilise windows authentication – so your two methods of authenticating applications and users are:
– SQL Authentication:This authentication method uses a username and password.
– Azure Active Directory Authentication:This authentication method uses identities managed by Azure Active Directory and is supported for managed and integrated domains. Use Active Directory authentication (integrated security) whenever possible.
Hamish also elaborates on some of the trickier bits about Azure Active Directory for someone used to on-prem AD solutions.
Comments closed