Best Practices: Changing Values

Jun 3, 2004

What is the best way to design your tables in cases where field values change?

Example:
CREATE TABLE Product (ProductID INT, Description VARCHAR(32), Price SMALLMONEY...);

CREATE TABLE Purchase (PurchaseID INT, ProductID INT, Quantity INT);

Since price obviously change over time, I was wondering what the is the best table schema to use to reflect these changes, while still remembering previous price values (like for generating reports on previous sales...)

is it better to include a "Price SMALLMONEY" field in the purchases table (which kind of de-normalizes it) or is it better to have a separate ProductPrice table that keeps track of changing prices like so:

CREATE TABLE ProductPrice (ProductID INT, Price SMALLMONEY, CreationDate DATETIME...);

and have the Purchase table reference the ProductPrice table instead of the products table?

I have used both methods in the past, but I was wanted to get other peoples' take on it.

Thanks

View 3 Replies


ADVERTISEMENT

Changing NULL Values...

Mar 8, 2000

I have a field called Trainee1 and there are about 3 in every 10 records that the value is <NULL> - I want to change that value to 0.

I have tried to use this command but it changed EVERY record to <NULL>

update Trainee1 set RegId=replace(RegId,'NULL','0')

Has anyone any suggestions on how to just change the records where the value of traineeId is <NULL>???

Thanks in advance,


Anthony

View 2 Replies View Related

Changing Values In A Record

May 2, 2004

I have the problem of dynamically changing a field value in a row depending on the value of the same field in the previous row, assuming the table is sorted on that field.
:(
You may consider that field as a key of same sort as other values in the table do not help in selecting them uniquely.
Ex.
Before After
130 -> 130
130 -> 130A
140 -> 140
140 -> 140A

View 5 Replies View Related

Changing Default Values

Jul 20, 2005

How can i change the default value of a column? I already have a columnnamed DateOfRental but I want to alter it so that it has default valuegetdate()ThanksDavid--http://www.nintendo-europe.com/NOE/...=l&a=Prodigious

View 4 Replies View Related

Autogrowth Values Keep Changing

Sep 21, 2006

Hi!

We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.

What am I missing here?

BR John

View 17 Replies View Related

Changing Values Returned...Newbie Needs Help.

May 29, 2008

Hi Guys,

I have had a look through the forum and I can't seem to be able to find how to do this... I have a vague recollection of achieving this before but it was many years ago since I've done any sql.

I have a table like this

StaffID JobNumber Status
------- --------- ------
1 50 'In Progress'
2 20 'On Hold'
3 30 'Completed'
4 40 'Completed'


what I want to be able to do is change the status to something common if it is not 'Completed' I don't want to update the database though, I just want to return it in a select statement. Is this possible.

My output would be something like;

StaffID JobNumber Status
------- --------- ------
1 50 'NOT Completed'
2 20 'NOT Completed'
3 30 'Completed'
4 40 'Completed'

Many thanks in Advance.

Bobg

View 3 Replies View Related

Changing Null Values To Blanks

Aug 15, 2013

Aim- the following columns “Cancel_Date”, “First_Post_Date”,“Last_Post_Date” can either be populated with figures or have a null value.

I want all Null values to be removed and be replaced with a blank

Final table is

Select *
from #test
left join #SF_AccountBuild on #test.FDMSAccountNo = #SF_AccountBuild.[External FDMSaccountno]

Results

[FDMSAccountNo],
External_ID ,
Parentsfid,
[DBA Name],
[Legal Name],
Street,
[MM3-DBA-ADDR2],

[Code] .....

View 5 Replies View Related

Changing Field Values In A Stored Procedure

May 4, 2003

Here's the deal:

I import a flat file from a legacy system, and then convert it into a single table. That works simply enough.

Then I have a SP that querys that table using a parameter for an accountID. My business tier calls that SP and returns the results to the calling tier (my web application). Easy enough...

Now for the question. The people who created the flat file (written in COBOL) decided to use "codes" to represent data. So, for instance, if I'm looking for the account plan, I can expect to see characters like ], or [, or +, etc... These characters have a special meaning, like:

] = Plan A
[ = Plan B
+ = Plan C, and so on.

Currently, the web application displays those characters, but I want it to display the actual plan name. Is there a way that when I execute the SP, the SP could pull the necessary records, and whenever it encounters a certain "plan" character, it could convert it into a "readable" name? Say that it sees that the plan_type field has a value of "]" for twenty records, so it converts those twenty records' plan_type value from "]" into "Plan A"? I'm not sure if I can do that, but I want to at least evaluate the option if I can.

I've evaluated other options, like using a CASE statement in my code, but I shot that down quickly...for obvious reasons. I don't wanna be changing my web application or business tier each time these guys update a plan name, or add a new one, delete an existing one, etc...

I've also thought about creating a dictionary table than contains the plan's code and its name, and then just INNER JOIN the first table with the dict table. This would keep my SP very simple (it's very straight-forward right now, and I like that). That way, if a plan name is ever changed, or a new one is added, I simply update the dict table using a simple query. However, if my SP is doing the conversion, I could just as easily update the SP.

Either of these methods would work for me, and I *do* know how to do the latter (dict table). However, there are quite a few other fields that I may have to do this for. I believe when I left for the day on Friday, my last count was 14 fields total that needed translation. That would mean 14 different dict tables! That could certainly affect my SP performance with all those INNER JOINS!

Therefore, I'm certainly interested in figuring out if it's possible to do the former method (SP), and then I shall decide which method is best for my situation.

Feel free to include your thoughts on which process you think is better as well. I'm really riding the fence with this one. However, if I can't find out how to change field values in my SP, then obviously I'll make a decision very quickly...

Thanks in advance.

View 3 Replies View Related

SQL Security :: Virus Changing Values In Field

Sep 18, 2015

I've got this virus URL... in our production server. It is changing the values in a table column.

View 6 Replies View Related

Changing Column Values On Table Insert Trigger

Nov 7, 2006

Hello,i have a table and if a record is inserted i will test a numeric valuein this table. If the this value is greather as 1 million, than anstatus column should be changed from 'A' to 'B'.Yes and sorry, this is a newbie question.On Oracle this work simple:create trigger myTrigger on tableXasbeginif :old.x 100000 then:new.y:='B'end if;end;ThanksMaik

View 2 Replies View Related

Inserting Values Into SQL CE 3.5 Ntext Field Is Changing All Rows...

Apr 25, 2008



Has anyone seen this issue before? We are running a SQL CE 3.5 database on a windows desktop. A couple of our tables have ntext fields. When we do an insert the statement updates the value for all rows, not just the one that was added. I can easily repro this with some of the online samples too. Try the following:


SqlCeConnection conn = new SqlCeConnection(_sConn);

conn.Open();

SqlCeCommand cmd = conn.CreateCommand();

cmd.CommandText = "CREATE TABLE BlobTable(name nvarchar(128), blob ntext);";

cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO BlobTable(name, blob) VALUES (@name, @blob);";

SqlCeParameter paramName = cmd.Parameters.Add("name", SqlDbType.NVarChar, 128);

SqlCeParameter paramBlob = cmd.Parameters.Add("blob", SqlDbType.NText);

paramName.Value = "Name1";

paramBlob.Value = "Name1 Memo";

cmd.ExecuteNonQuery();


cmd.CommandText = "INSERT INTO BlobTable(name, blob) VALUES (@name, @blob);";

SqlCeParameter paramName = cmd.Parameters.Add("name", SqlDbType.NVarChar, 128);

SqlCeParameter paramBlob = cmd.Parameters.Add("blob", SqlDbType.NText);

paramName.Value = "Name2";

paramBlob.Value = "Name2 Memo";

cmd.ExecuteNonQuery();



After the second execution the blob column in both rows will have the value 'Name2 Memo'.

This is obviously a huge problem for us and would appreciate it if someone can explain what is happening. Seems like a bug but would like to be certain before I go the support route.


View 1 Replies View Related

Merge Replication - Changing Values From Filtered Column

Nov 6, 2006

Hi

I'm working with merge replication between Sql Server 2005 and Sql Server 2005 Mobile. I'm using dynamic filtering by function HOST_NAME (... where Table.FilteredColumn = HOST_NAME())

When I'm trying to chagne values from filtered column on the client side (Table.FileteredColumn) I get error message that the column is read-only.

How can I change data in filtered column? Is this possible?

 

View 3 Replies View Related

Changing The Report Parameters Or Data Sources To The Values You Specified Is Not Allowed

May 24, 2007

I have a report I want to modify and when I go to deploy it I get:



Changing the report parameters or data sources to the values you specified is not allowed. The report is configured to run unattended. Using the specified values would prevent the unattended processing of this report



Now I understand the error, it is because on the report server's web management interface I entered credentials for the report to run that point to a custom datasource and supplied the appropriate credentials to run the report.



However, I don't want to have to reconfigure that just to deploy a slightly modified version of the report.



How can I get around this? Or what is the proper work around? I just want to change the background color from dark gray to light gray on a header column....



Thanks!

View 5 Replies View Related

Importing From Excel Into SQL Server 2005 Changing Values To NULL

Mar 9, 2007

I can't figure this one out...

I have an Excel spreadsheet (Excel 2003 format) with a column of values. The column is formated to General (although I've tried it formated to text). The values are alphanumeric. However, there are a few values that are completely numeric.

Example:

_ITEMNUMBER_

1-528

K214-5

184PR

45678

As can be seen, the last value is completely numeric. I am importing into a table with one column that is formated as nvarchar(50).

I use dtswizard.exe and do a simple import into the table and the alphanumeric values import just fine but anytime a value is completely numeric it has a NULL value in the table instead of the value that should be there.

I've tried changing the format of the column to nchar, char, etc, etc. Always comes in NULL.

Any ideas?

View 7 Replies View Related

QN Best Practices

Sep 18, 2007

Hi everyone,

I wonder if somebody here could recommend a good article about MS Service Broker. I'm looking for some advice and tips in designing applications using SQL Service Broker, mainly QN. For instance, maintenance routines and common faulty scenarios I might find later when my solution is implemented. I have googled for a while but all I can find are recopied examples of QN.

Thanks

View 3 Replies View Related

SqlDataSource Best Practices

Jun 20, 2008

Hi I use data presentation controls like gridview, formsview in my application. In many of the webforms i also use multiple datasources mainly for the purpose of 2 way data binding for controls within data presentation controls.I am concerned about the performance issues this might cause as users using these pages increase.What is the likely performance impact ?Once the databind is done and values are populated in the respective controls, does the database connection of datasource control get closed, or is it open?What are the best practices while implementing datasource controls? 

View 2 Replies View Related

SQL 2000 Best Practices

Nov 16, 2001

Hi all. Thanks for taking the time to read this.

I'm looking for some documentation on SQL 2K Installation tips on a Windows 2000 Member Server platform as well as best practices for ongoing maintenance .

Real world experience as well as Microsoft propaganda are all welcome.

Thanks again.

View 1 Replies View Related

Best Practices ( Installation )

Dec 7, 2001

Could some one give best SQL 2000 install practices such as -
1) SQL app, should this be in OS drive or not ( pros, cons )
2) Should OS be on Raid 1

etc

Thanks,

View 3 Replies View Related

Best Practices To Optimize A DB ?

Sep 1, 2004

What are the best pactices to optimize performance accsiing an SQLServer DB ?

commands, mantenance plan...

Thanks

View 1 Replies View Related

DDL Best Practices Question

Jul 20, 2005

I am looking for some examples of how to manage DDL scripts amongvarious versions of a production db and development and testing. Ihave tried a few things in the past, and it always gets very muddledand cumbersome.I need to be able to build any version of the database from scratch,BUT I also need to maintain an upgrade path from any version to anylater version. So it is not enough to just maintain a master buildscript, but I don't want to maintain 2 different things (modify themaster build scripts AND create a new "ALTER" script for each versionchange).I thought I had seen an article somewhere that layed out a process formanaging this, but I can't find it now (I thought it was in SQL ServerMag). Does anybody know of this article or have a resource they couldpoint me to that outlines best practices in this area?Thanks,Jason Wood, DBA in training.

View 1 Replies View Related

Best Practices For Large DB

Jul 20, 2005

Hi All,My question is what are the best practices for administering largeDBs. (My coworker is the DB administrator. I'm more of thedeveloper. But slowly being sucked in.) My main concern is that wehave some DBs that take approx 3 hrs a night just to rebuild theindexes. I know that with MSSQL 2000, I can use partitioned views tobreak out the table(s) into smaller databases and tables. But we alsohave an older server that runs MSSQL 7. Lastly how do you handledrive space issues? Do you spread out the DB across multiple MDFfiles on different drives? Thanks in advance.

View 1 Replies View Related

Encryption Best-Practices

May 31, 2006

Please forgive me if I have overlooked a thread that answers this question, but I assure you that I have looked.

I would really appreciate a guide of sorts that would tell me the correct steps to take to properly secure a column in my database. I don't need specifics on how to do each step, I either have those already or can find them myself. In fact, I have already successfully encrypted and decrypted some data. I just want to make sure that I create the right keys and certificates and that I follow best-practices as far as backups and stuff is concerned.

Thanks,

Todd Sparks

View 1 Replies View Related

Best Practices Question

Jun 13, 2007

Environment is SQL Server 2005 x64 Enterprise running under Windows Server 2003 x64 Enterprise with four processors and 16GB of ram.

I have 28 data copy routines I would like to add to a SSIS package. They use the Data Reader Source to an ODBC database (InterSystems Cache) and copy the table contents to a SQL2005 database for reporting needs. The data rows in these 28 routines range from only 100 rows to over 6 million rows depending on the table. I have tested these individually and they work fine. My question is, is it a good practice to have all of these routines in a single package or can I expect performance degragation?

View 4 Replies View Related

Tracking Changes Best Practices

Nov 5, 2007

I've got a table that has frequent updates to it. I want 100% change tracking on this table though, so we can rollback to any previous version, or just see any changes people make.

Is there a best practice for things like this? Currently, I'm using a trigger on UPDATE to take the previous values and store them in a history table. This keeps track of who changes what, and when. Plus the most recent data is seperate and more performant to access.

I've also heard about putting an 'IsActive' flag on the main table and any changes that are made just get marked as In-Active and a new record gets added.

Any input?

Thanks!

View 14 Replies View Related

Best Practices For SSIS

Mar 27, 2008

I am new to SSIS, but done alot of DTS 2000 development.

What is the concensus for developing SSIS packages? Do you just place objects and change the properties of each object, having multiple objects basically doing the same thing, with different properties? Or do you set object's properties and then change properties by code in scripts? Ie Execute SQL, setting connections and SQL Statement by code in a script? Is this even possible? With Microsoft OOP I assume this is possible.

Script> Set properties of ExecuteSQL > set flow to ExecuteSQL.

Thanks!

View 4 Replies View Related

Upserts Best Practices

May 19, 2007

Is this this only way to do it in SSIS?
http://sqljunkies.com/WebLog/ashvinis/archive/2005/06/15/15829.aspx
For some reason I figured that SSIS would have this kind of stuff built into it, it seems a function that many would use.

View 1 Replies View Related

Best Practices On Maxinsertcommitsize

Mar 23, 2007

Hi,

I wonder if anyone knows what would be the best case scenario for the property 'maxinsertcommitsize' for the sql destination task if I want to load 6m records into a target. Is the best setting 0 (try loading all in one batch) or should I choose a different value for example 1000000 per batch?

Thanks,

Marc

View 4 Replies View Related

Best Practices For SQL Data Access

Nov 1, 2007

Hi,
i am newbie in ASP.net world. i am using 3 tier application architechture for my web based application. data base is sql server 2000. i have looked at object and sql datasource objects but i think they are not suitable for my requirements. so i am planning to directly use ado.net to access data from database.( i.e. creating connection, then creating commands n executing them)
now what i am looking for is the best known practices for the above task. i have following solutions in my mind please let me know if i am missing some or which could be the best aproach.


careate one class which will handle all the database requests so that all the pages and business objects request that class to to do all the db related stuff. (creating connection, command n execution)

View 4 Replies View Related

Best Practices Question For Outputting

Sep 19, 2005

Hey guys,

Little bit of a newbie question here...I have a database with about 20
or so tables in a relational model.  I am now working on an output
scheme and had a quick question regarding best practices for
outputting.  Would it be best to

1) Set up a view that basically joins all of these tables together, then bind a DataSet/DataTable to it and output as needed?
2) Setup individual views for each table and run through them?

Thanks for the help!

e...

View 2 Replies View Related

SqlCommand Parameters Best Practices

Feb 23, 2006

I was writing a simple sign up class the other day and coded
a Register method with a SqlCommand and Parameters like this:

            SqlCommand sqlCmd =
new SqlCommand("Regester", sqlConn);
            sqlCmd.CommandType =
CommandType.StoredProcedure;
           
           
sqlCmd.Parameters.Add("@UserName", SqlDbType.VarChar, 55);
           
sqlCmd.Parameters["@UserName"].Value = userName;

           
sqlCmd.Parameters.Add("@Firstname", SqlDbType.VarChar, 55);
           
sqlCmd.Parameters["@Firstname"].Value = fName;

             etc...

Seems pretty straight forward right? A senior coder writes his params like this

            SqlParameter pUserName
= new SqlParameter("@UserName", SqlDbType.VarChar, 55);
            pUserName .Value =
address2;
           
sqlCmd.Parameters.Add(pUserName );

            SqlParameter pFName =
new SqlParameter("@FName", SqlDbType.VarChar, 55);
            pFName .Value = city;
           
sqlCmd.Parameters.Add(pFName );

            etc...

What is the benefit, if any, of creating a new instance of SqlParameter for
each value passed to the sp?

How could I easily test the code for speed and resource use besides tracing?

View 4 Replies View Related

Backup Libraries - Best Practices

May 5, 2005

We are preparing to stand up a server with an HP tape library backup system using Data Protector software.

Our intention is to copy backup files from each of our db servers to the file system of the server with the tape library. There will be two tape rotation schemes where there will be a daily offsite tape and one with a monthly offsite rotation for less critical applications. Our systems group isn't happy about that configuration but are willing to implement what we want.

My questions are:
Is there any one out there using one of these and if so can you give me a 10,000 foot view of your process?

Addtionally, is this the best way to utilize this resource?

Any serious suggestions and comments are greatly welcomed and appreciated.

Sidney Ives
Database Administrator
Sentara Healthcare.

View 1 Replies View Related

SQL Server Best Practices Anlyzer

Nov 22, 2004

Hey Gang, I got a question about authentication. I have a Just loaded SQL Server on my virtual box and loaded Microsofts bpa and microsoft security anlyzer. I get a funny reading. The security scan work but the bpa scan does not. I also look at the database that I can get access to and notice a new database schema. I was thinking if I remove this database will it have any affect on the present database.

View 1 Replies View Related

Best Practices: GROUP BY Clause

May 20, 2004

I was wondering what the best way to write a GROUP BY clause when there are many (and time consuming) operations in the fields by grouped.

Fictious example:

SELECT DeptNo, AVG(Salary) FROM Department GROUP BY DeptNo;

This will give me the average salary per department. Let's say, however that
I had 10-15 fields being returned (along with the AVG(Salary)) and some fields even had operations being performed on them. Is it better to create a temporary table to calculate the sum per department (or a VIEW) and then
perform a JOIN with the rest of the data?

Fictious example:

SELECT DATENAME(y, StartDate), DATENAME(m, StartDate), DATEPART(d, StartDate), SUBSTR(DeptName, 1, 10), SomeFunction(SomeField), SomeFunction(SomeField), AVG(Salary)
GROUP BY DATENAME(y, StartDate), DATENAME(m, StartDate), DATEPART(d, StartDate), SUBSTR(DeptName, 1, 10), SomeFunction(SomeField), SomeFunction(SomeField);

Am I better off writing my query this way or using a JOIN on some temporary table or view?

Thanks

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved