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
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
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 RelatedWhat are the criteria to decide the optimal(or close to optimal) fill factor?
Any input is appreciated.
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
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.
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
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
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
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
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
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.
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' Â values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) Â must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName          Clm ABC-X
X
Page2:
GroupName          Clm ABC-Y
Y
Page3:
GroupName          Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
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?
Hi!
I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311679
Provide Individual Page Numbering per Group and Total Pages per Group
Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.
I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.
Thanks!
Karen
I have an SSRS report with groups that when exported to excel contains drill-in's (plus marks on left side). The issue I have is that for all the groups in the drill-in, those cells become merged. I want to keep the group drill-in but have the cells UNMERGED. I have heard this can be done with the RDL XML but I don't know what to modify to accomplish this.Â
View 4 Replies View RelatedI'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
I have a user in SQL Server with a NT login of Mike
I changed his NT account to Mikel in User Manager
Now when I try to add Mikel, Im getting error 15401.
Do I need to delete NT login in SQL Server 'Mike' account first ?..before adding 'Mikel' ?
Can I go into the Master database and just change Mike login to Mikel ?
Thank you
I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:
Year
Month
Date
And the following row groups:
Region
Product
SubType (hidden, data at the date level is summed to Product)
At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)
select top 15 count(*) as cnt, state from table
group by state
order by cnt desc
[code[...
Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.
View 1 Replies View RelatedI have one domoain in the forest. The domain level is set to Windows 2000 native mode and forest level is set to mixed mode. My SQL server 2005 server joined to this domain. I added a brand new domain local group and add a normal user account to this domain local group. I login to the SQL server 2005 server and make a query "SELECT * FROM sys.login_token". I cannot see my domain local group in sys.login_token. However, if I add my account to a global group, I can see it there.
Then, I setup another forest. This time, I have domain level set to Windows 2003 mode and forest level is set to Windows 2003 native mode. I do the same testing. This time, I can see my domain local group in sys.login_token.
Why does SQL server 2005 has this limitation? Is it a bug?
HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned.
DECLARE @StartDate varchar(12)DECLARE @EndDate varchar(12)DECLARE @Region varchar(20)
SET @StartDate = '01/01/2002'SET @EndDate = '12/31/2008'SET @Region = 'Central'
SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName
FROM dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid
WHERE (A.createdon >=@StartDate AND A.createdon <= @EndDate)AND (B.new_RegionName = @Region)AND (A.casetypecode = 2)
I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.
this is the best I can come up with:
Code:
SELECT
DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts
FROM log_directory_contacts
GROUP BY post_date
The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.
What am I doing wrong? Thanks!
Hi folks,
I have a Projects , each project have many tasks now i want to display tasks replated to each project:
for example:
Project1-------------------->task1
task2
task3
task4
Project2----------------------->task4
task5
task6
.............................................projectN.....................
how to write query for this
i have 2 tables:
Project .......>columns are projectid
Task------------->columns are projectid, taskid
|
I have a matrix with two row groups and one column group with about 6 items in it. I have about 2100 rows at the lowewst row group level. This report was built solely for excel export. The first row group has about 20 items and controls the visibility of the other group. When I toggle the visibility of the second row group, how can I make the the header of the first row group copy down for each row of the other row group? The first row group is the Section and the second is Mnemonic.
Example:
Now:
Code Snippet
Column Column
Section1 -
Mnemonic
Mnemonic
Mnemonic
Mnemonic
Section2 -
Mnemonic
Mnemonic
Mnemonic
Mnemonic
Should be:
Code Snippet
Column Column
Section1 -
Section1 Mnemonic
Section1 Mnemonic
Section1 Mnemonic
Section2 -
Section2 Mnemonic
Section2 Mnemonic
Section2 Mnemonic
I have a code like this:
SELECT Node_ID,Day,Operation, AA,BB
FROM
(SELECT
CASE
WHEN Operation LIKE 'NOTIFY' THEN SUM(Total_request) ELSE 0 END AS AA,
CASE WHEN OPERATION LIKE 'SEARCH' THEN SUM(Total_requests) ELSE 0 END AS BB,Node_ID,DAY,Operation
[code]....
So i want to make two columns by the name of operation. in the real code AA and BB are calculates with many counters. My code doesn't work, I have an error: "not a single-group group function" .....
Hi,I was hoping someone may be able to help me with a tricky T-SQLproblem.I need to come up with a SELECT statement that does basically thefollowing:Select RCRD_REFNO, MAX(MODIF_DTTM) as MODIF_DTTM from[StageDb].[dbo].tblPersonInfo group by RCRD_REFNOHowever, I need to select ONLY the TOP 1 of each group (i.e. only 1record for each unique RCRD_REFNO). The problem is of course that if Iadd 'top 1' after select, it only brings back 1 record full stop,rather than 1 for each group!Now, I have previously come up with a similar query that DOES do thissuccessfully, but it relies on a criteria (such as a unique identifier)-unfortunately, the nature of the table I'm using for this currentjob means that it actually doesn't have a primary key, as it'ssimply a staging area for raw data, and can even have completelyidentical records in it. I think the only way I'm going to be able todo it is to literally use the 'TOP' command somehow, but am notsure how to adapt the above to implement it...I'd be very gratefulfor any advice.Many thanks
View 6 Replies View RelatedHere is my Table Structure ( from Oracle database)
Team | Customer Code | Amount | Credit Limit
1 , a, 100, 1000
1 , a , 200, 1000
1 , b, 100, 100
1, b, 1000, 100
1, b, 2000, 100
2, a, 100, 2000
For the Report, I want to group the Team and Sum each customer total Amount and Show the Exceed limit amount.
Here I want to present
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 3000
2 a 100 2000 0
Team Total 100 0
Total 3400 3000
BUT it turn out..
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 2300 ( Problem here a )
2 a 100 2000 0
Team Total 100 0 ( Problem here a )
Total 3400 2400 ( Problem here b)
I Grouped the Custoer Code and Team I can preform the sum
however I can't Do the Exceed total
becoz the value should be
iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0)
but for the team total in team 1 the result is 2300 ( 3300 - customer a 's limit) not add from exceed amount
And the finial total it turns out 2400 (3400 - 1000)
I have tried use the coding to sum up the exceed
but I found that the group total is sumup first than the sum up the detail :
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 0
2 a 100 2000 0
Team Total 100 3000 ( The Total from Team 1 ! )
Total 3400 0 ( Problem here b)
this situration , I can't change the query statement
I can do the good result for CR report
but for reporting service 2005, I can't to the first report result
Any one can help me ??
thank you
need to migrate a cluster with an AG dtabases to new data center cluster with AG.
I was wondering if is possible to do mirroring on top of the AG configuration? or what other options could be to migrate a cluster that has 3 nodes and setup the ag databases to a new datacenter.
partition with single file group or multiple file group which one best.
we have some report running from partition table, few reports don't have any partition Key and after creating 400 partition with 400 file group it is slow.what is best practices to crate 400 file group or single file group.
How do i do a sum(IIF in a group footer. I just want to display one year total.
=SUM(IIF(Fields!Column_Text.Value="2007", Fields!Period_1.Value, 1))
This works in the details section, but gives me an error in the footer section. What should i do? PLease help, this is driving me crazy!
Hi all,
I have a Report and I have used Table in the report to define 5 levels of Sub Groupings: Group1, Group2, Group3, Group4, grpResourceBehavior.
The Sample data which comes is:
Group1 Group2 Group3 Group4 ResourceBehavior BgtHrs BgtAmt
A1 B1 C1 D1 NML 12 12
A1 B1 C1 D1 ODC 0 12
Now, to calculate a field "Cost", I have to use following condition. If the ResourceBehavior is NML, then Cost = BgtAmt/BgtHrs
But, if the ResourceBehavior is ODC, then COst = BgtAmt/(SUm of BgtHrs for the resourcebehavior NML)
I am not able to calculate the Sum of the BgtHrs for different ResourceBehavior. Also, I need this field calculation for each and every group as well, and this makes it all the more complicated.
Any help is appreciated.
Thanks in advance.
Swati