Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

GO-SQL blog A blog dedicated for SQL Server Developers alike!

My name is Andrew Fenna, and I am a database developer at The Laterooms Group. I have written this blog to educate my fellow database developers in subjects that I think are necessary to keep at the top of our game. I have been working with RDBMS’s since 2002 and I have worked within financial, gaming, public, and travel sectors within the Northwest of England. I have gained quite a lot of experience throughout my journey and have tackled some very difficult challenges along the way. Therefore, I am blogging my experiences, which I hope will give you the inspiration to try and tackle problems that are deemed impossible to start, but with some encouragement and guidance become much more simpler and structured, thank you for your time. Andrew J Fenna.

How to remove CardinalityEstimate in query plan on computed columns…

Hello,

I have come across a really good tip if you’re using computed columns that involve a concatenation of columns and conversions within the column.

Say for instance you are creating a computed column like the one below:-

CREATE TABLE dbo.SomeTable(
ID INT NOT NULL,
StartDate DATE NOT NULL,
EndDate…
Read more

Quick tip, ever needed to manipulate SQL on many lines in SSMS? now you can….

Have you ever had the need to select values that are directly on the next line down but drag-and-drop selects the entire line?

For example, if I need to copy all the highlighted values from the below SQL to form the basic of another statement:-

Dragging and dropping would select… Read more

Increasing database unit tests connection timeout period in VS 2012

Have your ever had a timeout error in a visual studio 2012 unit test like below?

How to get around this problem is to modify your unit testing app.config file to increase the “CommandTimeout” setting to the desired period of time (in seconds) see below:-

Run the test again and… Read more

A quick hint – evaluating 2 tables in one IF BEGIN END block

Have you ever wanted to evaluate 2 tables in one IF BEGIN END block, I firstly wrote this:-

Which always evaluated to 0, this shocked me so i rewrote as :-

Which gave me the outcome i needed.

Regards

Andrew

Read more

Generating a unique identifier outside of SQL and persisting it…

I’ve been thinking about a question from a application developer regarding generating a unique identifier outside of SQL and persisting it in SSQL. I have done a little research into it and have found 2 problem with GUIDs:
1. GUID is a 16-byte type while INT is a 4-byte type.… Read more

Generating a unique identifier outside of SQL and persisting it…

I’ve been thinking about a question from a application developer regarding generating a unique identifier outside of SQL and persisting it in SSQL. I have done a little research into it and have found 2 problem with GUIDs:
1. GUID is a 16-byte type while INT is a 4-byte type.… Read more

Using a system function as a computed column within a table-value function….

Wouldn’t it be nice if SQL Server allowed this functionality? for instance if did the below:-

I get the above error. I know the code can be easily rectified by doing:-

It just makes the code read bad and perform bad as you would need to add this system function… Read more

CDC changes column-by-column instead of row-by-row, can you improve it???

I’m writing a table value function that returns a cdc recordset that, instead of returning changed data row-by-row, I need to output them column-by-column.

Say for example I have a table called auth.Account with cdc enabled on fields Email and Password. e.g.

EXEC sys.sp_cdc_enable_table @source_schema = N’auth’,
@source_name = N’Account’,… Read more

Restore SQL Server databases remotely using my SQL CLR

Has anyone ever though of pushing a SQL Server database backup out remotely? (SQL Native andor RedGate Backups) My idea came from having to schedule SQL Server Agent jobs on the target servers to run a RedGate restore command to refresh our Development, QA and CI environments with a cleansed… Read more

SQL Training – Introduction to SQL Server Data Tools

Introduction

This document will provide a basic understanding on SQL Server Data Tools (SSDT) and its integration into Visual Studio 2012 Professional. The document will discuss the positioning, heritage its aims, and the meaning behind declarative database development. This document will give you a basic tour of Visual Studio 2012…

Read more

SQL Training – Introduction to SQL Server Data Tools

Introduction

This document will provide a basic understanding on SQL Server Data Tools (SSDT) and its integration into Visual Studio 2012 Professional. The document will discuss the positioning, heritage its aims, and the meaning behind declarative database development. This document will give you a basic tour of Visual Studio 2012…

Read more

Run real-time SQL tracing at application level with a ORM….

If you ever need to know exactly what dml your application is running as you have an ORM as your persistence layer like NHiberate. The most effective way is to add an application name parameter to your ado connection string and use SQL Server Profiler, to listen for this application,… Read more

Run real-time SQL tracing at application level with a ORM….

If you ever need to know exactly what dml your application is running as you have an ORM as your persistence layer like NHiberate. The most effective way is to add an application name parameter to your ado connection string and use SQL Server Profiler, to listen for this application,… Read more

My 1st Blog – out of memory exceptions in SSDT… yes its technical

When unit testing a stored procedure in visual studio 2012 (SSDT); sometimes you can get a out of memory exception (especially if you’re retrieving over 1 million rows+). To resolve this, go to the TEST\Test Settings\Default processing Architecture menu and select ‘X64′, this will give you a massive increase in… Read more

My 1st Blog – out of memory exceptions in SSDT… yes its technical

When unit testing a stored procedure in visual studio 2012 (SSDT); sometimes you can get a out of memory exception (especially if you’re retrieving over 1 million rows+). To resolve this, go to the TEST\Test SettingsDefault processing Architecture menu and select ‘X64′, this will give you a massive increase in… Read more