This editorial was originally published on Mar 21, 2009. It is being re-run as Steve is on vacation.
I ran across this post ranting about T-SQL. It's got going pretty quickly with some less that professional responses. It's cleaned itself up a bit, but it got me thinking for this Friday.
My first programming was done in BASIC. From there I played with a little assembler before going on to Pascal and C in high school. The came LISP, APL, Fortran, more assembler, C++, and a little ADA in college. My professional career had me programming in Lotus 1-2-3, dBase, FoxPro, C, VB, and finally SQL with minor dabbling in HTML, XML, ASP, and some .NET.
I think I've had a decent amount of programming experience in my life. In that time, I've found that most programming languages to work fine, and while I've enjoyed some more than others, I haven't thought that any "sucked." I wouldn't want to write most modern apps in APL, but I don't much think there's any thing wrong with picking VB.NET v Java v C#. So for this Friday's poll:
Is T-SQL really a strange paradigm?
Or SQL in general? Is it really messed up? Would the structures used by LINQ make more sense to anyone? Hardcore developers seem to like a different structure, but is it because it seems more natural to them? Or is SQL really such a strange beast.
Many people seem to have trouble with SQL. Just read the forums here at SQLServerCentral to see all the issues that come when people try to query the database. But is it just hard to learn or is there a better way to build a query language?
The Voice of the DBA Podcasts
The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.
Before the SQL Window functions were implemented, it was tricky to calculate rolling totals or moving averages efficiently in SQL Server. There are now a number of techniques, but which has the best performance? Dwain Camps gets out the metaphorical stopwatch. More »
SQL Server 2012 brought in some great new features and one of the important ones is scalability and performance via AlwaysOn. AlwaysOn is a superset feature and is a combination of many things you will learn about. As the language suggests, this technology achieves a SQL Server infrastructure that can be "always on". For businesses that run 24x7 downtime means the loss of business. This type of risk is out of the question for these businesses. This book discusses in detail the concepts of SQL Server AlwaysOn starting from the basics.
Yesterday's Question of the Day
(by Jens-Peter Giersch):
The following scenario works on SQL Server 2000, 2005, 2008, 2008 R2, 2012 and 2014 CTP 2. The syntax below is valid for SQL Server 2012 and above; for earlier versions please refer to the documentation or search your memory for the appropriate commands. The syntax differences do not affect the result!
Create the Windows user and groups first:
net user TestWindowsUser Test1234! /add net localgroup TestWindowsDBadmin /add net localgroup TestWindowsDBread /add
Add Windows user to Windows groups
net localgroup TestWindowsDBadmins TestWindowsUser /add
net localgroup TestWindowsDBreads TestWindowsUser /add
Log in to SQL Server with enough permissions to create databases and manage logins. Create this database:
CREATE DATABASE DBall;
Both Windows groups have logins...
CREATE LOGIN [Domain\TestWindowsDBadmins] FROM WINDOWS;
CREATE LOGIN [Domain\TestWindowsDBreaders] FROM WINDOWS;
..and both logins have access to the database "DBall":
CREATE USER TestSQLDBadmin FROM Login [Domain\TestWindowsDBadmins];
CREATE USER TestSQLDBreader FROM Login [Domain\TestWindowsDBreaders];
Add user "TestSQLDBreader" to the role "db_datareader"...
ALTER ROLE db_datareader ADD MEMBER TestSQLDBreader;
...and user "TestSQLDBadmin" to the role "db_owner":
ALTER ROLE db_owner ADD MEMBER TestSQLDBadmin;
Now drop the login with dbo permissions:
DROP LOGIN [Domain\TestSQLDBadmins];
User "TestWindowsUser" meanwhile logs in to SQL Server, connects to database "DBall" and executes the following command:
CREATE TABLE t_Test
ID INT IDENTITY NOT NULL,
Test VARCHAR(200) NULL
What permissions does the TestWindowsUser user have and what is the result of this command? (select 4)
"TestWindowsUser" has the permissions of the "Public" role.
"TestWindowsUser" has the permissions of the "db_datareader" role.
"TestWindowsUser" has the permissions of the "db_owner" role.
The CREATE TABLE command executes successfully.
"TestWindowsUser" has all the permissions of the roles "Public", "db_datareader" and "db_owner". Therefore the "CREATE TABLE" statement succeeds.
SQL Server identifies every user by its SID. If the login is an entity of Windows, the SID of the database user is the same SID as of the login as well as that of the Windows group.
The SID detected was at least part of an already authenticated entity of Windows and therefore SQL Server “assumes” the SID is safe and needs not to be checked. SQL Server doesn't "see" that the login doesn’t exist anymore and the user ís orphaned.
The alternative would be that SQL Server would have to query Windows for every request of a SQL Server user for the membership in any groups relevant in this database. If only one group were involved the overhead might look acceptable. The moment when more than let’s say 5 groups were involved (see comment below) would create an overhead and a resource leakage that possibly wouldn’t be acceptable anymore, especially for a simple statement like
SELECT name FROM sys.objects;
It falls into the responsibility of the DBA to make sure that orphaned users do not occur.
Up to now I didn’t find any documentation about this effect.
Members of the SQL Server Clinic team at the PASS conference in Charlotte suspect a caching problem of the token on SQL Server which I doubt a bit: The described effect still exists after a restart; this means there is a new authentication and a new security token (Kerberos or NTLM, depending on whether you try this out on a domain or workgroup machine).
A contact of mine at Microsoft suggested me unofficially this answer:
One of the reason behind this design is because of the cost involved in performing the additional checks to confirm orphaned user groups.
Since the user group is an entity which is contained within databases SQL Server doesn’t do additional checks to validate the corresponding login.
Consider this scenario: ‘Domain\TestWindowsUser’ is part of >30 groups. Every time this user logs in it would need to validate if the user group is orphaned or not. This would be a costly operation.
Ignore how Domain\TestWindowsUser ended up in the situation. TestWindowsUser is in a domain group that is a member of db_owner and another that is a member of db_dataReader. So TestWindowsUser has all permissions of both roles. The lack of a login for TestWindowsDBadmins just means not everyone in that group can exercise the db_owner membership in the database – but anyone with a membership in TestWindowsDBreaders AND in TestWindowsDBadmins is a db_owner.
Hence this is completely by design and not a bug.”
You may check from time to time whether Microsoft answered it or not. If you want to engage a bit more in the community please feel free to add a comment or confirm that you were able to reproduce this behaviour. Perhaps we get an official statement.
Many thanks to Tom (“L’eomot inversé”) for his precious time he spent reviewing this QotD and his valuable advice!
Frequently you'll want to know how fast your database has been growing. Ideally, you'll have historical size information on all the databases that you work on. In the real world, however, this is not necessarily the case.
What we have most often is the backup history. Luckily, we can get a rough outline of the growth of your database, over time, from the msdb..backupset table. This query will give the size of the backup, every time that a backup was done. From this you can get a pretty good idea of how fast your database is growing.
Update Insert Trigger
- I have this Update Trigger:
CREATE TRIGGER trgUpdateInsert
declare @DirectID int;
declare @DirectName varchar(100);
declare @DirectAmt decimal(10,2);
select @DirectID = i.Direct_ID from inserted...
Updating a db table with a loop
I have this sql query:
from dbo.BI1_View_Dim_CalendarDefinition as C WITH (NOLOCK)
inner join dbo.BI1_View_Dim_SystemParameters WITH (NOLOCK)
on Run_Type = 'SALES'
where TrxDate = RUN_DateHistoryFrom
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.