Database Discussion To Change Perpective

Aug 2, 2006

Hello

I have been doing relational database forever(or a long time) and have been intruduces to a team that uses a highly normalized database(propietary) to manage workflow.

We are capturing data in an AUDIT Trail EAV format.(500 million rows)

It is my task to build this into a data warehouse for reporting and I need to have with my team a relational database discussion. The relational database knowledge on this team is DB2 based, IDMS, and other past evolutions.

The common processes used are recieve a flat file and process this file sequentially using C# or VB doing lookups of other databse tables and writing out another flat file to be converted in XML for load to the propritary system.

My goal is to attempt to introduce new design concepts to my team and these are some talking points that I have come up with for a lunch and learn session.

can anyone else add to this list I don't want to get into a deep discussion about 3rd NF, Star Schemas vs Snowflake, etc.. I want to keep is informational and light to eliceit discussion and relat it back to older technologies.

some of the topics we can discuss are:
Why the data warehouse
Real-time tables what needs to stay in prod
What is going to happen to reporting database
Interaction between database on the same cluster/server
Interaction between databases on different servers (linked servers not allowed)
Set processing as opposed to cursor processing.
Table types
EAV
Type1
Type2
Fact
Dimensions
Code

View 1 Replies


ADVERTISEMENT

NEW SQL DISCUSSION GROUP

Jul 5, 2001

Dear all,

There is now a NEW SQL discussion Group currently being tested;
http://www.baysignia.com/discussions/discussions.xml
Try it out and send comments to faq@baysignia.com

Many Thanks for your comments

SQL DBA
baySignia Systems

View 1 Replies View Related

Index Discussion

Mar 23, 2004

Hi Folks,

Got a topic open for debate.

We currently have an archive table - DDL

CREATE TABLE [dbo].[Audit] (
[id] [int] identity (1,1) NOT NULL ,
[col1] [char] (10) NOT NULL ,
[col2] [char] (15) NOT NULL ,
[col3] [int] NOT NULL ,
[col4] [varchar] (50) NOT NULL ,
[col5] [datetime] NOT NULL ,
[col6] [varchar] (4000) NULL ,
[col7] [char] (3) NULL
)
GO


This table grows to about 40 million rows during the course of the month. The table has a clustered index on the id field and a non clustered index on the col2 and col3. The id column is not used in queries. At the moment we run weekly dbcc reindexes on all the indexes. We are running into a space issue on the reindex of the clustered index (copying the whole table out , ordering etc) and are considering dropping the index or changing to a non clustered index. (The DBCC utility that we have built will only rebuilt all the indexes or none at all.)

I feel this is not a good idea and know my reasons. I would like some input as to why this might prove a bad idea.

Will it increase page splitting? Will the table performance be impacted even if the queries are not specifically using the clustered index?

What are the reasons for and against?

Thanks Folks

View 14 Replies View Related

Discussion Forum - Datastructure

Jul 27, 2005

I'm creating a discussion forum for my website, using Sql Server 2000. I need to display 'Number of threads', 'Number of posts', 'Last post by' (username/id and date) for each forum, and 'Number of replies' and 'Last post by' (username/id and date) for each thread.Here are a couple of ideas I have come up with to solve this problem:1) Poll the database (using a stored procedure that returns the number I'm looking for) for each forum when I loop all the fourms. - I suspect this approach isn't optimal, since it creates more traffic to the database.2) Have fields in my Forum and ForumPost tables for 'Number of threads' and so on. Now, create triggers that updates these fields every time a post is made. - I guess this would be much more effective than the first apporach, since everything is done on the database server directly.Are there any other ways that are better? Please advice! Thanks a bunch for any help!

View 7 Replies View Related

Fill Factor -discussion

Apr 4, 2001

What are the criteria to decide the optimal(or close to optimal) fill factor?
Any input is appreciated.

View 2 Replies View Related

MS Access Vs. SQL Server 7 Discussion

Mar 29, 2000

I'm sure this has been a topic in the past. I would like to build a fact sheet about each one and do a compairison. Are there any articles/links/resources out there that speak to this issue?

Thank you,
Nathan

View 2 Replies View Related

Discussion On The TEXT Datatype And ASP

Sep 22, 1998

OK, I`ve been researching the use of the TEXT datatype all day and would
like opinions on what I`ve found.

First, a little background. I have been tasked with writing an ASP
application to handle the display of FAQs for a company`s products. I would
like to store all info in a table much like

faqID int
question TEXT
answer TEXT

Simple enough, right? I then tried to create a stored procedure to add a
new FAQ and all hell broke loose. ASP would not pass anything larger than
255 chars to the stored procedure.

I read in the "ADO and SQL Server Developer`s Guide" from Microsoft about
using varchar datatypes of 255 chars (instead of TEXT) and chunking large
text up to fit in these smaller datatypes. This seems like a lot of work.

I also read in "Inside SQL Server 6.5" that "The text datatype is sometimes
awkward to work with. Many functions don`t operate against text, stored
procedures are limited in what they can do with text, and some tools don`t
deal with it well." (page 632). This statement concerns me greatly. How
are stored procedures limited in dealing with TEXT? Do the standard SQL
UPDATE and INSERT commands work or must READTEXT and UPDATETEXT be used
instead?

I guess my question is, what is the best way to accomplish this? I have a
feeling that others have had to do this before. Is SQL Server not meant to
handle large textual objects? Is chunking the best way to go? Will version
7.0 handle this scenario better?

Any help greatly appreciated!

--Matt Richmond
MenoX Technologies, Inc.

View 1 Replies View Related

Where Is SQL2000 Discussion List?

Mar 15, 2002

List-

I have completely lost entry into SQL2000 Discussion List with the following URL:

http://ls.swynk.com/scripts/lyris.pl

This URL taking me to internet.com testlist. I have sent a request to the webmaster but there is no responce.

Anybody know how to get into this forum? I am not able to look at my group messages since yesterday.

Thanks for Redirecting me by any other URL.

Jaganmohan Rao

View 2 Replies View Related

Table Linking Discussion

Jan 15, 2004

I would like to hear your thoughts on a philosophy I adhere to.

As a rule of thumb I've always preached that Unique Indexes are for linking tables and Primary Keys are used to ensure that records aren't duplicated. I’ve embraced this philosophy for a couple reasons, the main one being that I don’t have to create numerous foreign key fields in the foreign key table.

However I’ve done most of my programming in Access and am now in need of something more robust (SQL Server v7) and I’m wondering if I need to reconsider.

I do also have a how to question; that being is it possible to create a table join on a unique index in SQL Server v7 and if so how? I would like to have an Auto Number / Auto Incremented / Unique Identifier field in the Primary Key table that links to a numeric field in the Foreign Key table.

Thanks in advance
Dog

View 14 Replies View Related

Is Discussion Board Reply Working

Jun 18, 1999

I cannot reply to any messages of the board? Is anyone else having this problem?

I see from the board that there haven't been any replies for a few days...

Jarlath O'Grady
mailto:jogrady@swynk.com
http://www.swynk.com/friends/ogrady

View 2 Replies View Related

To DB/SQL Teachers: A Wrong Query On Northwind DB Discussion

Jul 26, 2006

Good day to all.

I'm a DB teacher in a University. Planning and developing SQL exercises for my students I found a "tricky" or extrange sql exercise.

The sql exercise using Northwind DB reads as follows:
"Wich is the total amount of the freight that corresponds to all orders containing products of seafood category ?"

The first sql code that comes to my mind was:

select sum(freight)
from orders o, "order details" d, products p, categories c
where o.orderid=d.orderid and d.productid=p.productid and p.categoryid=c.categoryid
and categoryname='seafood'

This is equivalent code constructed with Query constructor in Enterprise Manager:
SELECT SUM(dbo.Orders.Freight) AS Expr1
FROM dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN
dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID INNER JOIN
dbo.Categories ON dbo.Products.CategoryID = dbo.Categories.CategoryID
WHERE (dbo.Categories.CategoryName = N'seafood')

These 2 equivalent queries output 27722.9600 as result, but the correct result (total freight) is 23791.1400.

Did you also think (as I did) that the exposed code is correct?

Now I know the correct sql code that gives the corect answer and also the explanation why the code exposed fails, but before share it under your posible request, want to know your tech comments about this exercise in order to know if this particular exercise is "tricky" (make people to fail) or is my total fault and need to review my strategies of applying/constructing sql (and teaching).


Thanks a lot.

Carlos

View 12 Replies View Related

Real-Time Data Mining Discussion

Sep 27, 2006

I am about to prepare a paper concerning the field of real-time data mining. Real-time here means the process of incremental training of an existing model as soon as the data arrives.

There is a number of papers introducing algorithms for incremental association analysis, incremental clustering etc. Stream mining ís a field which is closely related to that. The main reason for the implementation of incremental algorithms is a) the large amount of data to be mined and b) the high rate of new data that is evolving every day.

Using classical batch mining algorithms, models that are outdated for some reason, would have to be re-trained, which could be very time consuming for billions of records. And once the training is completed, the training would have to be restarted once again because a bulk of new data has been arrived.

The question that I would like to discuss now is: For what real world applications would it be a meaningful or even essential to use real-time training of models?

Two main reasons could determine the answer to that question:

You just want to incorporate new data into existing models in order to increase the prediction accuracy of your model or
Your underlying data is subject to more or less massive changes (also refered to as concept drift) and you want to adapt your mining model continuously to that reality.

I'm looking for some examples or ideas where one of these cases apply and it would be a good idea to have incremental mining algorithms involved.

I'm looking forward to inspiring some discussion on that issue.

View 3 Replies View Related

How To Change The Data Format For A Single Database In A SQL Server Group Of Database

Nov 1, 2001

Hi all,

I have to change the date format for one database in a group of databases in my sql server 2000,Can you please tell me how to change the date format.


thanks in advance

View 1 Replies View Related

Change In Database User Perms When Copying Database

Mar 9, 2000

How to maintain Database User Permissions when copying the Database from One SQL Server to another(Either through backups or sp_detach). The reason is the login sid is different in the target server and as a result the database user is not able to map to the login existing in the target server. The only way I can correct this is through dropping and recreating the user's again and assign the permissions, or change the system catalog - sysusers to remap the login to the user in the database.
I do not wish to use the sp_addalias as it is available only for backward compatibility.
Is there a better way of doing this ?

View 4 Replies View Related

[Performance Discussion] To Schedule A Time For Mssql Command, Which Way Would Be Faster And Get A Better Performance?

Sep 12, 2004

1. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection

above, which way would be faster and get a better performance?

View 2 Replies View Related

NORTHWIND Database Was Re-created From A Different Database:How Can I Change The Entry In Sysdatabases For Database 'NORTHWIND'?

Jan 14, 2008

Hi all,

From the http://msdn.microsoft.com/en-us/library/bb384469.aspx (Walkthrough: Creating Stored Procedures for the Northwind Customers Table, I copied the following sql code:

--UpdateSPforNWcustomersTable.sql--

USE NORTHWIND

GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectCustomers' AND user_name(uid) = 'dbo')

DROP PROCEDURE dbo.[SelectCustomers]

GO

CREATE PROCEDURE dbo.[SelectCustomers]

AS

SET NOCOUNT ON;

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers

GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertCustomers' AND user_name(uid) = 'dbo')

DROP PROCEDURE dbo.InsertCustomers

GO

CREATE PROCEDURE dbo.InsertCustomers

(

@CustomerID nchar(5),

@CompanyName nvarchar(40),

@ContactName nvarchar(30),

@ContactTitle nvarchar(30),

@Address nvarchar(60),

@City nvarchar(15),

@Region nvarchar(15),

@PostalCode nvarchar(10),

@Country nvarchar(15),

@Phone nvarchar(24),

@Fax nvarchar(24)

)

AS

SET NOCOUNT OFF;

INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)

GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'UpdateCustomers' AND user_name(uid) = 'dbo')

DROP PROCEDURE dbo.UpdateCustomers

GO

CREATE PROCEDURE dbo.UpdateCustomers

(

@CustomerID nchar(5),

@CompanyName nvarchar(40),

@ContactName nvarchar(30),

@ContactTitle nvarchar(30),

@Address nvarchar(60),

@City nvarchar(15),

@Region nvarchar(15),

@PostalCode nvarchar(10),

@Country nvarchar(15),

@Phone nvarchar(24),

@Fax nvarchar(24),

@Original_CustomerID nchar(5)

)

AS

SET NOCOUNT OFF;

UPDATE [dbo].[Customers] SET [CustomerID] = @CustomerID, [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE (([CustomerID] = @Original_CustomerID));

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)

GO

====================================================================================
I executed the above code in my SQL Server Management Studio Express (SSMSE) and I got the following error messages:

Msg 911, Level 16, State 1, Line 1

Could not locate entry in sysdatabases for database 'NORTHWIND'. No entry found with that name.

Make sure that the name is entered correctly.

===============================================================================================================
I know I recreated the NORTHWIND Database from a different Database before and I did not do anything for the entry in sysdatabases. How can I change the entry in sysdatabases for database 'NORTHWIND' now? Please help and advise.

Thanks in advance,
Scott Chang

View 5 Replies View Related

Change Database Name

Jul 21, 2004

Hi, i have database in sql server called search, but today, i found it is changed to search (Single user), I don't know who did it. How can I change to mutilple users, also how to change the name back to search. Thanks.

View 10 Replies View Related

***How Do I Change A Database Name?***

Dec 31, 2006

Hi,

This is my first posting to this forum. I'm trying to
I'm trying to change the name of my database from ASPNET.mdf to CulturedStar.mdf. I need to change the physical name and logical name.

I'm working with Microsoft SQL Server Management Studio Express.

How do I do this?

Thank you so much for your time and effort ~ Paul

View 8 Replies View Related

Change The In Use Database

Mar 26, 2007

In SMSS or QA why the following code does not change the database in use to My_db? The value for @DynSQL is USE [My_db].

USE [master]
GO

DECLARE @db_name varchar(100), @DynSQL varchar(512)

SET @db_name = 'My_db'

SET @DynSQL = 'USE [' + @db_name + ']'

Print @DynSQL
EXEC (@DynSQL)
GO


It seems the change occurs inside the dynamic portion because if I SELECT a table within that database, it works. For example the following works but it does not change the default database to My_db.

SET @DynSQL = 'USE [' + @db_name + ']'+' SELECT * FROM Mytable'

View 3 Replies View Related

How To Get The Ddl Of Every Change In The Database

Mar 15, 2008

How to get the ddl of every change in the database
Includes changes in the table, column, constraints's
That is a new table added, table drop table name changed, column type change column added, primary keys , foreign key, check constraints e.t.c

I am not looking for the changes in triggers. stored procedures, functions.

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

View 8 Replies View Related

Change Database Language

Dec 30, 2006

i using sql server Express, i want to change the default language from US-English to British EnglishWhere and How?
 

View 3 Replies View Related

Database Owner Change

Jun 27, 2007

 Hello,I'm using tableadapters in VWD 2005 Express to link our ASP.NET application to a SQL Server 2000 database.  Initially, I used Database Explorer to drop tables into the DataSet object, which picks up the owner of the tables from the database.Recently, we had to change the owner of all objects in the database, including tables and stored procedures.  When we run our application it chokes on stored procedures because the owner of the SP's has changed.  My question is: how can I update tables and tableadapters in the DataSet to reflect the change in owner, without having to recreate everything in DataSet?  

View 1 Replies View Related

How To Change Owner Of Database? Please Help!

Jul 29, 2007

 Hi,I just transferred my website and database (SQL 2000) to a new host who's SQL Manager doesn't support the previous username I had for the database.The previous owner of database tables etc was Database_master and now I want to change it to just Master.Please advise! Someone else designed the website for me, and honestly I have just some basic knowledge of databases and such.I would appreciate help and if possible with detailed steps.Many thanks in advance,Isje 

View 2 Replies View Related

Change Database Password

Sep 8, 2007

I have a SQL database hosted on a server. This server supports SQL Sever 2000. How do I change the password to the password?Thanks in advance, 

View 1 Replies View Related

Change Database Time

Feb 25, 2008

Hello, is there a way to set the database to my local time?
I am having troubles using the Membership.GetNumberofUsersOnline method - it is showing users online who in test mode I have logged off, but I think due to time zone difference between my server and myself, still appear logged on.
This is causing further difficulties in using my admin functions to edit user information, because for some reason  I cannot edit a user who is logged on...
Help anyone?

View 1 Replies View Related

How Do I Change The Owner Of My SQL Database?

May 17, 2008

Hello,
 I'm trying to access the Database Diagram of a sql database I created but am getting the error:
 
"The database does not have a valid dbo user"
 I also noticed that my NT group user was incorrect and this is the name being used as the owner of the database. How do i change this?
 
Thanks

View 3 Replies View Related

Change The Owner Of The Database

Jan 10, 2001

Hi All,

I want to change the OWNER of the database to 'sa' and I executed the
following system stored procedure

EXEC sp_changedbowner 'sa'

and I get the following error message..

Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46
The proposed new database owner is already a user in the database.

Any ideas
Thanks
Reddy

View 1 Replies View Related

How To Change The Database Owner In SQL 7

Oct 18, 2000

Is there a stored procedure to change the database owner in SQL 7?

Thanks in advance,
Faustina

View 4 Replies View Related

Change Database In Procedure

Apr 4, 2008

I work at a medical billing office. We provide billing services for a number of clients. The primary billing software runs on a big AIX system, but for reporting and a few other things we get a daily dump into sql server. The way the dump happens each client get's their own database in sql server. This works great for reports that go to individual clients, but it can be a real pain for internal reports that cover all clients. We end up with sql code that looks something like this:
SQL Code:






Original
- SQL Code




ALTER PROCEDURE ProceName
AS
BEGIN
CREATE TABLE #Temp
(
Client varchar(5),
FieldName varchar(20),
OtherField int
)

DECLARE @Client varchar(5)
DECLARE @SQL varchar(8000)
DECLARE curAll cursor FOR /*SQL Code to get Client list here */
OPEN curAll
FETCH NEXT FROM curAll INTO @Client
WHILE @@FETCH_STATUS = 0
BEGIN
Set @SQL =
'SELECT ''' + @Client + ''', *'
+ ' FROM dump_' + @Client + '.dbo.TableName t'
+ ' WHERE t.FieldName=''somevalue'''

INSERT INTO #Temp
Exec(@SQL)

FETCH NEXT FROM curAll INTO @Client
END

--send results to application
SELECT * FROM #Temp

--clean up
CLOSE curAll
DEALLOCATE curAll
DROP Table #Temp
END






ALTER PROCEDURE ProceNameASBEGIN    CREATE TABLE #Temp    (       Client varchar(5),       FieldName varchar(20),       OtherField int    )     DECLARE @Client varchar(5)       DECLARE @SQL varchar(8000)    DECLARE curAll CURSOR FOR /*SQL Code to get Client list here */    OPEN curAll    FETCH NEXT FROM curAll INTO @Client    WHILE @@FETCH_STATUS = 0    BEGIN          SET @SQL =             'SELECT ''' + @Client + ''', *'        + ' FROM dump_' + @Client + '.dbo.TableName t'        + ' WHERE t.FieldName=''somevalue'''         INSERT INTO #Temp            Exec(@SQL)          FETCH NEXT FROM curAll INTO @Client    END      --send results to application    SELECT * FROM #Temp     --clean up    CLOSE curAll    DEALLOCATE curAll    DROP TABLE #TempEND


As you can imagine, this is a real pain. There must be someway to dynamically change the current database in the procedure and run the code there directly rather than creating a string and calling out to exec 50+ times. Any ideas?

View 5 Replies View Related

Change The Owner Of Database

May 26, 2004

Dear Ladies & Gentlemen,

I need help here. I have problem on my Database own by 'unknown". Can I change the onwner of that Database.

Detail of SQL:
Micosoft SQL Server 2000

If anybody can help me, please reply this thread.

Regards

View 1 Replies View Related

How To Change COLLATION NAME For The Database?

Feb 12, 2004

Is there a way (besides "ALTER DATABASE COLLATE ...") to change collation name for the whole database? I tried to use the "ALTER DATABASE" command, but it didn't work. And I wouldn't like to run "ALTER COLUMN" commands for over 100 tables.

View 2 Replies View Related

Attach Database And Change It Name

May 4, 2007

Hi All.
I just start to use SQL2005 and don't know how attach .mdf file's database and change name of database at the same time?
Thanks.

View 5 Replies View Related

Database Level Change

Jul 20, 2005

I have many tables and in those i require to change some data. Sayfrom ARCA to ARCAEX. I am sure that the string is unique in the sensethere will be no ARCAABC. So what do i do change by not manuallyneeding to search in each table and the whole database and still canbe sure that the changes have taken place. Please helpRegards,Rajesh

View 2 Replies View Related







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