CREATE statement

Question

Post reply

CREATE statement

Viewing 15 posts - 16 through 30 (of 41 total)

You must be logged in to reply to this topic. Login to reply

PRAMANA.DBA SSCertifiable Points: 5507 More actions · Answer 1

PRAMANA.DBA

SSCertifiable

Points: 5507

March 7, 2013 at 5:59 am

#1594405

+1

Nice Question ....

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

GOOD QUESTION...

NICE EXPLANATION FROM GUYS lOKESH, DANNY...

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

manik_anu SSCrazy Points: 2397 More actions · Answer 3

Nice question... I really learned two points.... 1. with go- how to create stored procedures and functions

2. without go- how to create stroed procedures and functions........:-D

Manik
You cannot get to the top by sitting on your bottom.

mtassin SSC-Insane Points: 23099 More actions · Answer 4

Good Question.... lots of fun to read through... I started saying Dibbiddy Dibbidy Dibbiddy....

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

david.wright-948385 SSCarpal Tunnel Points: 4043 More actions · Answer 5

Nothing wrong with the following for step 6 though (and the equivalent for step 4):

USE DBDB;

-- no 'GO' necessary

IF OBJECT_ID('dbo.udfUDF') IS NULL

exec('CREATE FUNCTION dbo.udfUDF(@f int) RETURNS INT AS BEGIN SET @f=1 RETURN @f END;');

In my opinion 'GO' is very overused. It's almost "stick a go in everywhere just in case", particularly in code examples.

For instance in this QOD, all except one 'GO' can be ommitted and the whole script executed in one 'GO' (but two batches(!)):

-- Query #1

USE master;

IF DB_ID('DBDB') IS NOT NULL DROP DATABASE DBDB

-- Query #2

IF DB_ID('DBDB') IS NULL CREATE DATABASE DBDB

go -- this one is necessary otherwise the rest of the script won't compile.

-- Query #3

USE DBDB;

IF OBJECT_ID('dbo.TBTB') IS NULL CREATE Table dbo.TBTB (tb1 int)

-- Query #4

IF OBJECT_ID('dbo.uspUSP') IS NULL

exec('CREATE PROCEDURE dbo.uspUSP AS SET NOCOUNT OFF');

-- Query #5

IF OBJECT_ID('dbo.TYTP') IS NULL CREATE TYPE dbo.TPTP AS TABLE(TYTB int)

-- Query #6

IF OBJECT_ID('dbo.udfUDF') IS NULL

exec('CREATE FUNCTION dbo.udfUDF(@f int) RETURNS INT AS BEGIN SET @f=1 RETURN @f END;');

sgehret 2557 Valued Member Points: 66 More actions · Answer 6

I ran all 6 in my SQL Server 2008R2 and the only one that did not error was the first one. Query 2 gives me error:

Msg 5133, Level 16, State 1, Line 1

Directory lookup for the file "D:\Database files\SQL Server\DBDB.mdf" failed with the operating system error 3(The system cannot find the path specified.).

and this caused every subsequent query to fail. I even changed the query 2 to use DB_ID. I changed back to original which I knew would fail and yes, that fails too.

So what is going on here? I am running SQL Server 2008R2 Express. Could that be it? I am just learning SQL Server. I work mostly with Oracle.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Revenant SSC-Forever Points: 42467 More actions · Answer 8

I am nominating this for the Most Misguided QotD of the Year Award.

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 9

Revenant (3/7/2013)
I am nominating this for the Most Misguided QotD of the Year Award.

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

david.wright-948385 SSCarpal Tunnel Points: 4043 More actions · Answer 10

sgehret 2557 (3/7/2013)
I ran all 6 in my SQL Server 2008R2 and the only one that did not error was the first one. Query 2 gives me error:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "D:\Database files\SQL Server\DBDB.mdf" failed with the operating system error 3(The system cannot find the path specified.).
and this caused every subsequent query to fail. I even changed the query 2 to use DB_ID. I changed back to original which I knew would fail and yes, that fails too.
So what is going on here? I am running SQL Server 2008R2 Express. Could that be it? I am just learning SQL Server. I work mostly with Oracle.

sgehret: If the directory doesn't exist, create it. If the directory exists (and there's enough free space on the disk for a new database), start services.msc and see what user the SQL Server service is running under, then look at the permissions on the directory to check the service user has full access.

TomThomson SSC Guru Points: 104773 More actions · Answer 11

Revenant (3/7/2013)
I am nominating this for the Most Misguided QotD of the Year Award.

I think it's a poor fifth in that race.

Tom

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 12

Interesting question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 13

(Bob Brown)

SSCrazy

Points: 2705

March 8, 2013 at 6:15 am

#1594936

Interesting question, thanks so much!

Miles Neale SSChampion Points: 13147 More actions · Answer 14

L' Eomot Inversé (3/8/2013)
Revenant (3/7/2013)
I am nominating this for the Most Misguided QotD of the Year Award.
I think it's a poor fifth in that race.

Tom,

This being early March, your comment says volumes about the QotD so far this year.

The thing I must say about all those questions is that each has given the community a great platform for discussion about topics and uses that might not have taken place without the poor questions.

M.

Not all gray hairs are Dinosaurs!

sgehret 2557 Valued Member Points: 66 More actions · Answer 15

Hi,

It's official. I'm an idiot. I have my local SQL Server setup to create data files on my D drive due to space issues. But I connected to a database on another server to test this question. Big duh! on my part. When I finally figured it out and ran it on my Server and saw the correct results.

Sorry for any inconvenience. Thanks for your information.

Take care,

Scott

CREATE statement

Cookies on SQLServerCentral