A Fine Slice Of SQL Server
Greg Dodd doesn’t need no steenkin’ tables:
Sometimes you have a list of values or parameters that you’d like to run a select statement over the top of to get a result. There’s a few ways of doing this, usually I see people create a Temporary Table and insert the data. But is there an easier way?
Yes, we can select from VALUES:
The result of this is called a virtual table, and as far as other parts of the query are concerned, it’s just another table.
Comments closedQuestion: How can I find and report on SQL Server tables with LOB columns but where the table has no PRIMARY KEY? I’m classing an LOB column as ‘TEXT’, ‘NTEXT’,’IMAGE’ ,’XML’, ‘VARBINARY’,’NVARCHAR(MAX)’ & ‘VARCHAR(MAX)’.
Read on for the answer.
Comments closedBill Fellows fights with the language:
It seems so easy, I was building json in SQL Server and the date format for the API specified it needed to have 3 millsecond digits and the zulu timezone signifier. Easy peasy, lemon squeezey, that is ISO8601 with time zone Z format code 127
SELECT CONVERT(char(24), GETDATE(), 127) AS waitAMinute;Running that query yields something like 2023-05-02T10:47:18.850 Almost there but where’s my Z? Hmmm, maybe it’s because I need to put this into UTC?SELECT CONVERT(char(24), GETUTCDATE(), 127) AS SwingAndAMiss;Running that query yields something like 2023-05-02T15:47:18.850 It’s in UTC but still no timezone indicator.
Read on for several attempts and what finally did the trick.
Comments closedEric Rouach shows off a nice extension to T-SQL in SQL Server 2022:
Create External Table As Select or “CETAS” has finally become available on SQL Server with the release of the 2022 version.
After a short setup, we can create various formats files containing any query’s result set. The created file/s must be kept on an Azure storage solution i.e. Azure Blob Storage.
The process also creates an external table reflecting the updated file’s content.
We’ve been able to do this in Azure Synapse Analytics dedicated and serverless SQL pools for a while, so it’s good to be able to create an external table from a SELECT query on-premises, especially considering that it’s the only way we have left to write to external sources using PolyBase.
Chad Callihan occasionally inserts something:
Have you ever maxed out the SQL Server table column limit yet still needed more columns? Hopefully not considering SQL Server has a max limit of 1024 columns per table. But as I found out, it’s possible for someone to reach out and ask for even more. Sparse columns are an option to consider when you can’t get enough. Let’s take a look at what sparse columns are and how they can be used.
Sparse columns have very little utility, except in the most “I don’t think you’re doing it right” scenarios. Still, if you happen to end up in that scenario, there is a way out, though I’d really want to understand the nature of the data in that problem and, knowing just the amount of detail in the scenario that I do, would lean toward storing the data either in an unpivoted fashion (one row per entity * attribute in an EAV-style “‘additional attributes” table) or as a JSON string and let the client sort it out.
Comments closedHaving access to the
psqlcommand-line tool is essential for any developers or DBAs that are actively working with and connecting to PostgreSQL databases. In our first article, we discussed the brief history ofpsqland demonstrated how to install it on your platform of choice and connect to a PostgreSQL database.In this article we’ll get you up and running with all of the essential things you need to know to start on your journey to becoming a
psqlpower-user. From basic command syntax to the most common (and helpful) meta-commands, it’s all covered throughout the rest of the article.
Also check out the comments for a link to a pager which works with psql.
Comments closedChad Callihan makes an update:
When recently troubleshooting an issue, I needed to update a database record to test application functionality. Because the table had an Always Encrypted column, some extra steps were needed to make the UPDATE successfully. Let’s look at the error encountered and how it was resolved.
Click through for the error and see how Chad got around the problem. This is definitely one of those head-scratcher solutions, where you can kind of understand why it’s necessary but still think the required process is dumb.
Comments closedPostgreSQL has a separate command-line tool that’s been available for decades and is included with any installation of PostgreSQL. Many long-term PostgreSQL users, developers, and administrators rely on
psqlto help them quickly connect to databases, examine the schema, and execute SQL queries.Knowing how to install and use basic
psqlcommands is an essential skill to have for anyone that will connect to PostgreSQL.
Most of the article covers installation and starting up, but before you can write queries, you’ve got to connect to the server and database.
Comments closedAaron Bertrand threatens to angry up the blood:
I’ve long been a huge advocate for always referencing objects with a schema prefix in SQL Server.
In spite of what may be a controversial title to many of my regular blog readers, I don’t really want you to stop that practice in most of your T-SQL code, because the schema prefix is important and useful most of the time. At Stack Overflow, though, there is a very specific pattern we use where not specifying the schema is beneficial.
Mind you, Aaron’s use case is a rather niche example, so I don’t plan on burning him in effigy. Just maybe singeing him a tiny bit.
Comments closedBrian Bønk shares some history:
A couple of years back I needed to find the latest used parameters from a reporting services – SSRS (yes, it is old!) report. The functionality was to find the latest used parameters for all parameters dynamically and use them as the default values for each parameter in the report, when tjhe user opened the report. If the uesr had not used the reprot before, it should ask for the parameters.
This was a alot of fun for me to do, as I needed to query the internal tables from the reporting services database. The tables ExecutionLogStorage and the Catalog was on play. This due to the fact that I needed the active user’s latest used parameter values from each specific report and this in runtime when the report was opened.
Click through to see how Brian did it.
Comments closed