Home › Posts tagged sql server

sql server

When the client asks for a unique ID based on name and address but gives you dirty data

Posted on by — No Comments ↓

case when len(ROW_NUMBER() OVER( PARTITION BY left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3) ORDER BY left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3))) < 2
then (UPPER(left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3) + '0' + convert(varchar(3),ROW_NUMBER() OVER( PARTITION BY left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3) ORDER BY left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3)))))
else (UPPER(left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3) + convert(varchar(3),ROW_NUMBER() OVER( PARTITION BY left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3) ORDER BY left(replace(name,' ',''), 3) + left(replace(addrsscty,' ',''),3)))))
end as ID

 

exhausted
my work is done

 

How I passed 70-461

Posted on by — No Comments ↓

Conclusion: the exam is easier than you would think. Take advantage of the second shot offer, and test your knowledge as soon as possible. 

I put the conclusion on top of this article, because this was my biggest surprise after completing the exam.
I used Transcender’s exam simulator pack that comes with flashcards, too, if you’d like to practice every question. The 70-461 exam prep does not have the same questions that I saw in the exam, but the verbiage, format, and difficulty are quite similar, which is why I recommend this product.

For this particular exam I don’t recommend the official prep book. It does cover the necessary topics, just not in depth to call it an adequate preparation tool for the exam.

Here is what I did:
I bought the exam pack and practiced every day when I had some free time at work, I would say one hour every day.
After a month of that I signed up for the exam next week. Microsoft periodically offers second shot promos, that is, you can re-take the exam for free if you fail the first time.
(By the way, Transcender also offers money-back guarantee if you can’t complete the exam within a certain time.)
I attended the first exam with the only intention to see how I measure up. I expected to fail.

I did not fail. My score wasn’t stellar, but I passed.

Therefore, please allow me to repeat the conclusion:

Conclusion: the exam is easier than you would think. Take advantage of the second shot offer, and test your knowledge as soon as possible. 

DBCC DBReindex

Posted on by — No Comments ↓

I ran into some ‘Message 823’ issues on my development DB, and upon running DBCC CheckDB() I saw that my indexes were the ones throwing the errors.
I got this handy cursor from SQL Server Performance, and decided to save it here for me, and for you, so that I don’t have to search for it over and over again. The script by the way is from the talented Brad McGehee, and I suggest checking out the article because it has more information and more cursors for failing indexes.

Without further ado:
USE DatabaseName –Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = ‘base table’

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,’ ‘,90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor


Conclusion: you, too, can fix your indexes with a cursor now.

sp_msforeachtable

Posted on by — No Comments ↓

I will make this short and sweet for you.
If there is something you want to do to all tables in your database, there is a command for you.

I will demonstrate, watch my hands because I cheat. To add a column to every table:
exec sp_msforeachtable 'alter table ? add intGoat int NULL'
To delete a column from each table
exec sp_msforeachtable 'alter table ? drop column intGoat'

And this is how you use this undocumented little gem.

Small print: emphasis on undocumented, aka can go away or change in nature without any warning. Test it on your dev DB before using it.
Willy Wonka is telling you to watch with undocumented features.

TSQL Bulk Insert with specific columns

Posted on by — No Comments ↓

The internetz is a world wide library, but I actually couldn’t find one article that would spell out this solution. So…

You have a linked server, or a .csv file, or an excel sheet. You have a SQL server. You want to push data from the file to the server.
Fear not! There is a statement for that.

Say there is your table ‘herd’ and you just received data on 50000 million more goats. You want to load that information into ‘herd’ to make sure everything is at the same place. Right.

INSERT INTO herd(animals)
SELECT (Goats)
from openrowset('Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;Database=S:\temp\List_of_my_Goats.xlsx;HDR=YES;IMEX=1','select * from [Sheet1$]')

Take it to a spin. SQL server will open List_of_my_Goats.xlsx, look for ‘Goats’ column on Sheet 1, and insert the values into the table’s animals column. Goats  = Animals.

Now say you actually have a whole Library of Congress worth of data on your new Goats. Sex, color, age, ability_to_bite, hostility_to_goat_tippers, and the like.
You want to load *some* of these data into your table, but only a defined set of columns.

INSERT INTO herd(animals, age, sex)
SELECT (Goats) as [animals],
age as [age],
sex as [sex]
from openrowset('Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;Database=S:\temp\List_of_my_Goats.xlsx;HDR=YES;IMEX=1','select * from [Sheet1$]')

SQL server will open List_of_my_Goats.xlsx, look for the columns on Sheet 1, and insert the values into the table’s columns. Goats  = Animals. Age = age, sex = sex.

Now, just an FYI – I ran into some syntax errors when the excel sheet’s columns were named in a multi-part manner (goats.sex or goats.age). If you receive the usual ‘multi part identifier can not be bound’ error, just go ahead and rename the columns.

Conclusion: The world is a happy place with Openrowset.

Running sum for last 12 months, SQL style

Posted on by — No Comments ↓

I’m going to tell you a little secret. Come, sit closer.

You know your running chart for every other dashboard, showing

the SUM / Average / Top / Least / etc.

of Sales / New customers / Quotes  / etc.

for the last…

1 hour / 1 day / 1 week / 4 weeks / 12 months / etc…. ?

In SQL world I’ve been solving the problem with dateadd() getdate().

SELECT
sum(case when month(orderDate) = dateadd( mm, -1, getdate()) then orderAmount end) as [total order amount last month]
FROM
GoatOrders
GROUP BY
month(orderDate)

Of course if you want your chart to contain the last 12 months, you’d have to repeat the select 12 times. No brainer, redundant query.

Here comes this little trick I’ve been using:

SELECT
SUM(orderAmount), month(orderDate)
FROM
GoatOrders
WHERE
orderDate >= dateadd(yy, -1, getdate())
GROUP BY
month(orderDate),
year(orderDate)
ORDER BY –this is where the magic happens
year(orderDate) desc,
month(orderDate) desc

The ORDER BY has it all.

On a completely unrelated note: USA won 2-1 against Ghana!

Conclusion: Now you know how to write even shorter code for your running charts.

SQL Server – move database files

Posted on by — No Comments ↓

By definition SQL Server uses the install directory to store its data, unless you’ve taken care of that aspect during installation. The default directory is

C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL\DATA

Initially, that solution is absolutely fine, until you realize that SQL Server and its rapidly expanding databases are slowly eating up the disk space dedicated for your OS. That’s when you want to move your databases to another location. Luckily, that’s a rather easy procedure, easier than moving your iTunes database. (Which is a complete pain. Take note, Apple.)

The steps:

1. In SSMS, run the following:

USE MASTER;
GO
ALTER DATABASE MyAwesomeDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
EXEC MASTER.dbo.sp_detach_db @dbname = N’MyAwesomeDatabase’
GO

That will detach your database from the files.

2. Move the database files from their current location to their new home. You’ll want to copy both files associated with the database, in our case the two files are:

MyAwesomeDatabase.mdf
MyAwesomeDatabaselog.ldf

3. To relink the files with your database, run the following:

CREATE DATABASE [CRM] ON
( FILENAME = N’D:\Data\MyAwesomeDatabase.mdf.mdf’ ),
( FILENAME = N’D:\Data\MyAwesomeDatabase.mdf.ldf’ )
FOR ATTACH
GO

4. Ta-dah! That’s it.

ps – the Internetz is a redundant space. There are two tutorials on this topic, one on Technet, the other one on SQLAuthority. As one would expect, the Microsoft tutorial is absolutely confusing and impossible to follow. The latter on the other hand is an excellent tutorial, as matter of fact you should spend some time reading some of the articles you find there. Full of good stuff.

Conclusion: Now you know how to move your database files.