It has been a while since I wrote a blog post for TSQL Tuesday and there is no better time then now following on from Brent Ozar’s Invite about our favourite data types in SQL server.
I was generally reading about SQL Server and how things have changed since the days of just having it within a Windows Eco-system only. It then led me to a cool website, SQL Server development – build an app using SQL server where you can get to see the high-level requirements of starting to build a solution based on a wide variation of languages and operating systems. https://www.microsoft.com/en-us/sql-server/developer-get-started/
There are many combinations possible with SQL Server being the back-end database of choice.
Another re-post of a video from last year, this time showing you an in-built protection of setting max server memory for your SQL Server. You can clearly see that if you enter a silly figure such as 50 MB, the minimum memory amount allowable for max server memory is 128 MB. You will see SSMS (SQL Server Management Studio) change it to 128MB.
The truncate option is fast and efficient but did you know that it takes a certain lock where you could actually be blocked?
Forget about Adaptive Query Processing for a minute, what other feature have I been waiting for? SELECT INTO a specific filegroup, not the default filegroup! I have needed this feature many times in the past. Let’s take a look at it using the WideWorldImportersDW database.
DBCC CHECKDB has the ability to perform parallel checking of objects. However, it absolutely depends on the edition of SQL Server, it only happens when using enterprise edition.
Let’s see this in action. I propose the following tests for this blog post:
- Test on a SQL Server Enterprise Edition.
- Test on a non-enterprise edition of SQL Server.
I could not read my error log on one of my local SQL Servers, when I executed the following code:
I received the below:
Msg 22004, Level 16, State 1, Line 2 Failed to open loopback connection. Please see event log for more information. Msg 22004, Level 16, State 1, Line 2 Error log location not found.
If you know about DBCC CHECKDB then most likely you will know about DBCC CHECKTABLE. Quite simply this command performs primitive system-catalog consistency checks, per-table consistency checks on the single table specified, and cross-table consistency checks on indexed views that reference the specified table. (Page 899 Microsoft SQL Server Internals 2012, Chapter 14, Page 899, Paul Randal)
With Halloween around the corner what better topic to discuss than phantom reads. A phantom read occurs when rows have been inserted after a read operation and becomes visible in a follow-up read operation within the same transaction.
So I had a corruption issue and I was thinking about running repair but I wanted to know what would potentially get deleted.