Re: Aliasing Problem In SQL Query - Database Discussion Boards

Re: compare two tables in different environments.

Om Prakash Pant14-Mar-11 2:04

Om Prakash Pant

14-Mar-11 2:04

Please check the following link which might help you:

http://www.sqlteam.com/article/comparing-tables[^]

Om Prakash Pant

Maintaing DB

Hum Dum8-Mar-11 23:45

Hum Dum

8-Mar-11 23:45

folder where files arrive in every two hrs.
Sp i created read files in every 2 hrs from folder and moves files in separate folder.

When reading files it inserts approx 11k - 12k lines in table.
Now this has to be run everyday at every two hour.

So, What measure should i take from slowing down queries result as days, months, year passes by?

One i have in mind run a SP at every 10-15 day interval which will copy this table data into another table and removes rows?

Your ideas ?

Re: Maintaing DB

Wendelius9-Mar-11 0:20

Wendelius

9-Mar-11 0:20

If I understood correctly you're concerned about the performance when the amounts of data grow. Based on the rough information you provided, few opinions:
- correct indexing strategy must be implemented (depending on the needs)
- I wouldn't use separate tables since it would affect program logic
- think carefully about the table structure, optimize it for typical use-cases
- consider using partitioning.

The need to optimize rises from a bad design.My articles[^]

Re: Maintaing DB

Hum Dum9-Mar-11 0:41

Hum Dum

9-Mar-11 0:41

You got it right.

I think about moving data to separate table as query which has to run on table, will need only information of 1 previous day , today, 1 after day on any given day.

Like if run my queries today i.e 09 Mar 11 17:04 PM
then query will look for data of 8, 9, 10 march

that's why i think about moving data after 10 days to separate table.

Mika Wendelius wrote:
- correct indexing strategy must be implemented (depending on the needs)
- I wouldn't use separate tables since it would affect program logic
- think carefully about the table structure, optimize it for typical use-cases

If apply all these will it still work after 5 year (assuming 12000 * 12 * 365 * 5 rows) ?

Mika Wendelius wrote:
consider using partitioning.

I don't have any clue about this?
Will be helpful if you point to an article (targeting less then have DBA exp)

Re: Maintaing DB

Wendelius9-Mar-11 0:55

Wendelius

9-Mar-11 0:55

Hum Dum wrote:
I think about moving data to separate table as query which has to run on table,
will need only information of 1 previous day , today, 1 after day on any given
day.

This makes things even easier and goes back to good indexing. So if the new rows are always added to the end in your timeline, you could consider using clustered index. This would make queries selecting the near past very efficient, see: http://msdn.microsoft.com/en-us/library/ms190639.aspx[^]

Hum Dum wrote:
If apply all these will it still work after 5 year (assuming 12000 * 12 * 365 *
5 rows) ?

Roughly 25 million rows, as long as the structure and indexing are well done you should be safe, of course depending on the requirements Smile | :)

Basically the earlier rows are kinda a dead mass if you don't query them as long as you have a good access path to the few rows you need

Hum Dum wrote:
don't have any clue about this?
Will be helpful if you point to an article
(targeting less then have DBA exp)

Here's one starting point: http://msdn.microsoft.com/en-us/library/ms178148.aspx[^]

Hopefully these help,

mika

The need to optimize rises from a bad design.My articles[^]

Re: Maintaing DB

PIEBALDconsult9-Mar-11 1:58

PIEBALDconsult

9-Mar-11 1:58

What I have done in the past is to periodically delete rows older than some threshold. If you like, you can use a trigger to also write them to some archive table.

Aliasing Problem In SQL Query

Anubhava Dimri8-Mar-11 22:24

Anubhava Dimri

8-Mar-11 22:24

Hello Everybody,

I have generate a problem While Aliasing Query.

SQL Command is : Select S_no as S. No. From Emp;

If you can think then I Can.

Re: Aliasing Problem In SQL Query

Luc Pattyn8-Mar-11 22:37

Luc Pattyn

8-Mar-11 22:37

the alias name should be a valid identifier, no punctuation, no spaces,...

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.

Re: Aliasing Problem In SQL Query

Simon_Whale8-Mar-11 22:55

Simon_Whale

8-Mar-11 22:55

Select S_no as [S. No.] From Emp;

would work but as Luc said that its not best practice.

As barmey as a sack of badgers

Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.

Re: Aliasing Problem In SQL Query

Anubhava Dimri8-Mar-11 22:58

Anubhava Dimri

8-Mar-11 22:58

It's Not working..............

If you can think then I Can.

Re: Aliasing Problem In SQL Query

Simon_Whale8-Mar-11 23:01

Simon_Whale

8-Mar-11 23:01

what sort of error message did you get?

As barmey as a sack of badgers

Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.

Re: Aliasing Problem In SQL Query

Anubhava Dimri8-Mar-11 23:08

Anubhava Dimri

8-Mar-11 23:08

Error is :

Invalid bracketing on name 'S. No.'.

If you can think then I Can.

Re: Aliasing Problem In SQL Query

Simon_Whale8-Mar-11 23:18

Simon_Whale

8-Mar-11 23:18

try

select columnName as S_NO from tablename

As barmey as a sack of badgers

Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.

Re: Aliasing Problem In SQL Query

Mycroft Holmes8-Mar-11 23:30

Mycroft Holmes

8-Mar-11 23:30

You're real problem is that you are putting UI information into the query, this type of formatting belongs in your user interface no the database

Oh and this does work in sql server 2005/08

SELECT ship [S. No.] FROM Ship AS S

Never underestimate the power of human stupidity
RAH

Re: Aliasing Problem In SQL Query

Wendelius9-Mar-11 0:27

Wendelius

9-Mar-11 0:27

Try adding quotation marks around the alias:

Select S_no AS "S. No." From Emp;

The need to optimize rises from a bad design.My articles[^]

Re: Aliasing Problem In SQL Query

venkynataraj9-Mar-11 0:52

venkynataraj

9-Mar-11 0:52

In SQL query '.' can be used to get a column from a table...

For eg : "Select Employee.EmpId from Employee"

So Sql always expects a column name after a '.' , use some other characters as alias names.

This would solve your problem

Re: Aliasing Problem In SQL Query

Prasanta_Prince15-Apr-11 1:00

Prasanta_Prince

15-Apr-11 1:00

Use a valid identifier. Dot is not a valid identifier.

Re: Aliasing Problem In SQL Query

Anubhava Dimri15-Apr-11 1:06

Anubhava Dimri

15-Apr-11 1:06

read complete summery of this question. Do't waste your time in Closed Questions.

If you can think then I Can.

Transactional Replicated Databases And Changing Schema

wizardzz8-Mar-11 6:50

wizardzz

8-Mar-11 6:50

Let's say I have a publisher and subscriber database set up. If I want to say, add a column to a table in publisher, will the change automatically be replicated to the subscriber database? Or is that only for data?

"Life should not be a journey to the grave with the intention of arriving safely in a pretty and well preserved body, but rather to skid in broadside in a cloud of smoke, thoroughly used up, totally worn out, and loudly proclaiming "Wow! What a Ride!"
— Hunter S. Thompson

Re: Transactional Replicated Databases And Changing Schema

Wendelius8-Mar-11 8:21

Wendelius

8-Mar-11 8:21

Modifications to the structure can also be replicated from publisher to subscribers but with several limitations. In short you can make 'minor' changes to the tables in publisher database and if those tables are included in a publication, modifications are repeated at subscribers. For more detailed information I think you should read through this: http://technet.microsoft.com/en-us/library/ms151870.aspx[^]

The need to optimize rises from a bad design.My articles[^]

Re: Transactional Replicated Databases And Changing Schema

wizardzz8-Mar-11 8:34

wizardzz

8-Mar-11 8:34

Exactly the link I needed, I wish I had found it on my own. Thank you for your time.

Re: Transactional Replicated Databases And Changing Schema

Wendelius8-Mar-11 11:24

Wendelius

8-Mar-11 11:24

You're welcome Smile | :)

The need to optimize rises from a bad design.My articles[^]

Unable to create database programmatically

Helfdane7-Mar-11 10:44

Helfdane

7-Mar-11 10:44

Hi there,

I'm trying to create a database programmatically using .Net3.5 and C#. I have this nice SQL-script which works fine in the SSMS editor, but now to try it for real in C#. I learned that the GO-statements were only for the SSMS editor, so I removed those. (am I right on that one?). What am I missing here?

Context: MSSQL 2008R2, VS2008, .Net3.5, WindowsXP.

Below is my script. The error I get is the following:

Incorrect syntax near 'LOG'.\r\nDatabase 'dbName' does not exist. Make sure that the name is entered correctly.

The source I use to execute this script is here. (removed the try/catch/finally blocks etc)

SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
string initDBQuery = string.Format(InstallationHelper.CreateDBScript(), settings.DatabaseLocation, settings.Database, settings.DatabaseUser, settings.DatabasePassword);
SqlCommand command = new SqlCommand(initDBQuery, conn);
command.ExecuteNonQuery();

This is the script (in C#-form):

            string script = 
            @"
            CREATE DATABASE [{1}] ON  PRIMARY ( NAME = N'{1}', FILENAME = N'{0}\{1}.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB );
            
            LOG ON ( NAME = N'{1}_log', FILENAME = N'{0}\{1}_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%);

            ALTER DATABASE [{1}] SET COMPATIBILITY_LEVEL = 100;

            ALTER DATABASE [{1}] SET ANSI_NULL_DEFAULT OFF ;

            ALTER DATABASE [{1}] SET ANSI_NULLS OFF ;

            ALTER DATABASE [{1}] SET ANSI_PADDING OFF ;

            ALTER DATABASE [{1}] SET ANSI_WARNINGS OFF ;

            ALTER DATABASE [{1}] SET ARITHABORT OFF ;

            ALTER DATABASE [{1}] SET AUTO_CLOSE OFF ;

            ALTER DATABASE [{1}] SET AUTO_CREATE_STATISTICS ON ;

            ALTER DATABASE [{1}] SET AUTO_SHRINK OFF ;

            ALTER DATABASE [{1}] SET AUTO_UPDATE_STATISTICS ON ;

            ALTER DATABASE [{1}] SET CURSOR_CLOSE_ON_COMMIT OFF ;

            ALTER DATABASE [{1}] SET CURSOR_DEFAULT  GLOBAL ;

            ALTER DATABASE [{1}] SET CONCAT_NULL_YIELDS_NULL OFF ;
 
            ALTER DATABASE [{1}] SET NUMERIC_ROUNDABORT OFF ;

            ALTER DATABASE [{1}] SET QUOTED_IDENTIFIER OFF ;

            ALTER DATABASE [{1}] SET RECURSIVE_TRIGGERS OFF ;

            ALTER DATABASE [{1}] SET  DISABLE_BROKER ;

            ALTER DATABASE [{1}] SET AUTO_UPDATE_STATISTICS_ASYNC OFF ;

            ALTER DATABASE [{1}] SET DATE_CORRELATION_OPTIMIZATION OFF ;

            ALTER DATABASE [{1}] SET PARAMETERIZATION SIMPLE ;

            ALTER DATABASE [{1}] SET  READ_WRITE ;

            ALTER DATABASE [{1}] SET RECOVERY SIMPLE ;

            ALTER DATABASE [{1}] SET  MULTI_USER ;

            ALTER DATABASE [{1}] SET PAGE_VERIFY CHECKSUM  ;

            USE [{1}];

            IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [{1}] MODIFY FILEGROUP [PRIMARY] DEFAULT;
";

A good programmer is someone who always looks both ways before crossing a one-way street. (Doug Linder)

Re: Unable to create database programmatically

Wendelius7-Mar-11 11:56

Wendelius

7-Mar-11 11:56

Helfdane wrote:
I learned that the GO-statements were only for the SSMS editor, so I removed
those. (am I right on that one?).

Yap, that's correct.

Helfdane wrote:
Incorrect syntax near 'LOG'.\r\nDatabase 'dbName' does not exist. Make sure that the name is entered correctly.

The semicolon is used to end a statement so in your database creation statement the LOG portion is part of the CREATE DATABASE statement. Try removing the extra semicolon from the first row:

CREATE DATABASE [{1}] ON  PRIMARY ( NAME = N'{1}', FILENAME = N'{0}\{1}.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
   LOG ON ( NAME = N'{1}_log', FILENAME = N'{0}\{1}_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%);

Haven't tried to execute a script using SqlCommand but hopefully it'll work. If it doesn't, you should split the statements to separate executions. Just a thought: From error-handling point of view it could better to use separate executions.

The need to optimize rises from a bad design.My articles[^]

Re: Unable to create database programmatically

Helfdane8-Mar-11 1:24

Helfdane

8-Mar-11 1:24

You're a lifesaver! Thanx a million!

A good programmer is someone who always looks both ways before crossing a one-way street. (Doug Linder)

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.