When I run the following code :
DECLARE @var1 float,
@var2 int;
SET @var1 = 32.91 ;
SET @var2 = 100 ;
SELECT CAST(@var1 * @var2 AS int);
the result is ... 3290 (whereas it should be 3291 !)
Also works for @var1 set to 33.91 34.91 35.91 36.91 37.91 38.91 39.91 but not for other values > 40.
Is that a SQL Server bug ?
what is the value when running sql script?
select cast(cast(.0000006 as numeric(38,10)) * cast(1.0 as numeric(38,10)) as numeric(38,10))
the result is .0000010000.
why the result is not .0000006000?
SQL 7 SP1 NT4 SP5
I have a TRANSACTION table with 150 million rows.
I have a USER table.
Each user has about 600 records in the TRANSACTION table.
The TRANSACTION cluster index is on USERID + RECID . The second index is on USERID + Fieldx + Fieldy.
The TRANSACTION table gets about 1.4 million inserts in a normal day and about 40,000 updates.
I want to go through the USER table and delete all users who have not visited me in a while.
I want to do this without substantially hindering performance in a production environment. I can perform this over a week period or two if needed.
The best way I thought of doing this was to grab x amount of users in a cursor and loop through deleting their corresponding TRANSACTION records.
Does anyone have any ideas on a better way. What is going to happen to my indices during this time ?
Thanks !!!
Does multiplication with 1 affect query performance?I have a a stored procedure that converts results to another unit if required. In alternative 1 below, the results are returned with a separate select statement if no conversion is necessary - in other words, no multiplication with a conversion factor is required. However, the code is not very nice since I need to repeat the select statement again in case a conversion is required, this time including the conversion factor.Alternative 2 uses cleaner-looking code. The conversion factor is set to 1 if no conversion is required, and a single SELECT statement is used to return the data. The @factor variable is defined as a float.I would rather use alternative 2, but I wonder if there is any performance penalty for doing that if no conversion is required since the results are always multiplied with the @factor? Or can SQL server somehow understand that @factor = 1 and no multiplication is required?--- Alternative 1: ---IF @fromunit_sid = @tounit_sid-- Return unconverted results
SELECT ISNULL(ls_totalWaterConsumption,0) AS ls_totalWaterConsumption,ls_theoreticalWaterConsumption AS ls_theoretical_WaterConsumption,ls_totalWaterConsumption - ls_theoreticalWaterConsumption AS ls_extra_WaterConsumption FROM Results WHERE scenario_id = @scenario_idELSEBEGIN
-- Get conversion factor
EXEC getConversionFactor @fromunit_sid, @tounit_sid, @factor OUTPUT -- Get the converted results
SELECT ISNULL(ls_totalWaterConsumption * @factor,0) AS ls_totalWaterConsumption, ls_theoreticalWaterConsumption * @factor AS ls_theoretical_WaterConsumption, (ls_totalWaterConsumption - ls_theoreticalWaterConsumption) * @factor AS ls_extra_WaterConsumptionFROM Results WHERE scenario_id = @scenario_idEND --- Alternative 2: ---IF @fromunit_sid = @tounit_sidSET @factor = 1ELSE
-- Get conversion factor
EXEC getConversionFactor @fromunit_sid, @tounit_sid, @factor OUTPUT
-- Get the converted results
SELECT ISNULL(ls_totalWaterConsumption * @factor,0) AS ls_totalWaterConsumption, ls_theoreticalWaterConsumption * @factor AS ls_theoretical_WaterConsumption, (ls_totalWaterConsumption - ls_theoreticalWaterConsumption) * @factor AS ls_extra_WaterConsumptionFROM Results WHERE scenario_id = @scenario_id And another question: is using an IF function considerably faster than making a call to another stored procedure?In alternative 2 above I use an IF statement to check if @fromunit_sid = @tounit_sid, and . But in fact the function getConversionFactor that I'm calling does exactly the same thing: if I pass in identical from- and to-values, it simply returns 1, so I could omit the IF statement completely and just use alternative 3. But is it slower?--- Alternative 3 -- Get conversion factor
EXEC getConversionFactor @fromunit_sid, @tounit_sid, @factor OUTPUT
-- Get the converted results
...
Hi all...I have a friend who has a problem I'm trying to help with (no...REALLY...it's a FRIEND, not ME!!! *LOL*)
I haven't run across the need...but in a nutshell...we have a table with many rows of data, one column in each row needs to be multiplied by all other same-columns in the table's other rows.
For example...
MyKey int, MyFloat float(53)
I want to multiply Myfloat by all other Myfloat columns in the table.
Similar to SUM(MyFloat) but something like PRODUCT(MyFloat).
Is there a aggregate kept in a basement closet somewhere, or a way to perform this operation rather than using a cursor to do it:
An example of my table:
1 3.2
2 4.1
3 7.1
if I could do a PRODUCT(MyFloat) I would want the result to be (3.2 X 4.1 X 7.1) or 93.152
Do I have to do this with a cursor?
Thanks!
Paul
I am currently building a website to deal with different product information and sales with php. I am using SQL to sort the database and pull out information.
The final thing i need to do is work out the total revenue of each product however the problem i am having is that the 'Price' column and 'SalesVolume' column are in two different tables and they need to be multiplied together.
The two tables and column headings are as follows:
Product
ID
Name
Price
MonthlySales
ID
ProductCode
Month
Year
SalesVolume
(ID and ProductCode are linked together in a relationship)
I cannot see anything wrong with the syntax in my query however i believe there is.
Here is the query I am using:
Code:
"SELECT SUM(Products.Price * SUM(MonthlySales.SalesVolume)) as revenue FROM Products
INNER JOIN MonthlySales ON(Products.ProductCode = MonthlySales.id) GROUP BY Products.ProductCode";
I'm trying to create a case statement that if a field = a certain code, I'd like to take another field * 0.9. But, I'm getting a Null value for the answer..here is the statement:,case when parts.ndc = '50242-0138-01' then labels.BAGSDISP*0.9 end "Units Dispensed"..For this example labels.BAGSDISP is a value of 2. So, in theory it should be 2 * 0.9 and the result should be 1.8 but I'm getting a NULL
View 9 Replies View RelatedSuppose I have users that can belong to organizations. Organizationsare arranged in a tree. Each organization has only one parentorganization but a user maybe a member of multiple organizations.The problem that I'm facing that both organizations and individualusers may have relationships with other entities which aresemantically the same. For instance, an individual user can purchasethings and so can an organization. An individual user can havebusiness partners and so can an organization. So it seems that I wouldneed to have a duplicate set of link tables that link a user to apurchase and then a parallel link table linking an organization to apurchase. If I have N entities with which both users and organizationsmay have relationships then I need 2*N link tables. There is nothingwrong with that per se but just not elegant to have two differenttables for a relationship which is the same in nature, e.g.purchaser->purchaseditem.One other approach I was thinking of is to create an intermediateentity (say it's called "holder") that will be used to hold referencesto all the relationships that both an organization and an individualmay have. There will be 2 link tables linking organizations to"holder" and users to "holder". Holder will in turn reference thepurchases, partners and so on. In this case the number of link tableswill be N+2 as opposed to 2*N but it will have a performance cost ofan extra join.Is there a better way of modelling this notion of 2 different entitiesthat can possess similar relationships with N other entities?
View 28 Replies View RelatedI have a huge log file (285M) on SQL Server 7.
The database itself is about 10M.
How can I reduce the log file ?
Is it possible to build it again from scratch ?
I tried the Truncate Transaction Log but it didn't help.
Hi,
I am using VS2003 and SQL Server 2005. I will get the huge size xml from the web service. I need to load this xml to sql server 2005 table as records.
I got below solution from microsoft site. Load XML to Dataset and from Dataset to SQL Server 2005.
Note If you call ReadXml to load a very large file, you may encounter slow performance. To ensure best performance for ReadXml, on a large file, call the DataTable.BeginLoadData method for each table in the DataSet, then call ReadXml. Finally, call DataTable.EndLoadData for each table in the DataSet.
Any other better solution is there to load huge XML to SQL server 2005?
I am writing a .NET service, and I need to insert files in a SQL DB for temporary storage.
I have never inserted a file into a SQL database before. I am thinking about using the image field type. Has anyone done this before? How did you do it?
I would like to migrate around 15 databases in production server to the new production server. The biggest database is 80 GB .
Wondering is there any fastest way to do that with very low downtime ?
What I can think about is shrink databases files and perform attach – detach .
Hey Guys
I have meet the same problem, too.
I create a table with 1,380,000 rows data,
the db real size about 114 MB.
The primary key size is nchar(6).
When I use RDA pull, I found that the primary
key in the PDA disappear. So, It took a long time
to get query response.
But when I delete some rows to 680,000 rows of data.
After I pull, The primary key can pull from the SQL Server.
PS: I didn't change any code. Just delete some rows.
Is that SQL-Mobile's bug??
PS: 1.Database and Temp Database limitation both are 384MB
2.If I use query analyzer to add primary key it works! so strange!!
3.Pull process return "S_OK".
4.After Pull process finished, the db connection still alive. It seems not like
time out problem.
5.Local Connection String:"Data Source='%s\%s';SSCEatabase Password='%s';SSCE:Encrypt Database='true';SSCE:Max Database Size=384;SSCE:Temp File Max size=384;SSCE:Temp File Directory=%s"
Hi,
I created a CLR UDF that returns a large number of rows, when I run it from my VPC (XP, SQL Server Developer Edition and 1GB Memory) it takes approx 2 min and 30 secs to start displaying the rows (Using Management Studio), when I run the same query in our development server (Win 2003, SQL Server Enterprise Edition, 8 GB Memory and 8 Processors) it takes more than 15 min to start displaying the results, does anybody have an idea why is this happening?
Thanks in advance
I have a CTE query against a table with 32K rows that runs fine in 2008R2. I am running it in 2014 Std Ed. against the same data and it runs very slowly. Looking at the execution plan I think I see what's contributing to the slowness.
Note that the "actual number of rows" is some 351M...how is this possible?
the query:
declare @amts table (claim int,allowed decimal(12,2),copay decimal(12,2),deductible decimal(12,2),coins decimal(12,2));
;with unpaid (claimID) as (select claimID from claim where amt+copay + disct+mm + ded=0)
insert @amts
select lineID, sum(rc), sum(copay), sum(deduct),
case when sum(mm)>0 and (sum(mm)<sum(mmamt)) then sum(mm) else 0 end
from claimln
where status is null
and lineID not in (select claimID from unpaid)
group by lineID
it's like there's some massively recursive process going on?
In
one of our forth coming projects, with ASP.Net/C#/MSSQL Server, We have
to deal with a Business table having about 15 millions of records. We
want to know, that which methodologies should we adopt, both regarding
front end and back end perspective, so the site could give optimised
performance. Also in place of a Dedicated Server, the Hosting Company
provides MSDE (that come with .net). Will this create any problem with
this project, that have such a huge table? Should we go for some
advanced database technique, such as, Clustering, Spliting Tables, etc.
Followings are the fields that the business table contains:
ID, Category ID (which comes from a Category table, each business is
under a category), BusinessName, SignupDate, Address1, Address2, Phone
Number,
Hours Of Operation, Years in Business, LicenseNumber, DiscountCoupon, Website
I am copying data from database to an excel file through SSIS. database is MS SQL 2005 and BIDS is also 2005.However, the job doing this task fails every time.As per investigation, the result of the query is more than 100,000 rows and we know that excel has a limit of 65000 rows of data.Is there a way of setting the limit up? or something? a better approach maybe so that everything will be copied to the excel file successfully.
The PrimeOutput method on component "Source - Query" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. End Error Error: 2015-10-22 04:34:58.05 Code: 0xC0047021
Source: Data Flow Task
Description: SSIS Error Code DTS_E_THREADFAILED.
Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:30:00 AM Finished: 4:35:05 AM Elapsed: 304.844 seconds. The package execution failed. The step failed. "
We have a Customized share point application with Very minimal data usage and we have used only 5 to 6 lists and libraries only in the share point.
Configuration is
Clients -- fire wall --- Load Balancer ---- WF1 and WF2 --- SQL DB
ROUTING IS VIA FIRE WALL.
SUDDENLY THE SITE GOT DEAD SLOW AND UNABLE TO TRACE THE PROBLEM AS EVERY THING LOOKS FINE.
Checked with the firewall Team and they stated its fine from their end & even we have verified the counters, CPU, Memory & Page life expectancy, buffer counters all looks good and even we do not have huge data in the database. We have only 50 concurrent users are working...
I need to periodically import a (HUGE) table of data from an external data source (not SQL Server) into SQL Server, with the following scenarios:
Some of the records in the external data source may not exist in SQL.Some of the records in the external data source may have a different value at different imports, but this records are identified univocally by the same primary key in the external datasource and in SQL Server.Some of the records in the external data source may be the same in SQL.
Due to the massive volume of the import, I would like to import only the records which are different from what I have in SQL Server (cases 1 and 2 above). In fact case 2 is the most critical.
I thought of making a query with a left outer join between the data in the external data source table (SOURCE) and the data in the SQL Server table (DESTIN). The join is done on the respective primary keys (composed keys of up to 10 columns) and one of the WHERE conditions will be that the value in SOURCE is different from the value in DESTIN.
The result of this query would be exactly what I need to import.
How to do this in SSIS??? I couldn't figure out how to join tables in different data sources yet.
In fact I cannot write a stored procedure to do that, since one of the sources is in a datasources not SQL Server.
I have seen the Lookup transformation in this article http://www.sqlis.com/default.aspx?311 but this is not exacltly what I want to do.
Another possibility is to use the merge join, but due to the sorting I believe its performances would be terrible!
Thanks in advance for your suggestions!
Dear all,
i have problems with log .
the mssql write 4 G log so plz how can i Eliminate log huge size
I have SQL Server 2005 Express Edition with Advanced Services running on a small web server. It all runs fine, but every now and then the log files grow and grow and eventually use up all the disk space of 30GB. As a quick fix, restarting SQL a couple of times clears out the logs and everything is up and running again. Any ideas on how to stop this happening?
View 5 Replies View RelatedI just peeked at my DNN setup and I found that I have a transaction log about 98 gigs large, compared to a DNN database that is only about 250 megs. Crazy, huh?
Do you happen to know what I need that transaction log for? Can I just delete it or will it break my SQL db? Is there a way that I can keep only maybe a week of transactions in it so it doesn't grow so dang large?
Thanks in advance for your response!!
Tim x 4
(always learning!)
One of my production databases is currently 51 mb. The transaction log is well over 5 gig. I have tried truncating and then shrinking the log through the use of SQL utilities. This does not work! How can I quickly resolve this problem without tampering with the production environment?
Thanks in advance.
Ray Reinders
I am not a DBA and I run a personal web site that has gotten pretty large. I have never done anything to maintain my sql server, and now my transaction log is 10 Gigs and my data is only like 300 Megs. I am starting to get a memory leak with the sql service. What should I do? Is it bad to have a huge transaction log. I am not familiar with any of this stuff, so someone please point me in the right direction.
View 3 Replies View RelatedHi all,
I found my database log file is 26GB and the database file is just about 280MB. We are doing full backup everyday. However, my sql server seems running very slow now and please advise:
1. How can I decrease/truncate my log file?
2. Would the huge size of the log file be the reasons slowing up my sql server?
3. Would anyone give me direction knowing more on the transaction log?
Thank you and appreciated!
Hi guys, its my first post! Its also like my first time really diving into sql. We are using sharepoint on site here along with sql server 2005, one of our log files is 255 GBs and needs to be made smaller very fast!! We are almost out of disk space and the log is growing fast.
I am very new to sql and dont even know where to go to enter commands, so youll have to bear with me here. I've read about truncating and shrinking and some other things, I am just worried and dont want to mess anything up. I know this is probably a simple task, but like I said, with the truncate command I was reading about, I dont even know where to go to type it in!!! If someone could please help it would be much appreciated. Thanks so much.
Hello, newbie here...
I have a huge dataset in MS SQL Server, over 11 million records of machine data taken every four seconds. I really don't need samples of this data at this interval. I'd like to run a query to retrieve my data for every 30 minute interval. I know enough SQL and DTS to SELECT was fields I want, and how to direct it to a CSV file, but I'm not sure how to manipulate the TimeStamp field in the query to only pull data every 450 records.
TIA
Hey everyone.
I have a problem and I am sure someone here can help. Lat night my DB was working fine, as it has been . This morning I get to the office and now everything has gone to hell in a handbag .
I can no longer connect to my sql2005 DB I get this error when trying to place an order on our order page.
"There was a problem with the website:An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)
The error has been logged."
I also notice the sql agent will not start. Any the former owner of the Co. had a eval version of sql management studio the must have just expired(could this be causing it?)
Any help anyone can offer would be great.
I have a .bak file of 72gb. But my database size is only 32gb, I got this value from sp_spaceused?
Anyone know why the .bak file is so big?. Is it possible to reduce the size? How could i reduce it?
http://www.sqlserverstudy.com
Hello,
I have a very big T-SQL script (~24mb) and I need the SQL Server on my hosted site to execute it.
Right now, I have a web page that uses Sql.Connection.ExecuteNonQuery () to do it, but the .NET process runs out of memory when I load the file into a C# string.
How would I go about executing this T-SQL script on the server?
Is there such a command:
EXECUTE SCRIPT "myscript.sql" FROM DISC
?
thanks.
Hi
this is regarding SQL Sever 2000. ( it was upgraded form sql7). its log file is increasing in very high manner. say 40 gb, 50 gb and now 57 gb. Mdf file is around 15 mb. we created back up and tried to restore to another system. its asking 57 gb free space. how to proceeed with file recovery. we have backups but it askes more space for log file. how to retrieve the data.
rgds
Pramod
Good evening:
We're porting an old app written in ASP.NET 1.1433 and SQL 2000 to ASP.NET 2.0 and SQL 2005. In the old app we have a few data grids that are populated from a dataset pulled from the database. We use a SQL query that we build based on more than 10 different user inputs, the result of which is an enormously complicated SQL string. We'd like to move this processing into a SPROC in the 2005 database.
Rather than writing stored procedures to create the SQL SELECT statement, is it possible to pass an entire select state ment to a SPROC and have it executed within? We're trying to capitalize on paging in 2005 using ...ROW_NUMBER() OVER (ORDER BY PM ASC)... and building the string using IF ELSE statements is mind numbling complex and tedious.
And suggestions woudl be great.
Thanks,
Brad
One of my databases is approxiamtely 1.5 GB's but when I run a backup the backup file explodes to 38GB's.
What is the proper usage of Shrink Database, is this safe or is there another method to reduce the size?