Categorization of Blog Posts

Sometimes I get asked; "Hey Artemis, are you a SQL Server Administrator or a Developer?" I then answer: "none of the two" :)

I would rather prefer calling myself a Data Architect as I believe it is more accurate. In a relevant article I posted a year ago, I talked about the responsibilities of database developers and administrators and how these responsibilities are often interconnected. The key point of that article was that by the time you are professionally involved with Database Systems you cannot only just know one of the two aspects. So, we often meet Database professionals that are administrators with development skills and vice versa.

If we take a look in Wikipedia, we can see that the role of a Data Architect is described as follows: "A data architect is a person responsible for ensuring that the data assets of an organization are supported by an architecture supporting the organization in achieving its strategic goals." I fully agree.

As you can see, on my blog I post articles having to do with SQL Server administration, development, SQL, security, Business Intelligence, etc. I don't like restricting my quest for knowledge in just one aspect of SQL Server and that's why I mainly focus on Administration as well as on Development and some other topics.

Anyways, after this introduction, I would like to mention that I've just finished updating my personal website and guess what; I've added a page for SQL Server there as well! :)

This page, will be providing a list of most of my blog posts categorized in order for presenting my posts in a more structured way. It will actually be an index for all my articles posted here.

So, here's the link!

Have a nice weekend!

P.S. This is my 99th post. I am wondering; what topic should my 100th post be featuring? :)
Read more on this article...

Eliminating Blank Spaces in SQL Server Tables

Blank spaces always needed special handling in many programming languages.

This is not an exception in the case of the SQL programming language.

In SQL Server, a variety of built-in string functions exist for assisting, among other, the developer to solve data-related problems in the case of undesired blank spaces.

However, the blank spaces scenarios vary and sometimes it is necessary to use a combination of various built-in string functions for solving the problem.

For example, consider having one or more columns within a record as the following:

An approach could be the following, using just the REPLACE built-in function like this:

Also, you could try this:

The above two approaches do not fully solve the problem of the specific blank spaces scenario.

As you can see, it started getting a little bit tricky regarding how it is possible to efficiently handle the specific blank spaces problem.

Further below I am suggesting a workaround on how to resolve such issues.

To this end, first we need to create a sample data set with a column containing blank spaces:

Now let's take a look at the data:




OK, it's time to implement the actual logic for addressing the blank spaces problem.
As it will be too complex to include the logic within a single T-SQL statement, I preferred to create a function because it will be much easier to use it again in the future and the code looks more structured.

Here's the DDL for my function:

--

--Function Name: NoBlankSpaces

--Purpose: Eliminate blank spaces (up to three words)

--Author: Artemakis Artemiou

--Input Parameter: Varchar(250)

--Output: Varchar(250)

--

CREATE FUNCTION [dbo].[NoBlankSpaces]

(

@string varchar(250)

)

RETURNS varchar(250)

AS

BEGIN

declare @part1 as varchar(100)

declare @part2 as varchar(100)

declare @part3 as varchar(100)

declare @part4 as varchar(100)

declare @temp as varchar(100)

set @part1=(rtrim(substring(@string,0,(charindex(' ',ltrim(@string))))))

set @temp=ltrim(substring(@string,(charindex(' ',@string)),len(@string)+1))

set @part2=SUBSTRING(@temp,0,CHARINDEX(' ',@temp))

set @temp=SUBSTRING(@temp,0,250)

set @part3=ltrim(SUBSTRING(@temp,CHARINDEX(' ',@temp),250))

set @string=rtrim(ltrim(@part1+' '+@part2+' '+@part3))

RETURN @string

END

GO

-------------End of Function DDL-----------------

As you can see in the above DDL code, I am using a combination of the SUBSTRING, LTRIM, RTRIM and the CHARINDEX SQL Server built-in string functions for achieving my goal.

Now, let's use the function and see what the outcome will be:

Cool! No more blank spaces! :)

The combination of the SQL Server built-in functions worked great towards the solution of the blank spaces issue.

You can download the code I used for this example from here.

I hope you found this post useful.

Drop me a line if you have any questions or comments!

Cheers!

Read more on this article...

Security Changes in SQL Server 2008

SQL Server 2008, among other introduced significant security changes that enhance the database administrators applying an even stricter security policy on the SQL Server instances.

Besides the changes in the Windows local groups that are created during the SQL Server setup process, the changes in surface control tools where now you can use the Policy-Based Management feature that is much more powerful than the Surface Area Configuration tool, and the Kerberos support for named pipes and shared memory protocols, the change that has a strong effect on the way that many of us used to work, is that the local Windows Group BUILTIN\Administrator is no longer by default included in the SQL Server sysadmin fixed server role on new SQL Server 2008 and R2 installations.

So, what does this mean? It means that if you try to access a SQL Server 2008 (or later) instance using a local administrator user account without explicitly granting him the sysadmin server role on the instance, you will not be able to have administrative rights on the instance. Actually, if this user has not any permissions on the specific instance, he will not be able to access the instance at all!

Someone might say that this makes things more complicated but the truth is that it does not. It is an excellent security enhancement that actually separates Windows administrator accounts from SQL Server administrators.

And by the way be careful when you install and perform the initial setup of a SQL Server 2008 (or later) instance because if you don’t include at least one user in the sysadmin role, you will be locked out of that instance :)

For more information on the security changes in SQL Server 2008 and SQL Server 2008 R2 you can visit this MSDN Library article. Make sure that you read it before configuring the security of your new SQL Server instance!
Read more on this article...