SQLService blog comments feed

Finding prime numbers with SQL server: Prime

Peter Larsson [swepeso@sqltopia.com]

It's about as easy as it can be written. No loops and no cursors. One insert and two selects that are unioned all.

Finding prime numbers with SQL server: Prime

Peter Larsson [swepeso@sqltopia.com]

Jag mailar dig Steinar min lösning.

0,2 s för alla primtal upp till 10 000.
0,4 s för alla primtal upp til 100 000.
4 s för alla primtal upp till 1 000 000.
35 s för alla primtal upp till 10 000 000.

Finding prime numbers with SQL server: Updated Standings: Daniel in the lead!

Steinar Andersen [steinar.andersen@spamprotected.sqlservice.se]

Daniel H's new improved solution is now execution in 12 seconds, great work :-)

Finding prime numbers with SQL server: Slightly optimized

Daniel Hutmacher [dhmacher [at] gmail.com]

I changed the manual looping into a SQL Server nested loop join. Here goes.. :)

http://pastebin.com/4rMjqifB

Finding prime numbers with SQL server: Having the numbers in a table is probably cheating so ...

Mikael Eriksson [micke314@gmail.com]

http://pastebin.com/sSWimLGC

Finding prime numbers with SQL server: Prime

Peter Larsson [swepeso@sqltopia.com]

Even simpler method to get the primes. Takes 2 sec to get all primes below 1 000 000.

CREATE

TABLE #Numbers

(

Prime

INTNOTNULL,

Number

BIGINTPRIMARYKEYCLUSTERED);

DECLARE

@Max INT

;

SET

@Max = 1000000

;

WITH

n0 AS (SELECT 1 AS p UNIONALLSELECT 1

AS (SELECT 1 AS p FROM n0 AS a CROSSJOIN n0 AS b),

AS (SELECT 1 AS p FROM n1 AS a CROSSJOIN n1 AS b),

AS (SELECT 1 AS p FROM n2 AS a CROSSJOIN n2 AS b),

AS (SELECT 1 AS p FROM n3 AS a CROSSJOIN n3 AS b),

AS (SELECT 1 AS p FROM n4 AS a CROSSJOIN n4 AS b)

INSERT

#Numbers

(

Prime

Number

)

SELECT

f.Prime

.Prime * f.Prime AS Number

FROM

(

SELECTTOP (1 + @Max / 6)ROW_NUMBER()OVER (ORDERBY p)FROM n5)AS v(Value)

CROSS

APPLY

(

VALUES (6 * v.Value - 1),

(6 * v.Value + 1

)

)AS f(Prime)

WHERE

f.Prime <= @Max

;

SELECT

Prime

FROM

(

VALUES (2),

)

)AS v(Prime)

WHERE

Prime <= @Max

UNION

ALL

SELECT

n.Prime

FROM

#Numbers AS n

WHERE

NOTEXISTS

(

SELECT*FROM #Numbers AS pWHERE p.Number <= n.PrimeAND n.Prime % p.Prime = 0)

DROP

TABLE #Numbers

;

Finding prime numbers with SQL server: My two pence.

Daniel Hutmacher [dhmacher [at] gmail.com]

Here's my take on the prime number algorithm, a T-SQL version of Eratosthenes' sieve:

http://dhmacher.wordpress.com/2013/03/11/a-prime-number-challenge/

Finding prime numbers with SQL server: Having the numbers in a table is probably cheating so ...

Mikael Eriksson [micke314@gmail.com]

http://pastebin.com/sSWimLGC

Finding prime numbers with SQL server: Prime

Peter Larsson [swepeso@sqltopia.com]

Same algorithm as last time, just adding more fixed numbers. Runs in 1,5 seconds for primes below 1 000 000.

CREATETABLE#Numbers

(

PrimeINTNOTNULL,

NumberBIGINTPRIMARYKEYCLUSTERED

);

DECLARE@MaxINT= 100000;

WITHn0(p)

AS (

SELECT 1

UNIONALL

SELECT 1

),n1(p)

AS (

SELECT 1

FROMn0ASa

CROSSJOINn0ASb

),n2(p)

AS (

SELECT 1

FROMn1ASa

CROSSJOINn1ASb

),n3(p)

AS (

SELECT 1

FROMn2ASa

CROSSJOINn2ASb

),n4(p)

AS (

SELECT 1

FROMn3ASa

CROSSJOINn3ASb

),n5(p)

AS (

SELECT 1

FROMn4ASa

CROSSJOINn4ASb

)

INSERT#Numbers

(

Prime,

Number

)

SELECTf.Prime,

f.Prime*f.PrimeASNumber

FROM (

SELECTTOP (1 +@Max/ 30)

ROW_NUMBER()OVER (ORDERBYp)

FROMn5

)ASv(Value)

CROSSAPPLY(

VALUES (30 *v.Value- 23),

(30 *v.Value- 19),

(30 *v.Value- 17),

(30 *v.Value- 13),

(30 *v.Value- 11),

(30 *v.Value- 7),

(30 *v.Value- 1),

(30 *v.Value+ 1)

)ASf(Prime)

WHEREf.Prime<=@Max;

SELECTPrime

FROM (

VALUES (2),

(3),

(5)

)ASv(Prime)

WHEREPrime<=@Max

UNIONALL

SELECTn.Prime

FROM#NumbersASn

WHERENOTEXISTS(

SELECT*

FROM#NumbersASp

WHEREp.Number<=n.Prime

ANDn.Prime%p.Prime= 0

)

DROPTABLE#Numbers;

Finding prime numbers with SQL server: Having the numbers in a table is probably cheating so ...

Mikael Eriksson [micke314@gmail.com]

http://pastebin.com/sSWimLGC

Finding prime numbers with SQL server: Prime

Peter Larsson [swepeso@sqltopia.com]

Here is another approach which runs for 2 seconds to find all prime numbers up to 1 000 000.

SET

NOCOUNT

Second Sieve of Atkin implementation

-- Clean up last session

DROP

TABLE #Primes

#Sequence

#Odd

#Even

-- Initialize user supplied parameter

DECLARE

@MaxPrime

BIGINT

SET

@MaxPrime = 1000000

-- Prepare helper variables

DECLARE

@MaxNumber

BIGINT

SET

@MaxNumber =SQRT(@MaxPrime

)

-- Create staging tables

CREATE

TABLE #Primes

(

Prime

INTNOTNULL,

Prime2

BIGINTNOTNULL)

CREATE

UNIQUECLUSTEREDINDEX IX_Primes ON #Primes(Prime2, Prime

)

CREATE

TABLE #Sequence

(

Seq

INTNOTNULL,

Seq2

BIGINTNOTNULL)

CREATE

UNIQUECLUSTEREDINDEX IX_Sequence ON #Sequence(Seq2, Seq

)

CREATE

TABLE #Odd

(

Seq

INTNOTNULL,

Seq2

BIGINTNOTNULL)

CREATE

UNIQUECLUSTEREDINDEX IX_Odd ON #Odd(Seq2, Seq

)

CREATE

TABLE #Even

(

Seq

INTNOTNULL,

Seq2

BIGINTNOTNULL)

CREATE

UNIQUECLUSTEREDINDEX IX_Even ON #Even(Seq2, Seq

)

INSERT

#Sequence

(

Seq

Seq2

)

SELECT

* n

FROM

(

SELECT 1+COALESCE(d.m, 0)+COALESCE(c.m, 0)+COALESCE(b.m, 0)+COALESCE(a.m, 0)AS nFROM (SELECTCAST(0 ASBIGINT)AS m UNIONALLSELECT 1 UNIONALLSELECT 2 UNIONALLSELECT 3 UNIONALLSELECT 4 UNIONALLSELECT 5 UNIONALLSELECT 6 UNIONALLSELECT 7 UNIONALLSELECT 8 UNIONALLSELECT 9)AS aLEFTJOIN(SELECTCAST(0 ASBIGINT)AS m UNIONALLSELECT 10 UNIONALLSELECT 20 UNIONALLSELECT 30 UNIONALLSELECT 40 UNIONALLSELECT 50 UNIONALLSELECT 60 UNIONALLSELECT 70 UNIONALLSELECT 80 UNIONALLSELECT 90)AS b ON @MaxNumber > 10AND b.m < @MaxNumberLEFTJOIN(SELECTCAST(0 ASBIGINT)AS m UNIONALLSELECT 100 UNIONALLSELECT 200 UNIONALLSELECT 300 UNIONALLSELECT 400 UNIONALLSELECT 500 UNIONALLSELECT 600 UNIONALLSELECT 700 UNIONALLSELECT 800 UNIONALLSELECT 900)AS c ON @MaxNumber > 100AND c.m < @MaxNumberLEFTJOIN(SELECTCAST(0 ASBIGINT)AS m UNIONALLSELECT 1000 UNIONALLSELECT 2000 UNIONALLSELECT 3000 UNIONALLSELECT 4000 UNIONALLSELECT 5000 UNIONALLSELECT 6000 UNIONALLSELECT 7000 UNIONALLSELECT 8000 UNIONALLSELECT 9000)AS d ON @MaxNumber > 1000AND d.m < @MaxNumberWHERE a.m < @MaxNumber)AS q

WHERE

n <= @MaxNumber

INSERT

#Even

SELECT

Seq

Seq2

FROM

#Sequence

WHERE

Seq2 % 2 = 0

INSERT

#Odd

SELECT

Seq

Seq2

FROM

#Sequence

WHERE

Seq2 % 2 = 1

INSERT

#Primes

(

Prime

Prime2

)

SELECT

* k

FROM

(

SELECT kFROM (SELECT 4 * a.Seq2 + b.Seq2 AS kFROM #Sequence AS aINNERJOIN #Odd AS b ON b.Seq2 < @MaxPrime - 4WHERE a.Seq2 <(@MaxPrime - 1)/ 4 )AS cWHERE k <= @MaxPrimeAND k % 12 IN(1, 5)UNIONALLSELECT kFROM (SELECT 3 * a.Seq2 + b.Seq2 AS kFROM #Even AS aINNERJOIN #Odd AS b ON b.Seq2 < @MaxPrime - 3WHERE a.Seq2 <(@MaxPrime - 1)/ 3UNIONALLSELECT 3 * a.Seq2 + b.Seq2 AS kFROM #Odd AS aINNERJOIN #Even AS b ON b.Seq2 < @MaxPrime - 3WHERE a.Seq2 <(@MaxPrime - 1)/ 3)AS cWHERE k <= @MaxPrimeAND k % 12 = 7UNIONALLSELECT kFROM (SELECT 3 * a.Seq2 - b.Seq2 AS kFROM #Even AS aINNERJOIN #Odd AS b ON b.Seq2 < a.Seq2WHERE a.Seq2 <(@MaxPrime + 1)/ 2UNIONALLSELECT 3 * a.Seq2 - b.Seq2 AS kFROM #Odd AS aINNERJOIN #Even AS b ON b.Seq2 < a.Seq2WHERE a.Seq2 <(@MaxPrime + 1)/ 2)AS cWHERE k <= @MaxPrimeAND k % 12 = 11)AS d

WHERE

k % 5 <> 0

GROUP

BY k

HAVING

COUNT(k)% 2 = 1

DELETE

FROM

#Primes AS p

WHERE

EXISTS

(

SELECT 1FROM #Primes AS xWHERE x.Prime2 <= p.PrimeAND p.Prime % x.Prime2 = 0)

INSERT

#Primes

(

Prime

Prime2

)

SELECT

FROM

(

SELECT 2 AS p, 4 AS p2 UNIONALLSELECT 3, 9 UNIONALLSELECT 5, 25)AS d

WHERE

p <= @MaxPrime

-- Display the prime numbers

SELECT

Prime

FROM

#Primes

Finding prime numbers with SQL server: I am beaten :-)

Steinar Andersen [steinar.andersen@spamprotected.sqlservice.se]

Both your solutions finds the primes up to 100000 in about 2 seconds, where my solution takes 1 minute and 17 seconds. Hard to draw any other conclusion than that I loose and you win :-)

To see wich one of your solutions is the best (accusations about cheating and doping aside ;-)) I upped the contest to finding the primes up to 1.000.000

And the winner (so far...?) is: Mikael Erikson with 20 seconds!

Daniel Hutchmacher comes in a good second at 21 seconds.

I wonder if that can still be beaten??

Finding prime numbers with SQL server: Peter Larsson in the lead!

Steinar Andersen [steinar.andersen@spamprotect.sqlservice.se]

I have verified Peter Larssons latest code to give the correct primes up to 1.000.000 on my server in 4 seconds.

Great work Peter :-)

Ny uppdatering för SQL Server 2012 SP1: Nu är buggfixen släppt

Henrik [henrik.pettersson@preem.se]

http://support.microsoft.com/kb/2832017

Ny uppdatering för SQL Server 2012 SP1: Problem med hotfix sp CU3

Henrik [henrik.pettersson@preem.se]

Detta kan vara bra att känna till...

Om man installerar SQL server SP1 CU3 på en dator med VisualStudio/SSDT så kan man inte öppna SSIS-projekt längre på den datorn.

Så installera inte den…

Hotfix ska finnas via MS.

http://connect.microsoft.com/SQLServer/feedback/details/781921/ssis-projects-and-maintenance-plans-do-not-work-after-applying-cu3-for-sql-server-2012-sp1

Kortet som sätter fart på SQL Server: Fusion iO kort

Henrik [henrik.pettersson@preem.se]

Ta en titt på korten från Fusion iO iställe. Jobbar med minnescache istället för SSD.

http://www.fusionio.com/products/iodrive2/

Fun with SQL Server sys.messages: sql supersite

Jonas [jb300@hotmail.com]

ett till intressant inlägg, helt i Paul "windowssupersite" Thurrots anda. cloudDb alltså, hade föredragit det framför Azur.

Fun with SQL Server sys.messages: sql supersite

Jonas [jb300@hotmail.com]

ett till intressant inlägg, helt i Paul "windowssupersite" Thurrots anda. cloudDb alltså, hade föredragit det framför Azur.

Värdet av certifieringar: .

Patrik [patrik.sandqvist@labcenter.se]

Helt rätt! Bra skrivet!

A SQL Azure tip a day (18) - My Laptop is faster than SQL AZURE ?!?:

Peter [swepeso@sqltopia.com]

Vad man kan opponera sig mot i det här testet är användandet av NEWID() isstället för NEWSEQUENTIALID(). Det skulle kunna visa sig att NEWID() på Azure genererade mycket mer page splits än på den lokala datorn. För att eliminera sådana eventuella felkällor borde NEWSEQUENTIALID() används istället för NEWID(). Dessutom bör inte det klustrade indexed ligga på GUID kolumnen alls, utan Int_Col kolumnen för att vara säker på att alla inserts hanteras på samma sätt, men det är en petitess.

A SQL Azure tip a day (18) - My Laptop is faster than SQL AZURE ?!?: can't find the second article

John [john@yahoo.com]

I am trying to read the followup post, but I get the error:

You requested the page '"/sv/start/blogg/sqlazurefollowup.aspx"' and that specific page cannot be found.

The resource might have been moved or is temporarily disabled or you do not have access to the resource.

Is it removed?

Change tracking for the non enterprise licensee:

anonymous [none]

But then you have to pay $$$ for a consultant that have to write the logic to get the data from the table as well as having the overhead of running two queries.

The maximum system-generated unique value for a duplicate group was exceeded : Rebuild?

Peter Larsson [swepeso@sqltopia.com]

Att bygga om det klustrade indexet skapar inte en ny uniqifier, bara RID-en

Undrar om en explicit DROP index, GO, och CREATE index hade fungerat bättre?

Byta databasnamn: Man kan även använda With Move som koden nedan

Mårten Rune [marten@prosql.se]

RESTORE

DATABASE [YourDB]

FROM

DISK=

'A:\NyDisk\And\Path.bak'

WITH

MOVE

'YourDBLogicalFilenameDATA'TO'D:\esired\Disk\And\Path\Altered.mdf'

MOVE

'YourDBLogicalFilenameLOG'TO'D:\esired\Disk\And\Path\Altered_log.LDF'

STATS

=5,

RECOVERY

Byta databasnamn: Logiska filnamnen behöver inte vara unika på en sql-instans

Thomas [thomas@rydberg.org]

Men däremot det fysiska filnamnet (sökvägen) måste vara unikt.

Check progress of rollback : Other useful columns

Levi W [control_station@hotmail.com]

Add these columns to the SELECT statement for additional information:

, start_time

, total_elapsed_time / 1000.0 / 60.0 AS minutes_elapsed

, ((total_elapsed_time / 1000.0 / 60) * (100 / percent_complete - 1)) AS est_minutes_remaining

, DATEADD(N, CAST(((total_elapsed_time / 1000.0 / 60) * (100 / percent_complete - 1)) AS INT), GETDATE()) AS est_time_of_completion

Proposed improvement in T-SQL JOIN syntax: Natural Join

Peter Larsson [swepeso@sqltopia.com]

En oönskad bieffekt av en Natural Join enligt ovan är att joinen kan slå fel om man lägger till en kolumn eller väljer en annan datatyp.

Se http://en.wikipedia.org/wiki/Join_(SQL)

Multiple nulls in a unique constraint:

[]

SQL Server statistics - read only database:

[]

SQL server statistics - table variables:

[]

SQL Server statistics - multicolumn statistics:

[]

SQL Server performance - death by power options:

[]

Veckans fråga med svar vecka 38: fofcjfZlzmUda

Farid [madison@phas.ubc.ca]

Hej igen!Torsdag 8 juli omkr kl 19 se5g vi pe5 marinetraffic.com att Shaddock le5g stlila 0 grader lite norr om Rf6nne pe5 Bornholm, sedan senare pe5 kve4llen och nu pe5 morgonen 9 juli kl 9 hittar vi ej ne5gon lila symbol med Shaddock ? Ligger ni kvar vid Bornholm?Ha det bra!Marianne o Janne

Veckans fråga med svar vecka 38: FAJsfmfrXPEtHdNL

Masanori [newyear@adcipare.com]

Mulet hela dagen, e5 kallt har det varit, se4kert bara 21 grader.Fick ni mlejet fre5n Lasse? Kan ju vara bra e5 veta om det har kommit fram till er.Tre4ffade Niklas en snabbis idag pe5 mitt jobb.kram Annika

Keeping track of Replication statistical data using Extended Events: uJFoOXwCRGeLFlKk

Ivan [canonada@canonada.pl]

Nice move Charlie. I'm all about MySQL these days. I still use MS SQL in a large enterprise eovirnnment at work but that's more because that's "what we know." MySQL can handle enterprise apps these days too and I hope to dip more into using MySQL in a HA eovirnnment.

SQL server - kolumn alias best practice: qFNFVRfQiWM

Samir [arman@physics.ubc.ca]

I did find a kink in using the filter mhoted and that is not a fault of CQRlog. The SKCC membership database consists of the callsign and the SKCC number. I found a few QSOs were missing using the filter mhoted as the membership file has not been updated upstream. That's not a fault of CQRlog but of the SKCC member who has not had SKCC update his call in the file.So it goes

SQL server - kolumn alias best practice: SQL server - kolumn alias best practice

Christian Janssen [christian.janssen@hm.com]

Hej, bra artikel!

Jag instämmer helt och använder alias som i CASE exemplet var det än går.

En förra kollega tyckte inte om notationen så mycket eftersom det ser ut som en tilldelning, den liknar jo "SELECT @myVariable = customerID FROM dbo.bbb" mycket. Kanske värt att tänka pp att man inte formaterar sådana rader på samma sätt.

Guest Blog: Performance Tip - Transactions Can Actually Boost Throughput: SWCYeiXFlkdgXX

Mildred [info@gammacarri.com]

you uncheck the menegamant tools-basic but check the other options (Database Engine being the critical one), then you will have SQL Server installed and running. You just won't have the SQL Server Management Studio GUI tool for managing the database. So you'll have to create and access the databases in other ways (for example, programmatically with C# or some other language or another database tool).

Dubbla index ?:

Andrey [info@businessofsoftware.org]

Dubbla index ?: PGHYomxDPueC

Andrey [info@businessofsoftware.org]

THX that\'s a great aswenr!

SQL server indexing strategies part 3 - non clustered indexes: UHEaVbZOeUjpfUEy

Yang [sandy.quinlan@wybusiness.org]

Hi,Great video! Your services too are rellay great! You provide a nice range of services and I'm sure many people could use your services. I have used you in the past, the work was done quickly and efficiently and still done to an escellent level of expertise! For the price, this is an amazing offer as the work you did was worth so much more. I would highly recommend this service to anyone with computing needs. Check out their main site for more info milesconsultingcorp . com.

SQL Server - cast string into date vs datetime: länk

Madhivanan [madhivanan2001@gmail.com]

Also refer this http://beyondrelational.com/modules/2/blogs/70/posts/10898/understanding-datetime-column-part-ii.aspx

Get CPU utilization by database: Fick ett fel

Tobias [tob_mo@yahoo.com]

Fick ett felmeddelande när jag körde skriptet...

(2 row(s) affected)

(3 row(s) affected)

Msg 8134, Level 16, State 1, Line 47

Divide by zero error encountered.

SQL server 2012 certifiering: BItsSwfviJIAFqVCCNg

zbnaele [mpluaq@wixlyh.com]

T6tQqX fbuqopvnxzle

SQL Server Indexing strategies part 2 - clustered index: DlGJZduPenldmOBP

Lola [klub.pastelka@email.cz]

Great cheat sheet you have put together here. Sometimes it is more hlfpeul to new SQL users to see an explanation in this format than reading the books online pages. Well done.

SQL Server Indexing strategies part 2 - clustered index: DpppocMYtuNSlG

Lucas [jjacobson@nsp.org]

Thanks for the post.a0 It helped cfnriom a problem we are having.a0 We have some old classic ASP code that is using ADO.a0 The programmer who wrote the code was using SCOPE_IDENTITY to get the IDENTITY used on a previous insert.a0 He was basically issuing a dynamic SQL statement and putting the result back into a ADO recordset.a0 This method worked for years on SQL 2000.a0 We just recently migrated this app to SQL 2005 and this broke.a0 The problem was the recordset was now getting a NULL value.a0 It is our thinking that the change in SCOPE_IDENTITY in 2005 doesn\'t mashup with old ADO that well.a0 So, rather than doinga0rsSave.Open "SELECT SCOPE_IDENTITY() AS \'EntryID\' ",cnTime,adOpenStatic,adLockReadOnlya0We are now doinga0rsSave.Open "DECLARE @RecID int; SET @RecID = SCOPE_IDENTITY(); SELECT @RecID AS \'EntryID\' ",cnTime,adOpenStatic,adLockReadOnlya0This is now getting the recordset populated with the IDENTITY.a0 Interestingly enough, if we did a CAST to INT on the original statement, that did not help.a0a0

Guest Blog: Keeping track of SQL Server Agent job durations: scgSJrrAWIjLXNmJ

Fadime [marketing@civicplus.com]

I think you’ve made some truly interesting ponits. Not too many people would actually think about this the way you just did. I’m really impressed that there’s so much about this subject that’s been uncovered and you did it so well, with so much class. Good one you, man! Really great stuff here.

Resource Governor Lite with QUERY_GOVERNOR_COST_LIMIT ?: cHFyMHxLYzl

Melissa [m.l.vdrmeer@kub.nl]

Brent,I think I am missing sitnmhoeg (trying not to be thick-headed), per the To Fix the Problem it states, In SQL Server Management Studio, you can right-click on each database and go into its properties to change Auto Update Stats Asynchronously to Enabled. which seems to be contradictory to the setting that you are looking for, and also conflicts with the blog you have linked to learn more.Again, unless I am missing sitnmhoeg it seems like all the information provided states to turn this option ON , but the blitz proc is looking for where it is on and warning to turn it off.Thanks for your patience and help in walking me through this. Glenn Berry was the original person that told me to turn it on, so I have made it part of the database policies to check that it is

Is Dynamic T-SQL a Good Design Pattern?: http://scarydba.com

Grant [grant@scarydba.com]

If you need to dynamically sort, you're stuck with dynamic TSQL or multiple different stored procs. No other reliable methods that I'm aware of.

Is Dynamic T-SQL a Good Design Pattern?: fpfJTfJloBHFguBNO

Marco [beereq@mail.ru]

I am new to “this.” Since I am coming from a Microsoft SQL Server envrionment, and to some other it may seem like the two versions should be very similar, and they are, to a certain degree, but they are also very different in some important and basic ways. Good read, thanks!

Veckans fråga med svar - vecka 18: Partitioner

Peter [peso@developerworkshop.net]

Det är inte bara SQL Server 2012 som stöder 15000 partititoner. Även SQL Server 2008 och SQL Server 2008R2 stöder 15000 partitioner med Service Pack 2.

Se http://blogs.msdn.com/b/hanspo/archive/2010/11/29/new-limit-for-number-of-partitions-in-sql-server-2008-sp2.aspx

Veckans fråga med svar - vecka 14:

Magnus Ahlkvist [magnus.ahlkvist@vansterpartiet.se]

Grant Fritchey har skrivit en bok som heter Dissecting SQL Server Execution Plans. Den kan laddas ner gratis som PDF från SQL Server Central: http://www.sqlservercentral.com/articles/books/65831/?utm_content=Grant080623

How to redirect SSRS URL: dwyjAfHXPiVuqju

Laraztsatie [siri_urz@hotmail.com]

It's a pleasure to find someone who can idntefiy the issues so clearly

Veckans fråga med svar - vecka 12: Good info

Pharme948 [johne610@aol.com]

Very nice site!

The eternal search for missing indexes, part 2:

Niclas [nic1980@hotmail.com]

There is a third solution: usually when you see an index suggestion which has almost all columns as includes, this is a clear sign that you might have the clustered index on the "wrong" column(s). (Clustered index on FK instead of PK might increase performance 100 times... ) Also, what your script does not suggest is whether the indexes should clustered or non-clustered. It is a bit tricky to apply suggestions from DMVs without actually thinking about the design and testing the indexes away from production environments.

Veckans fråga med svar - vecka 11:

Jens Nilsson [jens@spelkultur.com]

Greate post

Få SQL Server hjälp med #sqlhelp:

Jason Crider [arsqldba@gmail.com]

Why not create a list that others can follow? Alot of these people have lists created as well so maybe you can look at that. Made my life easier. :-)

Få SQL Server hjälp med #sqlhelp:

Steinar [steinar.andersen@sqlservice.se]

Hello Jason! I actually did that, and you can find that list at https://twitter.com/#!/SQLSteinar/sql-must-follows Regards Steinar Andersen

SQL server performance - Death by correlated subqueries:

Jens Nilsson [Jens@spelkultur.com]

Nice example

SQL server performance - Death by correlated subqueries: bKneiJLsuojxgaPsGOW

Joanna [lencaberuska@seznam.cz]

Brian beat me to it Growing Devices. The second I read this in my redaer, I clicked on it to come over and talk about growing a database.That headache rises up there. So do table corruption troubleshooting issues and the lovely server settings configuration screen(s?.. I actually just remember the settings setting process, not the exact dialog)

SQL server - Auto update stats part 1:

SQL server – Auto update stats part 2 « SQL Service – Specialiserade Microsoft SQL Server konsulter []

[...] The last week I posted a blog about auto update stats and this is a sequal to that blog post. I case you haven’t read it you should read it first. [...]

Mirroring related Error: 3624, Severity: 20, State: 1: ovvSMUcUYFKalN

Aya [juergensenm@t-online.de]

That's an astute answer to a tricky qeutsion

A SQL Azure tip a day (19) - High Availibility: vNTnUYJQAGcUFvrwQ

Mahesh [m.steatasholn@gmail.info]

Awoemse you should think of something like that

SET OPTIONS:

Erland Sommarskog [esquel@sommarskog.se]

Annars funkar DBCC USEROPTIONS...

A SQL Azure tip a day (14) - Migration: lBBbnlLBxJjIIWh

Andres [uvbopd@qdkjdg.com]

Good point, wrapping the names in bceakrts would avoid issues. I always avoid naming my tables such that it would be a problem, makes life ever so much easier but one cannot count on others to do the same.

SQL Performance - Death by non searchable argument:

Johan [johan@idstam.se]

It's cludge but I've got a solution for when the customer wants '%xxx%' really bad. Duplicate the field you want to search on but reverse the string in the copy. That way you can use: where a like 'abc%' or b like 'cba%' OR even better; use any full text search engine.

SQL Performance - Death by non searchable argument:

hawi [hakan.winther@sqlservice.se]

Hi Johan, nice to hear from you again! You are right and you can read more about the solution in the SQL server Deep Dives book in a chapter written by Erland Sommerskog Regards /Håkan Winther

Julbonus: Foldrar för mätvärden i tabular model: oDGfBsTODCyy

Chandan [lara.andrade@bbcr.com.br]

At last, smooene who comes to the heart of it all

Komma igång med RC0 (SSAS):

Jesica [richard.jordi@uct.ac.za]

Komma igång med RC0 (SSAS): FcXgqGGfIiInKHG

Jesica [richard.jordi@uct.ac.za]

This is a neat saummry. Thanks for sharing!

DMV och felsökning/prestandaoptimering: NypZiGgPohqxplVVqQX

Mercy [georges@monaco.net]

I want to see results in table so i m peirnrmfog following stepsafter clicking on View >Options >data grids -visual it shows me message as follows The following files specified on the executables tab could not be foundTNS PINGPing. i m using toad 7.3.0.0

DMV och felsökning/prestandaoptimering: EZGKgrftYbPCSU

Caramelo [claudio.alessio@gcp.ch]

Another difference from 10g is that plan_table is a real table rhater than a global temporary table.Don't you mean that 10g plan_table is global temporary and not real table?

Database Mirroring Timeout: pWPrTkrvTn

Adheka [tvmuseum@silverstar.com]

Thanks for sharing. Your post is a useful contrbiution.

Datawarehouse i SQL server - komprimering av index: HeTTSpYzlGmNFN

Mitchel [mjandrew@gocumbria.org]

Awesome you should think of smotehnig like that

SQL server performance - death by bad statistics:

SQL server – Auto update stats part 1 « SQL Service – Specialiserade Microsoft SQL Server konsulter []

[...] is a common problem when the statistics are out of date and you can read more about the problem at my earlier blog post, but what about auto update stats. Doesn’t it work? Well it does, the statistics are updated [...]

SQL server performance - death by bad statistics: test

test [test@test.com]

test

SQL server performance - death by bad statistics: rzOaLLHCRYZRvnFzNR

Mesi [admin@fungamercrew.de]

Yesterday I was attaching a daaabtse but SQL Server is throwing out an error (compression issue). I asked for help at twitter, and the tweeps sent me to various online resources (I focused on Paul Randal's blog). Research, read, Research, read test research read test I was running out of clues!Then I came across a forum post that suggests I check the data file's properties and see if it is marked Compress contents to save disk space (I unticked it). That solved the issue. I ran DBCC CHECKDB and the daaabtse was ok! Talking about overlooking

AS sp_grant_proxy_to_subsystem documentation error: ogJdZavTlql

Abm [scatio@gmail.com]

Police Car Auctions This design is indebcirle! You certainly know how to keep a reader amused. Between your wit and your videos, I was almost moved to start my own blog (well, almost HaHa!) Excellent job. I really loved what you had to say, and more than that, how you p

Force SQL Server Mirroring on specific IP Network!: qxRqzuIOmDEGFJ

Omar [steve_vanrooy@sil.org]

That's a slick answer to a chalenlging question

Force SQL Server Mirroring on specific IP Network!:

Alterando Database Mirroring para utilizar uma placa de rede dedicada | Vladimir M. B. Magalhães – Learn and Share []

[...] http://sqlservice.se/sql-server/sql-server-maintenance/force-sql-server-mirroring-on-specific-ip-net... Gostar disso:GostoSeja o primeiro a gostar disso post. Esta entrada foi publicada em Artigos, Virtual PASS BR. Adicione o link permanente aos seus favoritos. ← Feliz Natal 2011 [...]

Force SQL Server Mirroring on specific IP Network!:

Omar [steve_vanrooy@sil.org]

Ny version av SSMS Toolspack: FXVGDKiHAWBFya

Misty [w.slebradat@apache.jp]

Pleasing you sholud think of something like that

Filsystemfil, som rad i tabell - FileTable i Denali CTP3 (SQL 2012): jNirvVCfWa

Jorge [thais.araujo@bbcr.com.br]

SQL Server is a database? Are realtional Microsoft product. You k? Can not drive led SQL Server? a site?. In the world w Microsoft? You rate using IIS (Internet Information Services) as an alternative? Apache. IIS is part of a Windows Server can Versionen.SQL? Be used? to collect data? es that are used?'re keeping g? n? rer dynamic web pages can k?, but you m? Still need IIS for h? Shepherd the site.

PASS Summit 2011:

Feodor [feodor.georgiev@sqlservice.se]

Bra inlägg, Göran. Jag undrar bara om Adams session finns nånstans på nätet så att vi kan titta på den? Den största problemet med Hash matches och sorteringar är inte att de tar minne. Största problemet är att eftersom hash matchen har allokerat sitt minne, den kan inte allokera ytterligare minne om det räcker inte och det innebär att tempdb kommer att användas för at slutföra operationen. Och det, på sin tur, innebär att hash matchen kommer att 'spelas' på en långsamare terräng (disk systemet). :) Kan du blogga extra på detta ämne, please? Också, om hur parallelismen tar för mycket minne? MVH, Feodor

SQLUG event 6/10 2011- SQL Azure: uzaeAuOIzWLq

Elizita [iletisim@alkagroup.com.tr]

That's really thiknnig at a high level

PASS Summit 2011 - Dag 3 : iVeBsOHlzDDbxKR

Ezel [network@ranch.net.au]

That's a knowing aneswr to a difficult question

PASS Summit 2011 - Dag 3 :

Ezel [network@ranch.net.au]

Query options and CTEs:

Feodor Georgiev [feodor.georgiev@sqlservice.se]

Thank you for reading my post, Magnus! I am happy to see that someone reads what I write. :) You are right about the FASTFIRSTROW, but in the case I need it for it won't work, since I am processing 10000 row batchs in my SSIS and my idea is to start processing the first 10000 as fast as possible, so I don't waste time in my SSIS package. I am quite curious, though, why some of the query options work with CTEs while others don't.

Query options and CTEs:

Magnus Ahlkvist [magnus.ahlkvist@vansterpartiet.se]

I don't know if it helps at all in your case, but you could use table hint FASTFIRSTROW in the subquery. There are two major concerns with using FASTFIRSTROW. 1) It will be removed in Denali (http://msdn.microsoft.com/en-us/library/ms187373.aspx) 2) It is the equivalent of using FAST n with n=1 and you can't change n. Anyhow, this is how it would look: ;WITH cte (EmployeeID, ManagerID, Title) as ( SELECT EmployeeID, ManagerID, Title FROM dbo.MyEmployees FASTFIRSTROW ) SELECT EmployeeID, ManagerID, Title FROM cte

Query options and CTEs:

Magnus Ahlkvist [magnus.ahlkvist@vansterpartiet.se]

None of the query hints work, all the table hints work. But WHY that design was chosen... I have no idea :)

Backup With COPY_ONLY:

Feodor [feodor.georgiev@sqlservice.se]

SQL Server Management Studio 2005 does not support the COPY_ONLY backup, hence it is only through script that we can run this. In 2008 and up there is a Management Studio option for this. /Feodor

SQL Server Collation ?:

Håkan Winther [hakan.winther@sqlservice.se]

I SQL Azure så är default collation SQL_LATIN1_GENERAL_CP1_CI_AS och det går inte att ändra varken på server eller databasnivå. Behöver du en annan collation så måste du sätta det på kolumnnivå.

Moving TempDB in an easier way: VDixiBlPLgiXpeJw

Leota [krasnal7171@tlen.pl]

Mark,The "cms.schema_scripts" package shuold contain your scripts, named according to the spec of "migrationXXXXXX.cfc" or "migrationXXXXXX.sql", depending on whether you're using CFC or SQL-based scripts. There MUST be exactly six digits in each filename, and they MUST be numbered sequentially starting with one (or 000001).If you've set up your mappings correctly, the demo shuold definitely work, so I'm not sure what's up. The easiest way to check your mapping is to do an expandPath on it (#expandPath('/cms/schema_scripts')# for your example above), and then pass that to CFDIRECTORY and see what files it returns. It shuold return the list of migration scripts. If not, you're misconfigured somewhere.Hope that helps.

Order by with CASE, not always what you expect:

Håkan Winther [hakan.winther@sqlservice.se]

When you use the CASE in order by, you are sorting by an expression and not the column number from the result of the expression. The sql statement will give you the same result as: DECLARE @OrderBy char(1) SET @OrderBy = 'ß' SELECT ß,Σ, CASE WHEN @OrderBy = 'ß' THEN 1 END AS case1, CASE WHEN @OrderBy = 'Σ' THEN 2 END AS case2, CASE WHEN @OrderBy NOT IN ('ß','Σ') THEN 2 END AS case3 FROM dbo.Φ ORDER BY case1, case2, case3 And in this case you get the same order as the data was inserted, but that is not guaranteed.

Validate swedish personnummer in the database:

Christer [janchrister.nilsson@gmail.com]

Indenterat korrekt, hoppas jag: checkPersonNummer = (pnr) -> ..return false if pnr.length != 10 ..check = 0 ..for i in [0..9] ....d = parseInt pnr[i] ....d = d+d if i % 2 == 0 ....d -= 9 if d > 9 ....check += d ..check % 10 == 0

Validate swedish personnummer in the database:

Validate swedish personnummer in the database:

Christer [janchrister.nilsson@gmail.com]

Något enklare i Coffeescript. Dessutom kan kontrollen ske direkt på klienten. checkPersonNummer = (pnr) -> return false if pnr.length != 10 check = 0 for i in [0..9] d = parseInt pnr[i] d = d+d if i % 2 == 0 d -= 9 if d > 9 check += d check % 10 == 0 assert checkPersonNummer('5511300115'), false assert checkPersonNummer('5511300112'), true

Validate swedish personnummer in the database:

Magnus Ahlkvist [magnus.ahlkvist@vansterpartiet.se]

Här en funktion utan loopar. Förutsätter en nummertabell i databasen. En sådan kan skapas ganska enkelt med: SELECT top 11000 identity(int,1,1) as N into #t FROM sys.all_columns c1 cross join sys.all_columns c2 alter table #t add constraint PK_t PRIMARY KEY (n) Och här själva koden för personnummerkontroll. CREATE FUNCTION dbo.CheckPersonnummer(@personnummer varchar(12)) RETURNS bit AS BEGIN DECLARE @Return bit SET @Return=1 DECLARE @s varchar(10) IF NOT DATALENGTH(@personnummer) IN (10,12) SET @Return=0 ELSE SET @s = RIGHT(@personnummer,10) IF @Return 0 BEGIN DECLARE @PersonnummerModulu2 varchar(20) SET @PersonnummerModulu2=” SELECT @PersonnummerModulu2 = @PersonnummerModulu2 + CAST(CAST(SUBSTRING(@s,N,1) as int) * (1 + N % 2) as varchar(2)) FROM dbo.Nummer WHERE N <10 DECLARE @SifferSumma int SET @SifferSumma=0 SELECT @SifferSumma=@SifferSumma + CAST(SUBSTRING(@PersonnummerModulu2,N,1) as int) FROM dbo.Nummer WHERE N <= DATALENGTH(@PersonnummerModulu2) IF CAST(SUBSTRING(@s,10,1) as int) 10 – @SifferSumma %10 SET @Return=0 END RETURN @Return END Slutligen en för numeriska personnummer. CREATE FUNCTION dbo.CheckNumericPersonnummer(@personnummer bigint) RETURNS bit AS BEGIN DECLARE @Return bit DECLARE @s varchar(20) SET @s = CAST(@personnummer as varchar(20)) IF DATALENGTH(@s) NOT IN (10,12) SET @Return=0 ELSE SET @Return = dbo.CheckPersonnummer(@s) RETURN @Return END

Säg adjö till DTS: NWSFLdnLMdTg

Samir [biuro@icar.pl]

zipped the .cfm file that I used and added it to this entry (you should see a dowanold' link by the comments', print', etc).Again, I stress that this isn't an elegant solution nor is it one that I'm particularly proud of. But if you're using BlogCFC, and another, more elegant method doesn't come up, this might save you a little bit of time. Obviously, edit as needed.

Rätt hårdvara för SQL Server ?: RKeeVrYdYyHhU

Halima [danielle.costa@bbcr.com.br]

Taking the ovvreiew, this post is first class

Problem med installation av Denali CTP3 ?:

Amar Kadenic [Mr.kad@hotmail.com]

Hej Det finns en KB artikel om hur man bygger om Performance Counter. Har hjälpt mig en hel del. http://support.microsoft.com/kb/300956 // Microsoft

SQL server Denali features: EOMONTH():

Michael [michael.schonning@payzone.se]

Shouldn't the second SQL-statement be "SELECT EOMONTH ( @date, -1 ) AS Result;"?

SQL server Denali features: EOMONTH():

hawi [hakan.winther@sqlservice.se]

Yes you are right, it was a copy and paste error. Thank you for noticing.

SQL server Denali features: EOMONTH():

Michael [rosquistm@yahoo.com]

/* CREATE FUNCTION dbo.Month_Last_Day (@Date datetime) RETURNS datetime AS BEGIN RETURN(DATEADD( day, -1, STUFF(CONVERT(char(11), DATEADD( month, 1, @Date)), 5, 2, '01' ))) END */ select dbo.Month_Last_Day ('20110907')

SQL server Denali features: EOMONTH():

Michael [rosquistm@yahoo.com]

You can always roll your own function. SELECT DATEADD(dd, CAST(DATEDIFF(DAY,'17530101',CURRENT_TIMESTAMP) AS int) - DAY(CURRENT_TIMESTAMP), '17530101') as LastDayPreviousMonth

SQL server Denali CTP3 - Löpande summering: agBiCvBzItpyp

Karen [admin@searchcamp.eu]

It's great to find an expert who can explain tnhigs so well

Database Mirroring och SQL login:

Richard Hillström [richard@qreed.se]

Eller så kör man nånting sånt här :-) select 'create login [' + p.name + '] ' + case when p.type in('U','G') then 'from windows ' else '' end + 'with ' + case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_policy = ' + case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_expiration = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end + case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end else '' end + 'default_database = ' + p.default_database_name + case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end from sys.server_principals p left join sys.sql_logins l on p.principal_id = l.principal_id left join sys.credentials c on l.credential_id = c.credential_id where p.type in('S','U','G') and p.name 'sa' order by p.name

Database Mirroring och SQL login:

Robert L Davis [chrandrob@comcast.net]

Jag skrev en skriva för att behandla denna som väl: http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror

Behaga nåden min fattiga svenska expertis!

Troubleshooting SQL Server (re)compiles due to SET Options:

Bob Evans [links@ciquery.com]

A very useful tip , thank you. An interesting article at : http://www.sqlserver-dba.com/2011/05/stored-procedures-versus-ad-hoc-paramaterized-querieswhich-is-faster.html . Also a similar problem with ALTER

Troubleshooting SQL Server (re)compiles due to SET Options: XyvauJuIWdyTHghDgYi

Leonardo [ulianna12345@mail.ru]

Thanks for sharing. Your post is a ufsuel contribution.

Dynamisk SQL och vikten av statistik: WpVmzmjMRGXsRZr

Pebbles [ctkrn@custertel.net]

credit card kiwy doxycycline 250678 flight cheap ygjv

Svensk mästare i SQL Server programmering!: kImcKKMEruNNU

Martha [jmflavier@pacific.net.ph]

credit cards xolbz online colleges gmkvo infinity auto insurance :((

SQL Server compile and recompile demystified (?): Good info

Pharmg354 [johng483@aol.com]

Very nice site!

Remove individual plan from cache:

Håkan Winther [hakan.winther@sqlservice.se]

Nice! (but what happend to sp_recompile @object_name) :)

Remove individual plan from cache:

Jonas [jonas.bergstrom@sqlservice.se]

Good ol' sp_recompile is still there. I guess I should've been more precise in specifying what I meant by a query. I meant a specific connection run instance of a query. With it's own SET settings. For example NOCOUNT ON compared to not having set NOCOUNT ON. Probably just in ad hoc you should see those differences though. And SQL Server also recompiles when it's "advantageous" to do that.

Lista SQL login och dess egenskaper:

Feodor [feodor.georgiev@sqlservice.com]

Håkan, here is your script refurbrished to make it compatible with different collations (CS_AS): SELECT sp.name, sp.[default_language_name], sp.[default_database_name], PasswordLastSetTime = LOGINPROPERTY(sp.NAME,'PasswordLastSetTime'), BadPasswordCount = LOGINPROPERTY(sp.NAME,'BadPasswordCount'), IsLocked = LOGINPROPERTY(sp.NAME,'IsLocked'), LockoutTime = LOGINPROPERTY(sp.NAME,'LockoutTime'), DaysUntilExpiration = LOGINPROPERTY(sp.NAME,'DaysUntilExpiration'), IsExpired = LOGINPROPERTY(sp.NAME,'IsExpired'), sp.[create_date], sp.[modify_date] FROM sys.server_principals AS sp WHERE [type] = 'S' ORDER BY sp.[name]

Case study: DW - optimera dynamisk SQL: jsvVUmlYrWs

Supercinema [aheslop@wan.asso.fr]

Fantastic items from you, man. I've understand your stuff pvoeirus to and you're simply extremely fantastic. I actually like what you've bought here, certainly like what you're stating and the way in which wherein you say it. You're making it entertaining and you continue to take care of to keep it smart. I can't wait to read much more from you. That is really a great web site.

Case study: DW - optimera dynamisk SQL: rYhGlrZXacxox

Irfanbhai [info@zuvedra.com]

hello there and thanks to your intormafion ? I've definitely picked up something new from right here. I did then again experience some technical points using this website, since I experienced to reload the site many occasions prior to I may just get it to load correctly. I had been puzzling over if your web host is OK? Now not that I'm complaining, but slow loading instances times will very frequently affect your placement in google and could harm your quality rating if ads and marketing with Adwords. Anyway I am adding this RSS to my email and could look out for a lot more of your respective exciting content. Make sure you update this once more very soon..

Nu är det hög tid att uppgradera!: tPHoCIvfAIAUfywDAqI

Dwi [filipe.amaral@bbcr.com.br]

It's a pleasure to find someone who can iednitfy the issues so clearly

Udda beteende vid skriptladdning i SSMS:

hawi [hakan.winther@sqlservice.se]

Ett sätt för att hålla koll på vilken server man kör mot är att ladda ner SSMS Tools Pack från www.ssmstoolspack.com som har "connection coloring". Med connection coloring så ser man väldigt tydligt vilken connection som query fönstret tillhör. (förslagsvis rött för produktion). /Håkan Winther

Merge Join ?: yJUfLIWKaQYaqRs

Jessica [ray@raydevries.co.za]

I was rellay confused, and this answered all my questions.

Maxpoäng på certifiering: MCTS SQL server 2008, Database development:

hawi [hakan.winther@sqlservice.se]

Grattis Göran!

Nya hotfixar för SQL Server: PqdcOrOKiBkXV

Isis [alan56@adm.cgmh.org.tw]

Thanks for corntiubtnig. It's helped me understand the issues.

Atlanta a.k.a System Center Advisor: aNTrcaNuFINAAm

Aldair [scott.balyo@wybusiness.org]

What a joy to find such clear thinking. Thanks for posting!

SQL Server 2008 installation problem: macbmKlVPsVQOpU

khondee [info@hpnw.de]

That's not even 10 mniutes well spent!

enable Unicode compression in SQL Server 2008 R2 after upgrading: xzhboUCUPUlyeUGLYW

Musa [bill@billdick.co.uk]

Awesome you should think of soetmnhig like that

enable Unicode compression in SQL Server 2008 R2 after upgrading: BukPWGKGOStUSa

Dominiky [pier.beaujean@satmex.com]

Great article, thank you again for wriintg.

sp_configure:

Feodor [feodor.georgiev@sqlservice.se]

This is awesome! Thanks!

Auditing SQL Server groups: Part 2 of ?: CogHgILYJqVltQG

Mohammed [ronyvv@cariari.ucr.ac.cr]

I am new to this. Since I am coming from a Microsoft SQL Server enonevnmrit, and to some other it may seem like the two versions should be very similar, and they are, to a certain degree, but they are also very different in some important and basic ways. Good read, thanks!

Auditing SQL Server groups: Part 2 of ?: JFlxrNhsmgMRz

Roberto [paula.andrade@bbcr.com.br]

Thank you for your help. I used the second appcoarh to get past configuring MySQL to have root access from remote machine. Here is what I did: From command line, log in mysql (mysql -h localhost -u root -p)>)mysql> use mysqlmysql> ALTER TABLE `user` CHANGE COLUMN `authentication_string` `authentication_string` TEXT NULL COLLATE 'utf8_bin' AFTER `plugin`; This operation should succeed.Configure MySQL again (with root access allowed for remote machine checked) and the operation should succeed this time.

Auditing SQL Server groups: Part 1 of ?: SGGZOjjLLQNvEStoW

Maaraket [jaysnapp11@gmail.com]

You are all right to a certain nxetet. It really depends on what level of study we are talking about. To develop small or non critical softwares or to administrate non critical networks, we don't need to be very good at these two subject to study IT. For now, these are usually needed for IT markets in Cambodia. Physics (specifically electronics) are required for computer engineers (people who design, e.g., CPU). For critical systems, like softwares to control airplane, techniques in maths are used, e.g., to prove that the softwares are correct. Even in Networks, we use techniques in maths to prove correctness of protocols. Cryptography, which also concerns maths, is used to transmit data securely on networks.But for the need of our country right now, we don't need to be very good at these two subject to be able study IT. As already mentioned in the comments, it depends on our efforts and commitments. Everybody can study IT if they really like it.

Ökad tillgänglighet till mätdata för Nitro Consults entreprenörer: Antal

Anders [anders@ncvib.com]

Nu 1,3 miljarder och 45 miljoner värden per månad. Rullar på bra.

a centralized, unified view of my SQL Server 2008 R2 entities, please?: FWpPMDYyyNtDtJ

Monique [chi0628@seed.net.tw]

There should not be any prlmbeos on Windows 7, 32- bit (just make sure to choose the 32-bit installation). As for VS 08, it installs SQL Server Express (I think 2005), so that is a minor issue. It means that when you go to install 2008 R2, you either have to use a different named instance, like SQLEXPRESSR2. Or you have to uninstall SQL Server after VS 08 installs and before you try to install SQL Server 2008 R2.Then you can install R2 with the SQLEXPRESS named instance.

a centralized, unified view of my SQL Server 2008 R2 entities, please?: CCdtdGDBztsVbDTkza

Almosmohandes [kobud@kobud.net]

Thanks, Wil. Check out the SQLSaturday67 session page too. Some of the prerentess have download links there too. Some of the sessions I had to skip were by Brent Ozar, Tom LaRock, Allen White, Tim Ford, and Jeremiah Peschka. Tons of great stuff!

Free SQL server management studio add-in - SSMS Tools pack: SppDkgGmnoZUjiLncsO

Ronan [like_55578@yahoo.com.tw]

I'm shocked that I found this info so eisaly.

Free SQL server management studio add-in - SSMS Tools pack: TielmsSlnc

Edward [info@autotechnopark.org]

Real brain power on display. Thanks for that anwser!

My most un-favourite things about Resource Governor:

Jonathan Kehayias [jmkehayias@gmail.com]

While it may not be a official UI, the Extended Events Manager I wrote as a SSMS Addin on Codeplex is a great tool in managing and consuming Extended Events in SQL Server 2008 and 2008R2. http://extendedeventmanager.codeplex.com/

My most un-favourite things about Resource Governor:

Feodor Georgiev [feodor.georgiev@sqlservice.se]

Thank you for the link. I know about the unofficial UI, and I was already planning to include it in my next article on the subject.

My most un-favourite things about Resource Governor: tIRxiRcnivz

Wallace [gridnev_78.78@mail.ru]

1, NO. P2P system such as Chord keeps only O(logN) meta data at each node so that every reuqest can be directed to the real server via O(logN) route, but the first practical P2P system used in large internet company, which is Dynamo, tries to keep all meta data at one node so that every reuqest can be routed to serving node at most one step.2, The better way is to route client reuqest to the serving node directly, Client can cache all meta data of the whole cluster. Client reuqest is not related to load balance , the latter may means how to distribute data, such as consistent hash, etc.3, I think you mean whether to flush the data to disk here. It depends, some systems may flush the data at only one replica. The purpose is to avoid the whole cluster down problem, such as the whole data center is powered off.4, Suppose there is one tablet having three replicas, A, B and C. A is the primary. The update reuqest is successful as long as it takes effect at the primary (A) and propogated to either B or C. We know that A is consistent, but nobody knows B or C is up to date.5, I think you must know the difference between NWR and Primary-only mechanism. With Primary-only mechanism, the same data can be updated by at most one primary node, while the NWR mechanism always allow the same data updated by several nodes concurrently. So the NWR mechanism needs to deal with conflict. If we use timestamp to deal with conflict, we must keep the clock synchronized.

My most un-favourite things about Resource Governor: wJeKqQTvHyuLAHAsQ

Ishteyak [simon@volcom.co.za]

Hey Barney! Nice tool, but I've got a couple of qeotsiuns:It bumps the version before it executes the migration, and I'm worried that if an error occurs, it'll read like the schema is up to date, when it isn't. I put try/catch/aborts around the migrate(s), and put the revision bump at the end, to see if that would do it it sorta did.I'm still playing with it, so I don't know if that broke something The other comment is, what about a team scenario, where two members may have updated the schema at the "same time" so to speak? I figured that if this tool works for me, that problem could be solved in the "business logic" (always check the repository prior to adding a migration script, say), but thought it was an interesting thought, or whatever. =]Glad that you won a wii, that's cool- I just checked out the projects that were submitted, and I'm kinda stoked- looks like there are a couple of them that would make great additions to what I hope to be a release of Model-Glue actionpacks to make RAD more rapid eh. we'll see.Anyways, thanks for the code, esse!

My most un-favourite things about Resource Governor:

Feodor Georgiev [feodor.georgiev@sqlservice.se]

Aleksey, my question is more of a riddle and not a question which requires a straight answer. What I am referring to is the fact that any edition of SQL Server has the Resource Governor functionality built in. I.e. there is already an internal pool to which all connections are categorized. In any edition of SQL Server 2008 you can run the following DMVs and you will get at least one result: SELECT * FROM sys.dm_resource_governor_workload_groups SELECT * FROM sys.dm_resource_governor_resource_pools SELECT * FROM sys.dm_resource_governor_configuration It is only in Developer and Enterprise editions that we have options to create additional pools and settings. Hope this helps. /Feodor

My most un-favourite things about Resource Governor:

aleksey.fomchenko [aleksey.fomchenko@gmail.com]

Hi Feodor, how can I confirm that Standard edition has got Resource Governor feature if I do not have it on site? Because BOL tells us completely opposite information. I also do not have a answer on your question, but I suspect it should be SQL Engine.

Who changed my objects?:

hawi [hakan.winther@sqlservice.se]

To use the script you need to replace the ` and ´ with '. You also need to replace the – with - - (double minus signs) for the comments in the where clause. /Håkan Winther

Who changed my objects?: dbLKwuAQtEDK

Alexsandra [losee@weight.on]

The column names of the reslut set that are returned by EXCEPT or INTERSECT are the same names as those returned by the query on the left side of the operand.Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.

Tips på läsvärd bok!:

Feodor [feodor.georgiev@gmail.com]

Japp, boken är grymmt! Jag rekommenderar att ni köper den på nätet som en PDF fil. Prenumerera sig till http://www.manning.com/ maillist och ni kan få en bra pris. Här kan man läsa en en kapitel av boken också: http://www.manning.com/nielsen/SampleChapter1.pdf //Feodor

Perfmon Data collection - the secure and agile way: iHtTkESVqOXFHAL

Kaycie [biuro@aresmm.pl]

För få eller för många virtual log files kan påverka prestanda: NbOCzTpqDt

Reignbeau [listentojimmy@gmail.com]

doxycycline 0574 insurance quotes 1510 cheap flights dwpcf

Försenad julklapp ?: EoEAoOgHrDAnEFMrBr

Seamus [alliemyr@yahoo.com]

That's an apt answer to an intesreitng question

SQL server 2008 - correct way to rebuild a HEAP: KUmiNMrGlbeqlnw

Ethanael [d.shutoc@mail.net]

credit card 707436 doxycycline =P insurance quotes 446 flight cheap >:)))

Connecting to SQL Server DENALI CTP1:

LF-Jonas [jb300@hotmail.com]

Gå in här och kommentera, så att de får veta att det är fler som har problem: http://connect.microsoft.com/SQLServer/feedback/details/615732/denali-invalid-error-message

CTP1 Denali quick preview and first impressions: ABuNWyPRgvmiBfVqelU

Commodor [info@trilobyte.co.uk]

What a plaseure to meet someone who thinks so clearly

Denali (SQL server 2011) CTP released: XznKJxAoAhrPWEXoSDa

Aquatico [patricio@lonkotech.com]

Great stuff, you helped me out so much!

SQL server och SSD är framtiden...: kTZKYDPuKUeDIfL

Fabian [sa-nt@companydirectors.com.au]

This forum need shaking up and you've just done that. Great post!

SQL server och SSD är framtiden...:

Lars Hedberg [lahe13@handelsbanken.se]

Mja, här är det läge för en korrigering... RamSAN fungerar mycket bra till log-device, om den bara är rätt inställd! Det sagt, det finns alternativa lösningar som är snabbare, framförallt för log-device liknande laster. Men det är lite en annan historia, och inte direkt billigt det heller. Mvh Lars Hedberg

Besök hos Microsoft i Redmond.: mOBohfqPgapVwyuXeq

uxgwuzmo [qhyrgo@enezwt.com]

VX9TIO mnelwsstdnsy

strax dax för PASS SQL Server Summit 2010: XufmLeZwRfZfHVvXQ

Leipas [d.j.hall@gre.ac.uk]

Thanks for posting this! I was gnetitg the error: Msg 109, Level 20, State 0, Line 0A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 The pipe has been ended.) I searched for hours before coming across this article. Unchecked allow inprocess , now I can connect to the linked server flawlessly!

strax dax för PASS SQL Server Summit 2010: bkKvRHnAkYMmtN

Hasan [info@yyiannakopoulos.com]

To Load and Execute TOADPREP.SQL1.Connect as System or a DBA.2.Go to File > Open File.3.Open your TOAD > TEMPS folder.4.Double-click TOADPREP.SQL.5.The TOADPREP.SQL loads into your SQL Editor.6.Execute each stetemant in the script by clicking on the stetemant and pressing .If this is your first time running the TOADPREP.SQL script and you execute a Drop stetemant, you receive an error message. This is because there is nothing to drop. You can click OK and continue. However, you can just skip all the drop stetemants (don't execute the drop stetemants), since TOAD has nothing to drop.7.For the Connect stetemant, you need to modify the stetemant for your connection.8.When you exit, TOAD will ask you if you want to save changes. Click Yes.Quest Software

SQL Server - IO rekord eller hall of shame?:

Lars Hedberg [lahe13@handelsbanken.se]

Angående RamSAN 630: Du måste ta i beaktande att den inte presterar 8KB block read i 1.000.000 IOPS, utan troligen 4KB eller någon annan udda storlek... Allt är inte enkelt ens med SSS (Solid State Storage), man kan se tillverkarna presentera seriöst höga siffror. Men man måste hålla reda på vilken blockstorlek som t.ex. en sökning sker med, och hur, med vilket gränssnitt, Point-to-Point osv - många faktorer kommer helt plötsligt in som man tidigare aldrig sett som utmaningar... Om man som TMS säger att de klarar av 10GB/sekund, så måste man räkna baklänges och se vilken blocksize var det månne i ? Samma sak med IOPS, där kan man misstänka att TMS antagligen använt just 4KB eftersom det är "native" storleken på flash minnes helt enkelt. Dock så använder även TMS RAID bakom fronten så att säga så man ser faktiskt inte vad de gör med blocken direkt. Detta kan man se om man kör RamSAN-620 enheten i 512B block, man tappar 65% av prestandan (enbart 80 KIOPS) jämfört med 250.000 "utlovade" IOPS på RamSAN-620 (som då är 4KB block alltså). Visst kan man "slänga hårdvara på problemet" och hoppas på att det löser problemet, men det finns fallgropar med SSS också. Mvh Lars Hedberg

SQL Server - IO rekord eller hall of shame?: SIxXWZFalii

Lie [sommerfeld@nabu-hamburg.de]

I think you're probably on to stieohmng there, especially in smaller shops where the it's MSFT, how hard can it be mentality might start. It's one think when it's a workgroup setting, where people probably work with Office that was preinstalled on their pc when they bought it, and think that's all there is to it. But as you grow you realize you need an I.T. person to handle everything (from AD to Exchange to SQL and Sharepoint, so you end up with accidental everything administrators) and the myth perpetuates.For me, I think the more curious part of this story is why the cab driver was an Oracle DBA or better put, why an Oracle DBA would become a cab driver. Maybe you didn't have enough time for him to tell you, but I'm guessing there's a long story behind that.

Developers give DBA work, thank you!: sAuFbOYhvnBMmNS

Ranger [aquarialand@aquarialand.com]

Surpieor thinking demonstrated above. Thanks!

Developers give DBA work, thank you!: gtVhOeAKdlqG

Alexander [filatelistyka@o2.pl]

Ha! The punch harder reenferce comes from me tweeting about what the authoring process felt like. It was like I was punching myself in the junk as fast and as hard as I could, while the publisher stood behind me telling me to punch harder and faster.Did you get a copy of ours? I asked for one to be sent to you if you didn't get it, let me know what your mailing address is.

PowerCow, PowerShell and the Power in general: reYGyuxZVQlQWsyg

Jones [w.gliecriedac@web.tv]

That's cleaerd my thoughts. Thanks for contributing.

SQL Service fyller år! : hqkiLtIavBHR

Macco [speasr@k12tn.net]

insurance quotes fqbx doxycycline slfuy online colleges :D

SQL Service fyller år! : VhFlUjqbkdyaXM

ali [paula@mherc.org.nz]

Always a good job right here. Keep rollnig on through.

Update your SQL Server statistics !: OmsiIGeVbphhtV

John [office@globaleverantwortung.at]

Full of salient points. Don't stop beileivng or writing!

SQL Server objekt i TFS: qSYqQrReFZnQnsYdBW

Ali [quellmalz@nabu-hamburg.de]

Normally I'm against killing but this aritlce slaughtered my ignorance.

Microsoft SQL server 2008 SP2 released: KYNLMrTmhjBGmHpDJiz

Ally [kirstin@aspworldtour.com]

Knowlgede wants to be free, just like these articles!

Travels with Barley (in search of database peace): XUtxQrmRNnTSnswP

Asif [ramonrevilla@hotmail.com]

I know something needs to cganhe with thesubstring. I just do not know what it is. Basically, I need to put inposition 1 and 2, an 11.UPDATE tableSET SUB_DMO = SUBSTRING(SUM_DMO,1,2) = '11 WHERE field 1 = '000001688085 ANDfield2 = TNT' ANDfield3 ='MTSILV08

Travels with Barley (in search of database peace): WTQHfEoxOVdNBnjJ

Kaylin [gman@fs.fed.us]

viagra 124751 viagra 371 life insurance tqdyz

SQL server partitionering - defragmentering av index: pbpuwIPvRmPPGdzEcmo

Marie [info@deltaland.co.za]

What I find so interesting is you could never find this anyhwree else.

Don't panic: The log for database 'name_here' is not available.: klTwMJBQKyyIE

Caroliine [melanieaugais@yahoo.fr]

This post has helped me think things trohugh

Don't panic: The log for database 'name_here' is not available.:

Mr Incredible [alizulu@gmail.com]

why not just take the database offline and bring it online, this will sort out the problem?

Question of the week: index rebuild with maxdop?: HLBJBsTtKWUWXoORh

Chanesiia [johndoe@example.com]

Thanky Thanky for all this good information!

SSIS felmeddelande : Unable to prepare the SSIS bulk insert for data insertion: JtBhIKhROk

JORGE [diane@kbemail.com]

Which came first, the problem or the solution? Luckily it doesn't matter.

SSIS felmeddelande : Unable to prepare the SSIS bulk insert for data insertion: GEIdAiDUjlxbGd

Sealane [weiklepr@yahoo.com]

Great article, thank you again for writing.

How are CTEs not better than a temp table?:

Kev Riley [kevriley@gmail.com]

Why use an if..else at all - put the conditional logic into the where clause of the select from the CTE.

The flexibility of CTEs is that instead of having to access 100% of the data into the temp table, the optimizer can, if the right indexing exists, only access the subset that is needed.

Only when the subset is 100% of the original dataset, will the performance match the temp table solution, in all other cases, it will be better.

How are CTEs not better than a temp table?:

Kev Riley [kevriley@gmail.com]

Why use an if..else at all - put the conditional logic into the where clause of the select from the CTE.

The flexibility of CTEs is that instead of having to access 100% of the data into the temp table, the optimizer can, if the right indexing exists, only access the subset that is needed.

Only when the subset is 100% of the original dataset, will the performance match the temp table solution, in all other cases, it will be better.

Rebuild - this time it fragmented even more:

Feodor [feodor.georgiev@gmail.com]

Actually, a small correction about finding the indexes with page locks ON: the script should be: select object_name(object_id) as [TableName] , name as [IndexName] from sys.indexes where allow_page_locks = 0

Question of the week: SQL Server performance : VEtmYBkrRknSJLeHxMt

Lakeisha [office@mar.co.at]

insurance quotes 5642 doxycycline 3024 flight cheap >:((

Question of the week: string manipulation in T-SQL:

LF-Jonas [jb300@hotmail.com]

--Here is how I would do it! :) --First I made it like this to test my hypothesis that PATINDEX would help me to do a substring: --No, this is not my first attempt. Will not show that! ^^ SELECT SUBSTRING('abca1_bcd_esj', 1,PATINDEX('%[_]%', 'abca1_bcd_esj')-1) + SUBSTRING('abca1_bcd_esj', (PATINDEX('%[_]%', 'abca1_bcd_esj'))+1,LEN('abca1_bcd_esj')-(PATINDEX('%[_]%', 'abca1_bcd_esj'))) --It was a tricky one, I realised when I understood that underscore also is the wild card. Brackets saved me there. --Then I made it into a generic version so I could test any string (I limited it to 64 signs though, and there is a limit of 2000 signs for the embedded SQL variable), and be able to change any char to any other char, but just the first one: declare @stringtocheck nvarchar(64) , @chartoreplace nvarchar(1) , @chartoreplacewith nvarchar(1) , @sql nvarchar(2000) SET @stringtocheck = 'abca1_bcd_esj' SET @chartoreplace = '_' SET @chartoreplacewith = '!' --'before' SELECT @stringtocheck SET @sql ='SELECT SUBSTRING('''+ @stringtocheck + ''', 1,PATINDEX(''%[_]%'', '''+ @stringtocheck + ''')-1) + SUBSTRING('''+ @stringtocheck + ''', (PATINDEX(''%[_]%'', '''+ @stringtocheck + '''))+1,LEN('''+ @stringtocheck + ''')-(PATINDEX(''%[_]%'', '''+ @stringtocheck + '''))) ' --Show me what I've done so far SELECT @sql --Yeah, looking good, execute away! EXEC (@sql) abca1bcd_esj (1 row(s) affected)

Question of the week: string manipulation in T-SQL:

LF-Jonas [jb300@hotmail.com]

Forgot to exchange the "before" sign to the "after" sign: here is a new one: declare @stringtocheck nvarchar(64) , @chartoreplace nvarchar(1) , @chartoreplacewith nvarchar(1) , @sql nvarchar(2000) SET @stringtocheck = 'abca1_bcd_esj' --SET @stringtocheck = '' --SET @stringtocheck = '_' --SET @stringtocheck = '__' SET @chartoreplace = '_' SET @chartoreplacewith = '!' SELECT @stringtocheck IF LEN(@stringtocheck) > 0 BEGIN SET @sql ='SELECT SUBSTRING('''+ @stringtocheck + ''', 1,PATINDEX(''%[_]%'', '''+ @stringtocheck + ''')-1) +'''+ @chartoreplacewith + ''' + SUBSTRING('''+ @stringtocheck + ''', (PATINDEX(''%[_]%'', '''+ @stringtocheck + '''))+1,LEN('''+ @stringtocheck + ''')-(PATINDEX(''%[_]%'', '''+ @stringtocheck + '''))) ' --SELECT @sql debug EXEC (@sql) END ELSE BEGIN SELECT @stringtocheck END

Question of the week: string manipulation in T-SQL:

Feodor Georgiev [feodor.georgiev@sqlservice.se]

Stefan, the only problem with your query is that if you pass a string which does not contain any _ (underscores), then the result is null.

Question of the week: string manipulation in T-SQL:

LF-Jonas [jb300@hotmail.com]

Simple is good. Working code is better. ;) Just kidding, your was nice, though it doesn't work if the text doesn't have an underscore at all in it. Then it returns NULL ("disregarding of how many occurrences there are" i.e. couldn't it be an amount of underscore = 0, as well?). But then again, that's just a small modification of your code needed to be done. If you just replace the SELECT to something like this, it would work with way less amount of written letters, than mine: SELECT CASE CHARINDEX('_', @text, 0) WHEN 0 THEN @text ELSE STUFF (@text, CHARINDEX('_', @text, 0), 1 , '!' ) END So I guess my golden star is not as super nova as yours *rolling on to my back, exposing my throat*

Question of the week: string manipulation in T-SQL:

hawi [hakan.winther@sqlservice.se]

You can also add an ISNULL function to handle strings without any underscores. DECLARE @text AS NVARCHAR(15) SET @text = ‘abca1_bcd_esj’ SELECT ISNULL(STUFF(@text, CHARINDEX(‘_’, @text, 0),1 ,‘!’),@text)

Question of the week: string manipulation in T-SQL:

Stefan [stefan.c@inbox.com]

Keep it simple ;-) DECLARE @text AS NVARCHAR(15) SET @text = 'abca1_bcd_esj' SELECT STUFF (@text, CHARINDEX('_', @text, 0), 1 , '!' )

Question of the week: string manipulation in T-SQL:

Feodor Georgiev [feodor.georgiev@sqlservice.se]

Hey all! Great job! I am running low on golden stars at the moment! Thanks for your wonderul replies.

Question of the week: string manipulation in T-SQL:

LF-Jonas [jb300@hotmail.com]

--Final version: now it can take a string, that has NOT got the sign to search for in the string (IF clause solves that), as well as if the string is empty (other part of IF clause) or have that sign as the only sign/signs, adding to your specific example that it also succeeds with. Back to work! :) declare @stringtocheck nvarchar(64) , @chartoreplace nvarchar(1) , @chartoreplacewith nvarchar(1) , @sql nvarchar(2000) SET @stringtocheck = 'abca1_bcd_esj' --SET @stringtocheck = '' --SET @stringtocheck = '_' --SET @stringtocheck = '__' --SET @stringtocheck = 'abca1bcdesj' SET @chartoreplace = '_' SET @chartoreplacewith = '!' SELECT @stringtocheck IF ((SELECT PATINDEX('%[_]%', @stringtocheck)) > 1) AND (LEN(@stringtocheck) > 0) BEGIN SET @sql ='SELECT SUBSTRING('''+ @stringtocheck + ''', 1,PATINDEX(''%[_]%'', '''+ @stringtocheck + ''')-1) +'''+ @chartoreplacewith + ''' + SUBSTRING('''+ @stringtocheck + ''', (PATINDEX(''%[_]%'', '''+ @stringtocheck + '''))+1,LEN('''+ @stringtocheck + ''')-(PATINDEX(''%[_]%'', '''+ @stringtocheck + '''))) ' SELECT @sql debug EXEC (@sql) END ELSE BEGIN SELECT @stringtocheck END

The implicit conversion monster:

Query the cached plans: Implicit conversion « sqlconcept.com []

[...] the cached plans: Implicit conversion As I have written previously, the Implicit Conversions can be a performance problem and they can be easily [...]

Äntligen TechDays: KWWosWuMntsGsh

Amir [i.grieblis@gmail.dk]

That's more than sensible! That's a great post!

Äntligen TechDays: CMxFsGlJzOpzcz

Irvan [960943843@qq.com]

I'm so glad that the internet allwos free info like this!

Vinn en SQL Server Prestanda Analys !: IuAiChZXKbw

Rawit [chbudget@ihug.co.nz]

This is way more helpful than anything else I've lokeod at.

Performance debugging with DMVs: HTCloliyVFelivgnTF

Hamdah [qyvoasep@ybfisylk.com]

Frankly I think that's absluotely good stuff.

Performance debugging with DMVs: QZzapuSZJcleMohn

yorvisjavier [meaghan.mcleod@sharesociety.ca]

Thanky Thanky for all this good information!

Subtracting values from two consecutive rows: VSkEqVuuZtb

Andysurya [dyanncup@hotmail.com]

God, I feel like I sohlud be takin notes! Great work

Subtracting values from two consecutive rows: ERpGaOsuKw

Leon [ljuanei@sina.com]

This is both steert smart and intelligent.

When vanilla SSIS is not enough: oYlVLoHlaukuYzUEIRq

Norma [wjmsu@mail.ru]

I'm shocked that I found this info so esialy.

Question: how to convert hex to decimal: JDLaXvnQgk

Ussamean [wilfredocultura@ibest.com]

That's a smart way of thinking about it.

Question: how to convert hex to decimal: vpbsOhAEFEvKysSa

Regina [mail_grams@grams.senate.gov]

Articles like this really grease the shafts of knowledge.

Ask away: qsAToIQZtSgPvUo

Quy [bpcaravans@hotmail.co.uk]

Real brain power on display. Thanks for that anwser!

Ask away: eFclrsbBsiZrezwP

Ari [antinori@antinori.it]

It's a pleasure to find such ratioanltiy in an answer. Welcome to the debate.

SQL server 2005 slowing down after a while: DqrJglBEFWlFemzCva

Sanjida [info19.20@aol.it]

If only there were more clever people like you!

Lock pages in memory: JmrOtLFcWZwlEVQtt

David [eleamidg722482@gmail.com]

Wow, this is in every respect what I needed to know.

SQL Server Training on DMVs: cSKgRsQOVBlDh

April [patrick.verhees@chello.nl]

Mike,Still have not heard back from you on the FactSQL package for the SSIS mntinoriog project. It is still not working, there is a type mismatch for the Get Monitor Date container. I am note sure what is going on here. I have tried reloading your code off the website repeatedly to no avail. Do you have any idea as to what the problem may be? This is the only issue I have with this pkg. Everything else seems to be running correctly. I cannot get the cube to work without the FactSQL table being populated.

SQL Server Training on DMVs: vYLMHlYNta

Rilza [tyyzhaoxu@yahoo.com.cn]

A ralely good answer, full of rationality!

The AcquireConnection method call failed with error code 0xC0202009.: MazSQOkjKMBCyUK

Faizal [gplsktty@wtlplzmr.com]

Good point. I hadn't thought about it quite that way. :)

Kvalitetssäkring åt Riksbyggen: bNYKGjdfEuCTGm

Melisha [supergreen@martrans.org]

A simple and intelligent point, well made. Thanks!

Tack för i år!: JYIPIwVUzEJC

Scarlett [corsec@gapuraprima.com]

It's like you're on a misison to save me time and money!

Create database creates an exclusive lock on model: MMspDrlgcFVYEZN

Vaska [ragingbullets@gmail.com]

Thinnkig like that shows an expert's touch

Exekveringsplanen, din vän i nöden: hldAmUBKVcPsiAOUoC

Justtin [biuro@dmcatering.pl]

This does look promising. I'll keep coming back for more.

Exekveringsplanen, din vän i nöden: yMzmUYCEpFWC

Ivica [mary.noonan@dol.govt.nz]

I wanted to spend a minute to thank you for this.

PerformanceDW size problem: hsCvmmKTEbCbUAegE

Jamaal [sarah@crfl.co.nz]

That's a knowning answer to a difficult question

SQL Server 2008 non sysadmin SSIS job owner: lIBgpFgcjXluxgjia

All [cullmanopenmri@hotmail.com]

This site is like a classroom, excpet I don't hate it. lol

Hur förbättrar man SQL Server? : CzSNQFPJMZTWR

mardan [makoto.fujiwara@triumf.ca]

Haha. I woke up down today. You've ceheerd me up!

Hur förbättrar man SQL Server? : XZkSzzDZDujXxNlhV

Rosa [claudia_s@unidosdecolonia.de]

It's a joy to find someone who can think like that

Samnordiska SQL-system gör Mekonomen bättre i sin kundservice: IHESWGylKRJYQy

Edmund [v.joudrierolm@debian.ph]

God help me, I put aside a whole afternoon to figrue this out.

Extreme page splits... and more?: kZYgcMqHiUIWP

June [b.fronda@tvfestival.com]

No questions this is the place to get this info, thanks y'all.

sp_MSForEachDB - tricks of the trade: HPlzRpqrQPtqWvATG

Silver [pedidos@editoragarapuvu.com.br]

This is exactly what I was loonkig for. Thanks for writing!

Massor av bra gratis verktyg för SQL Server: kZOTrxxujkI

Amandah [szxadpzx@bxtcwoat.com]

Didn't know the forum rules allowed such briliant posts.

Säkerhetsproblemet med XP_cmdshell förklarat: zKjklKtdecrWKM

Marietta [ms2003@txooo.com]

It seems like new technologies come out every day to do the same thngis we have done for years and vendors are bombarding us with marketing campaigns to use them. It’s not uncommon to find ourselves wanting to implement these new technologies and techniques whilst the solution has been available to us all along. I would like to praise you for the simplicity and pointing out the obvious.Your blog entry for is refreshing and points out that many of the database objects in RDBMS are forms of automation. I’m now a subscriber to your blog.

Säkerhetsproblemet med XP_cmdshell förklarat: dNzMkyhTzAktTMPayI

Attila [karen.columbus@hotmail.co.uk]

Interesting story. At my last job, we had people in IT who tghouht that we, SQL Server DBAs, just sat around and watched databases all day. We were floored by that because we were extremely busy doing way more than just watching databases. So we then took it on ourselves to better communicate and show them what we did day to day. We were also shocked by the number of people in IT who would install SQL Server for some application without telling us, come to us when they had problems, and then look shocked when we asked if they had backups of the databases. It was umm interesting, to say the least.

SQL Server 2008 SP1 släppt: TqftUjtPzyefCy

Rafael [felix@holderied.de]

Nice post. I can't believe this isn't exaeipnld anywhere by MS. I googled and googled. I was thinking that SSDT was only new DB projects and BIDS was still something separate, only because I installed SSDT and it didn't give me any of the BIDS stuff.Can the 2012 BI projects(SSAS/SSRS/SSIS) target a 2008R2 server?I am using SSDT-DB for a 2008R2 server. It would be nice if I could upgrade my BI projects so that I can put them in the same VS2010 solution instead of having a separate solution for BIDS 2008(since it is VS 2008).

SQL Server 2008 SP1 släppt: zOSSOozGgBqYZcvgCKQ

Cody [oumali@mydestiny.net]

This is the ideal answer. Everyone should read this.

SQL Server 2008 SP1 släppt: GCnpjSbhEV

Irina [sahbjtaw@tctarjtr.com]

Good to see real expertise on display. Your contriubotin is most welcome.

A word on Central Management Server in SQL Server 2008: http://www.johnsansom.com

John Sansom [john@johnsansom.com]

Central Management Server(CMS) is a great feature and one that is undersused out in the field.

We make exstensive use of CMS in our environemnts as well as the MDW, another feature that does not get enough promotion.

These features came up in a conversation in the community forum recently, you might find the discussion of interest. (http://johnsansom.com/sqlforum/index.php?topic=14.0)

Happy Holidays!

CU3 för SQL Server 2008: bVrdVhloMBxAoVQ

Evin [m.hoslean@yahoo.nl]

I can't beleive you're not playing with me--that was so helpful.

SQL Server default trace: MInySySeKsZhnvRduA

Desirous [heather.scott@sharesociety.ca]

Great isnight. Relieved I'm on the same side as you.

Oracle Heap Tables or SQL Server Clustered Indexes ?: fHKmgyUYDrgEE

Thaynara [redsneakerdiary@gmail.com]

Apperication for this information is over 9000-thank you!

Bug in SQL Server 2012 Management Studio: ajdyCzejZkRCCfExyx

Hannah [vendite@creiitaly.com]

insurance quotes owl online colleges quunl auto insurance online :-)

SQL Server 2012 CU1 upgrade step 'msdb110_upgrade.sql' encountered error 547: SQL Server 2012 SP1 Error

Lars Ljungblom [lalj01@Handelsbanken.se]

I got a similar error when applying SP1 for SQL Server 2012, when you apply SP1 on a SQLServer 2012 after installing all required files, restart of services is needed for applying dbupgrades to the actual SQLServer, but it crashes with some errors. We tried some things but nothing worked, we don´t want to use Trace Flag 902 because we want the Db to be upgraded aswell, the only thing we found was that giving Service account that runs SQLServer service Local Admin privilegies, elsewise the upgrade ends with some specific information regarding what happens.

2012-11-30 10:16:26.32 spid6s Database 'master' is upgrading script 'u_tables.sql' from level 502 to level 569.

2012-11-30 10:16:26.39 spid6s Starting u_Tables.SQL at 30 Nov 2012 10:16:26:390

2012-11-30 10:16:26.39 spid6s This file creates all the system tables in master.

2012-11-30 10:16:26.40 spid6s drop view spt_values ....

2012-11-30 10:16:26.42 spid6s Creating view 'spt_values'.

2012-11-30 10:16:26.56 spid6s drop table spt_monitor ....

2012-11-30 10:16:26.56 spid6s Creating 'spt_monitor'.

2012-11-30 10:16:26.56 spid6s Grant Select on spt_monitor

2012-11-30 10:16:26.56 spid6s Insert into spt_monitor ....

2012-11-30 10:16:26.63 spid6s Error: 25641, Severity: 16, State: 0.

2012-11-30 10:16:26.63 spid6s For target, "package0.event_file", the parameter "filename" passed is invalid. Target parameter at index 0 is invalid

2012-11-30 10:16:26.64 spid6s Error: 912, Severity: 21, State: 2.

2012-11-30 10:16:26.64 spid6s Script level upgrade for database 'master' failed because upgrade step 'u_tables.sql' encountered error 25641, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

If you are interested, try upgrading a SQL2012 with SP1 where the SQLServer service account doesn´t have to high permissions on the actual server, just the ones applied during normal SQLServer 2012 installation.

Hopefully you receive the same result as I did, otherwise I´m out on deep water.......

//Lasse

SQL Server 2012 Administrator Skills Upgrade:

SQL Server virtual labs « SQL Service – Specialiserade Microsoft SQL Server konsulter []

[...] SQL Service and LabCenter is giving SQL Server 2012 related labs in, more information about that is here, here and [...]

Speeding up Index rebuild:

Feodor Georgiev [feodor.georgiev@sqlservice.se]

@Magnus: the code which is executed internally in SQL Server when you update statistics with Full Scan looks like this: SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [c2] AS [SC0] FROM [dbo].[Tablename] WITH (READUNCOMMITTED) ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL But if you update the statistics without a full scan the code looks like this: SELECT StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM (SELECT [cEndDateTime] AS [SC0] FROM [dbo].[Tablename] WITH (READUNCOMMITTED,SAMPLE 5.949706e+001 PERCENT) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1) I.e. the processing is limited to 1 thread (and a heavy use of TempDB, you can imagine) After all, updating statistics nowadays is a very tricky business, especially in larger environments. Funny enough, though, when the statistics are updated during index rebuild, there is less impact on the system. I guess this is a bug from MS, which they will fix, eventually. https://connect.microsoft.com/SQLServer/feedback/details/457024/update-statistics-top-100-percent-and-sort-warnings

Speeding up Index rebuild:

Feodor Georgiev [feodor.georgiev@sqlservice.se]

@Steinar: There is no guarantee that the index rebuild in any case will use all processors. What happens during an index rebuild (and this is also valid for creating indexes, dropping clustered indexes and constraints) is that the processing always starts with 1 coordinating thread, which tries to figure out the distribution of index keys. Then the coordinating thread will try to spread the ranges of index keys to the available threads, depending on the DOP. But if it cannot find enough ranges and depending on the available resources, the thread count can be lowered. After this, each thread starts working on its task of building an index structure, which at the end is "assembled" by the coordinating thread. Now, what problems may we see here? One thread does the scan for ranges and decides what is optimal, but is it always? :) Also, with parallelism, if a thread is done processing its task, what does it do while the coordinating thread becomes available to pick up the subrange structure?

Speeding up Index rebuild:

Magnus Ahlkvist [magnus.ahlkvist@vansterpartiet.se]

I agree that using more CPUs is usually beneficial to performance. But as I also do UPDATE STATISTICS, usually WITH FULLSCAN, I've run into scenarios where parallellism is not working out at all - a moderate sized DB takes over five hours to update statistics on, because of ill parallellism. Running the same code with Max Degree of Parallellism 1 finishes in half an hour. With ALTER INDEX REBUILD, you can use MAXDOP parameter, but with update statistics that's not available, and with just 10 votes, I doubt it will ever be included... http://connect.microsoft.com/SQLServer/feedback/details/628971/add-maxdop-parameter-to-update-stats

Instant File initialization - pros and cons:

Sergey Klimkevich [sergey.klimkevich@gmail.com]

SQL Server writes the data in 8K pages without the need to zero out the 8K that will be overwritten or zero out any unallocated pages in an extent. (http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-instant-file-initialization.aspx)