|
Thanks a lot, I'll give this a try and come back if I've encountered errors. 
|
|
|
|
|
My databases ARE in the millions of records, Luc is correct have 1 structure with all the information. Do not store any data twice (database design 101) eg is you are have industry or sector for an equity then have an equity table with the static data and a related table with the tick information.
If table size becomes an issue there are many better option than splitting by stock name. Partitioning by time is a better solution (partition by year would do). Doesn't express include analysis services (have not checked)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Breaking it down by market is not a good idea, but you do need to know what market something traded on. A single stock may trade on multiple markets in the US, even when you ignore after-hours trading and dark pools.
I've personally worked with global financial databases with billions of records. The DBA sets the appropriate indices and partitions and performance was fine.
What you do not want to do is design something fragmented where it is hard to change the structure later without losing your data.
As far as memory usage, you can use SELECT statements and limit the amount of memory that sql server will use. So if you have a 10 GB table, you can tell sql server not to use more than 500 MB of RAM for instance.
|
|
|
|
|
Hi Ted, thank you for the suggestions. I also feel that fragmenting the data is not a good idea. But if I dump them all into one table, that would create big redundancy in the date column (my 1st column), since I'll need to repeat it for every stock I have in my database. Is this okay?
Thanks!
|
|
|
|
|
Yes, you should use the date in your table (which is 4 bytes). If you created another table holding dates to try to reduce the size, then you would have an additional bottleneck.
Your primary key will be composed of multiple columns including ID, Date, and possibly source/exchange. If you really needed to save space in your table, you could use a smallint (2 bytes) for your date and map the minimum small int to Jan 1, 1970 or wherever you will start your database. Then it will be quick to add/subtract an offset to go back and forth from Excel's date format.
|
|
|
|
|
Thank you Ted, I'll give it a try and see how it goes.
|
|
|
|
|
hello all.
I want to validate category name before insert all information about category into database.
The following condition is the way i want to compare:
Ex: ------------------------------------------------------------------------
| name in TblCategory | name in Text box | comparative result |
------------------------------------------------------------------------
| white bag | whitebag | existing |
------------------------------------------------------------------------
| whitebag | white bag | existing |
------------------------------------------------------------------------
| White Bag | white bag | existing |
------------------------------------------------------------------------
I use this following function in sql database:
BEGIN
IF EXISTS(SELECT TOP 1 *
FROM TblCategory c
WHERE (c.c_name=@name OR c.c_name=REPLACE(@name,' ',''))
AND c.c_id <> @id
)
SET @result=1
ELSE
SET @result =0
END
this give me bad result if TblCategory (white bag) and TextBox to compare has word(whitbag)...this can't compare to me
|
|
|
|
|
I already answered your question in the other forum. Don't cross post. Please follow the post from there.
|
|
|
|
|
Hi All,
I have a SQL database column "ID", in which I added one by one entries as 2,5,6,1,8... randomely.
I want to arrange these entries in Ascending order so that in datagridview it get displayed in order 1,2,3,4... like this.
Can someone help me on this?
|
|
|
|
|
Ever tried the ORDER BY clause?
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Try this
declare @t table(id int)
insert into @t
select 3 union all
select 1 union all
select 5 union all
select 4 union all
select 2
Query:
select * from @t order by id
Output:
id
1
2
3
4
5
Niladri Biswas
|
|
|
|
|
I have already inserted "EmpID" as 1,2,3,4,5,6,7,8,9,10,11,12. But when i see the column data of table, i found that the data is arranged like this:
1
10
11
12
3
4
5
6
7
8
9
Since, the data is already inserted in the above order, i found that in crystal report the same format is coming...but i want the order in exactly ascending order in report finally, if not possible in database.
Can someone help me on this?
|
|
|
|
|
What's a good way to modify a large number of stored procedures? I took over a project that is not using SET NOCOUNT ON, i.e. the following query returns several hundred rows.
SELECT Distinct SO.Name, SC.TEXT
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.colid=1 --multiple colid's are utilized when the procedures text is very long
AND NOT UPPER(SC.TEXT) LIKE '%SET NOCOUNT ON%'
ORDER BY SO.Name
modified on Friday, November 27, 2009 2:17 AM
|
|
|
|
|
hi Ted
SET NOCOUNT ON is used if you dont want to show results. Lets say i have a Query that Updates a Table, at the end it will give me something like
100 Rows Affected
to make sure that the update does not show that message , you use SET NOCOUNT ON
i have never seen this use in a query passed as a parameter, am not sure if this is correct because as i remember correctly you are not supposed to do that. mybe you can explain your challenge clearly and we can suggest another option
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
I wanted to add SET NOCOUNT ON in the body of the stored procedure after AS. I am relatively new to SQL Server (I used Oracle before) and the articles I read on google suggested that you should always SET NOCOUNT ON before executing queries inside a stored procedure.
|
|
|
|
|
then the correct way to do it is the Following
Create Proc myprc
as
set nocount on
--Do what Ever and set it back to off
set nocount off
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
That's not what was asked, he knows how to set nocount on.
He wants to know if there is a way to easily modify all of his stored procedures to include this line.
|
|
|
|
|
I must have misunderstood him , No there is no way even SQL Compare wouldnt do that
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
You could perhaps do this using a cursor with sysobjects and sp_helptext.
You would use the cursor to step through "select name from sysobjects where xtype = 'P'".
You could then get the stored procedure using sp_helptext and modify it and execute accordingly.
|
|
|
|
|
Or you could go to your version control system, check out the scripts for the stored procedures, modify them as required (using a little noddy program to automate the process), test them, check them back in again and then re-run them to drop and recreate the stored procedures in the target database.
Oh, dear. No scripts.
|
|
|
|
|
But that would be too easy. 
|
|
|
|
|
Thanks. I think sp_helptext will do the trick. I also will take a look at SMO, which was suggested by another helpful programmer.
|
|
|
|
|
You can use SMO to generate scripts add the statement you need and execute the script.
|
|
|
|
|
I just found out that there is SQL Server Options -> Query Execution -> Advanced has the option to set NOCOUNT
|
|
|
|
|
Hi
I have tried to define a full-text index, but when I come to the dialog define population schedules in the wizard, I get errors. 
The errors are
This wizard will close because it encountered the following error: (Microsoft SQL Server)
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
EXECUTE permission denied on object 'sp_help_category', database 'msdb', schema 'dbo'.
SELECT permission denied on object 'sysjobs_view', database 'msdb', schema 'dbo'. (.Net SqlClient Data Provider)
Error Number: 229
Severity: 14
State: 5
Procedure: sp_help_category
Line Number: 1
I followed the article in Codeproject http://www.codeproject.com/KB/database/SQLServer2K8FullTextSearh.aspx
Please Help Me
Regards
Fia
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
