I haven’t had to work on the Database Weekly newsletter in a month, and I knew my time was coming, but for some reason I didn’t realize it was this week. My calendar didn’t remind me, or if it did I ignored it. When I closed down work yesterday I thought it was strange that I didn’t see a newsletter scheduled and after volleyball this morning I thought I should check.
Yep, my week.
So I’m jamming this morning, trying to get a newsletter built and scheduled.
One of the things you want to be aware of when writing T-SQL is using the proper function for a particular problem. Someone posted a question asking about why they were getting a 0 for this code:
SELECT Mychar , '''' + mychar + '''' FROM dbo.MyTable
That gave me these results
I used the quotes in order to show that one of my columns has spaces trailing in one of the columns. I noticed that the poster was wondering why they had these results?
SELECT Mychar , LEN(mychar) FROM dbo.MyTable
In the table, clearly there are 4 characters for the row with “4D” and 5 characters for the next row. However the length is being returned as 0. If you were planning on testing for blank strings, or using some substring function, this could be an issue.
The reason is simple. LEN, as noted in Books Online, ignores trailing spaces. The description of the function is: Returns the number of characters of the specified string expression, excluding trailing blanks.
So if you have a space at the end of your string, or just a string of spaces, you don’t get the correct length. What should you use?
Datalength – This function is designed to show the number of bytes used by the string, not the characters. Code shown below:
SELECT MyID , '''' + mychar + '''' , LEN(mychar) , DATALENGTH(mychar) FROM dbo.MyTable
A good thing to be aware of if you are writing string test routines. LEN is the function I know most people use, but it is somewhat flawed, IMHO, in T-SQL
There seems to be regular disagreement from SQL Server professionals about what features are most important for future versions and which bugs should receive the highest priority. However one thing that many customers complain about is the lack of features in the Standard Edition (SE) that exist in the Enterprise (EE) or Data Center (DC) editions.
In SQL Server 2000, it used to be that a customer had to buy Enterprise Edition (EE) to get clustering or log shipping. In SQL Server 2005 Standard Edition got clustering, but no partitioning or database snapshots. SQL Server 2008 and SQL Server 2008 R2 added new features, some of which are only available in EE and DC editions. In each case, as SQL Server evolves, some features get moved down to SE, while others do not. The distinctions between editions are arbitrarily made, with the idea that some features are “Enterprise and are used for large scale SQL Server installations. However there are also size limits on the editions that limit the number of instances, nodes in clusters, memory, processors, and more.
Is that the best way to separate the editions of SQL Server? It seems that I rarely find many people that agree with the different mix of features and limits that are built into the different editions. This Friday I wanted to get your opinion:
What should differentiate Enterprise (or Data Center) edition from Standard edition?
Do you think there should be a feature distinction? Or a hardware distinction? What would you change about the way things are done, or what makes the most sense to you, as a SQL Server customer.
In my mind, I’d like to see SQL Server priced on scale, and not on features. I’d like to see all features in all editions above Express. However charge me by the core, or some formula of cores + RAM that might make licensing cheaper for those low end dual core systems we have now, maybe equal for 4-8 cores, and then scaling up as people move to more cores, and handle larger workloads. Let me add licenses as I add hardware, and easily move those licenses around as I move my instances between physical servers.
Licensing is always a controversial subject, and one that we rarely agree on. However I’m curious what people think about the plan that might seem the most fair to them.
Steve Jones
I read this Forbes piece on the hacker “Kayla”, which led me to this correspondence posted when she (or he) hacked HBGary Federal. The transcript of emails is rather amazing and a little scary. She manages to get access to the servers through a clever bit of social engineering against a security specialist.
The whole plot depends on access to a person’s email account, but that’s entirely possible. Imagine creating a distraction and then stealing a target’s smartphone. Done cleverly, a busy executive or IT worker might think they lost the phone and spend time trying to track it down. Meanwhile a hacker has access to their email, sending who knows what messages out.
As system administrators, or anyone with privileged access to systems or data, should we assume that an email request from a person for a password reset, or an access request to additional privileges is genuine? My thought is that we might not want to “trust” these systems and instead, implement some other type of verification method along with this trusted access. Perhaps we ought to call the person and verify the request if we know their voice, or require them to present their request in person. At one place I worked, we used to require someone to personally come to the IT office unless we were sure we knew that person’s voice. That’s not a perfect solution, but it could help increase security.
Maybe we need a safe word for each privileged account. It could be a word we request from the user when they ask, or maybe it is an uncommon word that has to be worked into the request to help verify the end user’s identity. Ideally we would just feed the message into some system that would authenticate it, rather than allowing a technician to manually verify the safe word from some store of words.
Security is hard, but and none of my suggestions is perfect, but as there are more and more script kiddies, hackers, and social engineering professionals, perhaps a little paranoia is a good thing. Perhaps making it hard to gain addition access, especially privileged access, is a good idea.
Steve Jones
Voice of the DBA 