|
Solution - a new column was a bit data type : all existing rows require a value of 0 / 1 in that column.
|
|
|
|
|
hi guys
i am using oracel server , and vb.net,
in my database i have a table named abc in this table
i have a date field which contains date values and its type is date
in the format "10/13/1995"
there are rows in this filed for the same day as well also,
means it may also contain another entry like above "10/13/1995"
the issue is that i want to get dates based on months (group by) but based on
a particular year,
means i want it should show me all the group by rows for each year and for each month
like
1995= year , 07= month, 3 (number or rows)
1995= year , 06= month, 6 (number or rows)
1996= year , 07= month, 3 (number or rows)
1996= year , 06= month, 6 (number or rows)
hello
|
|
|
|
|
You should be able to do something like:
select to_char(MyDateField, 'YYYY'), to_char(MyDateField, 'MM'), count(*)
from MyTable
group by to_char(MyDateField, 'YYYY'), to_char(MyDateField, 'MM')
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Hi all,
I am having a database in SQl server 2000.In this database some tables are having around 8 lakhs records, when i am fetching the records from these tables it is taking more time (some times more than 10 seconds). Each table is have indexe.Can any body tell me How to reduce this fetching time ? or should i rearrange the indexes ?
Thanks,
S.Raju.
|
|
|
|
|
|
Without knowing anything about the schema, the data, the query, or what indexes you already have, it's hard to help.
General performance testing: before testing the performance of any query, dump the data in RAM so that you can get a true worst-case (all data coming from disk). You do this by running DBCC DROPCLEANBUFFERS. If you don't, a query may appear to be fast but a large amount of CPU usage occurs - this is the processor having to read a lot of different areas of memory and wait for that data to be read into its caches from slow main memory. It's often also worth making SQL Server recompile your query: run DBCC FREEPROCCACHE.
Then you need to look at what SQL Server is actually doing with your query. In Query Analyzer (you can't do this from Enterprise Manager), select Query, Show Execution Plan, then run your query.
SQL Server has five basic ways of accessing data in a table, grouped into two basic strategies. To understand these, it helps if you understand how SQL Server organises indexes. Basically the index is just a list, in sorted order, of all the values in the index columns, one entry per row in the table, plus a bookmark to find the actual row in the table. In a Clustered Index, the actual data for the remaining columns is present instead of the bookmark (and this is the only place that the data for the table is stored). For a non-clustered index on a table with a clustered index, the bookmark is actually a copy of the key columns in the clustered index. Otherwise it is just a pointer to the record in the table.
The two strategies are Seek and Scan. A Seek means performing a binary search to locate the first record in the index matching a given query, then examining each row in turn until a non-matching value is found, at which point it stops (because the index is sorted, it knows there are no more matches beyond this point). A Scan starts at the first record and simply compares each entry (in the actual row itself, or in the index) to the value desired (if another query is already scanning the same table or index, SQL Server Enterprise Edition can start from the point that the other query has reached to reduce memory thrashing). Seek is generally much, much faster than a Scan operation. In the worst case, a Scan must read every record.
Because of the nature of the Seek, it can only be performed on an index, while a Scan can be performed on a clustered or non-clustered index or on the table itself. So the five operators are Table Scan, Clustered Index Scan, Index Scan, Clustered Index Seek, Index Seek. For non-clustered index seeks or scans you will probably see a Bookmark Lookup too. However, if the non-clustered index contains all the data that you're actually requesting, SQL Server does not need to refer to the base table or clustered index, and the Bookmark Lookup does not occur. In this case the index is referred to as a 'covering index' for the query. You should avoid SELECT * for this reason. Only ask for the columns you need.
The ideal, to me, is to have a covering index for each query, so the server can rapidly locate the data. The cost of building and maintaining such indexes can be prohibitive on a table that is updated regularly.
Note that SQL Server will rarely use multiple indexes on the same table to locate data. The cost of comparing the results from more than one index is high, and normally the query optimizer will conclude that using one then filtering the results will perform better than reading multiple indexes.
Seeks are most effective when the indexes are highly selective - that is, there is a reasonable distribution of a lot of different values. If a column can only have two values, and those values are evenly distributed, there actually isn't a lot of point using an index on that column as the bookmark lookup cost becomes quite high - generally SQL Server then uses a different index that is more selective, or performs a scan instead. If instead most of the rows have value A with only a few having the other value B, the index might be helpful when looking for rows with the B value. This can be problematic as SQL Server compiles queries based on the values given the first time, which may not be suitable if different values are given.
Finally, to use a Seek, the query must form a search argument or sarg - Microsoft refer to it as sargable. See here[^] for a discussion of what is and is not sargable.
|
|
|
|
|
Thanks for the write-up! Nice summary.
David
|
|
|
|
|
Can you provide an example of the SQL that you are using to fetch records?
What programming language are you using for your front-end code?
|
|
|
|
|
How to copy existing database in same folder?
Plz Provide the code in C#.Dot.
|
|
|
|
|
What database?
What same folder?
Atleast put a little effort in. When you just throw a few words int oa post with no detail and apparently very little thought it makes it seem like you can't be bothered, so why should we?
|
|
|
|
|
I m trying to make an application which is connected with more than one database and its depend on user that user may have any name so I create a database name is blank in bin debug folder when user make a new colony database. It copy from the existing database which have already same tables query etc.
plz help me and thakx in advance
|
|
|
|
|
You still haven't said what databse engine your using.
Access, Sql Server, MySQL?
If it's file based like Access you could just take a direct copy. If its server based like Sql Server you could just take a backup.
|
|
|
|
|
i m using Access.
yur thankfull
|
|
|
|
|
If You can give me coding in C# or Vb.net or you can give me logic  
|
|
|
|
|
As Colin Angus Mackay said, check out the FileInfo class, the File class would do it too. Both live in System.IO
|
|
|
|
|
|
It is actually a test for other users on how powerful is thier vocabulary. Remember in the school days English classes, there used to be an exercise where a passage with just hints would be given and We need to rewrite it as a flowing content.
|
|
|
|
|
Hi experts,
I want to change value of an identity column without dropping table / references / constraints / deleting rows from that table.
I am using SQL server 2005 and I have tried to modify the column as identity seed to NO using MS SQL Server Management Studio but it is hanging.
Is there any SQL statement to modify that column as Identity Seed to NO.?
Harini
|
|
|
|
|
Harini N K wrote: I am using SQL server 2005 and I have tried to modify the column as identity seed to NO using MS SQL Server Management Studio but it is hanging.
This normally indicates that there are existing connections open that have taken Schema Stability locks on the table. These locks should be shortlived - check for transactions that haven't been committed or rolled back.
To disable the identity feature completely, use ALTER TABLE table ALTER COLUMN column_name data_type. It sounds, though, as if you want to set the identity value of some existing rows, or set the identity value of new rows you're inserting. To do this, use SET IDENTITY_INSERT table ON. It's normally a good idea to turn this off when you're done. Also, use DBCC CHECKIDENT to ensure that the next row inserted which uses the identity value does not get a clashing value.
|
|
|
|
|
I use INNER JOIN for a multi-part identifer tbl1 a and tb1 b
But I can't find solution for select another table (exam: tbl2)
This is the code:
select a.field1,..
from tbl1 a
inner join
(select field1,.. from tbl1 group by field1,..) b
on a.field1 = b.field1 and ..
group by a.field1,..
I want to select another field(exam:field3) from tbl2, my code
select a.field1,..,tbl2.field3
from tbl2, tbl1 a
inner join
(select field1,.., from tbl1 group by field1,..) b
on a.field1 = b.field1 and ..
group by a.field1,..,tbl2.field3
The Question is: where I put the code:
a.field1 = tbl2.field1
Thanks a lot !!!
|
|
|
|
|
masyarial wrote: select a.field1,..,tbl2.field3
from tbl2, tbl1 a
inner join
(select field1,.., from tbl1 group by field1,..) b
on a.field1 = b.field1 and ..
group by a.field1,..,tbl2.field3
The Question is: where I put the code:
a.field1 = tbl2.field1
<br />
SELECT a.field1,..,tbl2.field3<br />
FROM tbl2 <br />
INNER JOIN tbl1 a ON a.field1 = tbl2.field1<br />
INNER JOIN (select field1,.., from tbl1 group by field1,..) b ON a.field1 = b.field1 and ..<br />
GROUP BY a.field1,..,tbl2.field3<br />
Regards
KP
|
|
|
|
|
Any advice from you guys where I can find easy to read and digest SQL tutorials?
Thanks a lot
Dom
|
|
|
|
|
Your post is VERY general. For basic SQL tutorials just type in a few keywords in this fancy-shmancy new thing called GOOGLE.
_____________________________________________
Flea Market! It's just like...it's just like...A MINI-MALL!
|
|
|
|
|
Wow thats just great! I never thought we have the same brain waves I know I have it in me..
You've just enlighten me
Thanks Lecky
Now I can be a gold member, UTFG you guys
SNORE 
Dom
|
|
|
|
|
firestoper wrote: You've just enlighten me
Before Posting the questions in the forum you should get ti cleared by some of our Friends like google and other tutorials.
After that only we could post the question in the forum.
Regards,
Satips. ![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Walk beside me, and just be my friend. - Albert Camus
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
