I am in the process of designing a SQL 2005 database with tables that may hold several hundreds of millions of rows.
Due to various constraints, I am trying to save as much space as possible by optimizing the size of a row. Currently one row contains the following columns:
byte(4), byte(3), byte(3) = 10 bytes.
Adding 4 bytes for the row header, plus 3 bytes for the null bitmap (as described in BOL) I am ending up with 17 bytes/row. In a real world test it was an average of 18.3 bytes/row.
There are no indexes, no primary key and all columns are not NULL. Hence my question: since no column allows a null value, is there a possibility to "remove" the 3 bytes Null Bitmap?
Is there any other way to shave off one or two more bytes by using a clustered index etc?
Hi, My DB size (Right click on DB Name, Data Files tab, Space Allocated field) was 10914 MB.
I delete a huge table (1.2 million records * 15 columns). I checked the db size again. It didnt change. Shouldn't it decrease because I delete a huge table ??
I have transnational replication setup on two environments, on one server distribution database is tiny, but on the second server the distribution database is 5 times bigger, and taking up lot of space, both environments have almost same size of data.
Has anyone implemented split data for an application between two databases because the data size is extremely large? If so could you please point me to relevant information.In this split data scenario, a table will automatically carry over to another database whenever the size limit for the current database is reached. The challenge is here for the DAL (data access layer) to automatically look into the appropriate database when the next row of data is in another database. OR Perhaps there is another solution to this terasize data problem..Any help on this would be greatly appreciated.
I have the next question, and i would like to hear what do you thinkabout, and if is there a better solution for "my problem"here is the question, I have a huge table with 60GB of data (imagefiles). The problem happen always when i try to ALTER the structure ofthe table. For example I change a field char(3) to char(4)...thesqlserver then performs the "alter table" command...that must besomething similar than "insert into the new table + drop the actualtable" and for that I need about 60GB o space for my LOG file, andtakes hours to complete the operation.Is this the only way to alter a single field in my table??I would like to heard you opinions...Thanks..ALberto
I need to compare if two developers did the job correctly and created identical tables.
The problem is more complex, but I will try to solve it somehow if I solve the problem of comparing two tables (let them be in different SQL Server 2008 databases) and their properties. No data needs to be compared.
Actually in my transformation i am transferring huge amount of data.
i have been using oledb command finally to dump my incoming data to respective tables.
For Example :
if you have two tables
table 1,table 2
in my incoming data i have a lookup and check for two unique columns with that of the unique columns in the table 1.if the record does not exsist i try inserting a record into table 2 and get the unique filed of the record and store that in particular column of table 1.
the data is very large an is this the better why or any suggesstions do let me know..
Hi All,I've the following table with a PK defined on an IDENTITY column(INSERT_SEQ):CREATE TABLE MYDATA (MID NUMERIC(19,0) NOT NULL,MYVALUE FLOAT NOT NULL,TIMEKEY INTEGER NOT NULL,TIMEKEY_DTTM DATETIME NULL,IID NUMERIC(19,0) NOT NULL,EID NUMERIC(19,0) NOT NULL,INSERT_SEQ NUMERIC(19,0) IDENTITY(1,1) NOT NULL)GOALTER TABLE MYDATAADD CONSTRAINT PK_MYDATAPRIMARY KEY (INSERT_SEQ)GOThe TIMEKEY_DTTM field is generated, from the value actually insertedinto theTIMEKEY field, by the following trigger:CREATE TRIGGER TIMEKEY1ON MYDATAFOR INSERT ASBEGINDECLARE @M_TIMEKEY_DTTM DATETIMESELECT @M_TIMEKEY_DTTM = DATEADD(SECOND, INS.TIMEKEY +EP.GMT_OFFSET * 0 ,'1970-01-01 00:00:00.000')FROM INSERTED INS, LOCATIONINFO EPWHERE INS.EID = EP.EIDUPDATE MYDATASET TIMEKEY_DTTM = @M_TIMEKEY_DTTMFROM INSERTED INS, MYDATA MDWHERE MD.INSERT_SEQ = INS.INSERT_SEQENDGOThere is also a composite, non unique, index defined on thetuple:(MID,IID,TIMEKEY,EID)CREATE INDEX IX_METDATA ON MYDATA (MID,IID,TIMEKEY,EID)GOAs a consequence of an application design change, I would also changethis index to be UNIQUE, but when I try to drop and create it I get anerror, because the tables stores some duplicated rows...In order to succesfully upgrade the index definition, I wrote some DMLstaementsto lookup and remove the duplicated rows, keeping only the firstrecord inserted, i.e. the one with the lowest INSERT_SEQ:---- This table stores then umber of duplicated records eventuallydiscovered-- into the MYDATA table; the initial value for the NUM_DUPLICATESfield is-- 0 (no duplicated record)--DROP TABLE DUPLICATESGOCREATE TABLE DUPLICATES (TABLENAME VARCHAR(17),NUM_DUPLICATES NUMERIC(19,0) )GOINSERT INTO DUPLICATES VALUES ('MYDATA',0)GOINSERT INTO DUPLICATES VALUES ('CATEGORIESDATA',0)GO---- ///////// CLEAN UP OF MYDATA TABLE--DROP TABLE TMP_MYDATAGOCREATE TABLE TMP_MYDATA (MID NUMERIC(19,0) NOT NULL,TIMEKEY INTEGER NOT NULL,IID NUMERIC(19,0) NOT NULL,EID NUMERIC(19,0) NOT NULL,INSERT_SEQ NUMERIC(19,0) )GO---- Insert into the TMP_MYDATA table all the duplicated records for-- the tuple (MID,IID,TIMEKEY,EID) and NULL for the INSERT_SEQ field--INSERT INTO TMP_MYDATA (MID,IID,TIMEKEY,EID)SELECT MID,IID,TIMEKEY,EIDFROM MYDATAGROUP BY MID,IID,TIMEKEY,EIDHAVING COUNT(*)>1GO---- Updates the INSERT_SEQ field to the lowest value in the group-- of duplicated records--UPDATE TMP_MYDATASET TMP_MYDATA.INSERT_SEQ = (SELECT MIN(INSERT_SEQ)FROM MYDATAWHERE TMP_MYDATA.MID = MYDATA.MID ANDTMP_MYDATA.IID = MYDATA.IID ANDTMP_MYDATA.TIMEKEY = MYDATA.TIMEKEY ANDTMP_MYDATA.EID = MYDATA.EID )GO---- Updates the value of NUM_DUPLICATES for the MYDATA table.--UPDATE DUPLICATESSET NUM_DUPLICATES = (SELECT COUNT(*) FROM TMP_MYDATA)WHERE TABLENAME = 'MYDATA'GO---- Delete from the MYDATA table all the duplicated records,-- keeping only the row with the lowest INSERT_SEQ-- The delete is performed only if there are duplicated recors;-- this is achieved using a "short circuit" AND on the number ofrecords-- stored into the NUM_DUPLICATES field of the DUPLICATES table for-- the MYDATA table...--DELETE FROM MYDATAWHERE ( SELECT NUM_DUPLICATES FROM DUPLICATES WHERE TABLENAME ='MYDATA') > 0 ANDEXISTS ( SELECT 1FROM TMP_MYDATAWHERE MYDATA.MID = TMP_MYDATA.MID ANDMYDATA.IID = TMP_MYDATA.IID ANDMYDATA.TIMEKEY = TMP_MYDATA.TIMEKEY ANDMYDATA.EID = TMP_MYDATA.EID ANDMYDATA.INSERT_SEQ > TMP_MYDATA.INSERT_SEQ )GOThis tecnique works fine on a normal table (1M recs) but is not veryperformanton huge tables (>10M records)!Do you know a better way to achieve the task of removing all theduplicates records, preserving the lowest INSERT_SEQ betwee theduplicates and also preserving the sequence seed, so that a new recordinserted at time t1>t0 is enumerated with an INSERT_SEQ|t1 >max(INSERT_SEQ)|t0 ?Thanks a lot for your help!PatrizioPS. sorry for such a large post!
1. 2 Dimensional tables(Parent), DIM1 with 50000 rows, and DIM2 with 1000 rows
2. Fact 1 with 50 columns, 25 Million rows and with FK to DIM1 and DIM2
3. Fact 2 with 40 columns, and 25 Million rows and with FK to DIM1 & DIM2 tables.
Actually the fact 1 and fact 2 have same related data but since our Analysis cube person wanted the fact table not to have more than 50 columns we divided the tables into 2, but they have the same compound key.
Above said, I have a situation where I have to select all the columns, in both fact tables, and do a group by. I wrote the query and ran "Analyze Query in the Database Engine Tuning Advisor" for it. It gave bunch of recomendations about the statistics and indexes which I created. When I executed the query the result came up in matter of seconds, which was good.
In the query I had a condition having MarketName='Bridgeview' and DateID = 344 (FK of today-1).
When I wanted the data for last 30 days I changed to DateID in ( > FK of today -32 and < FK of today), the query responded and worked fine.
But when I changed the query to get MarketName='Aurora' (other than I used when I ran Tuning Advisor), the result returned is empty set. When I removed the MarketName condition, it is supposed to return all markets' data, but it returns only Bridgeview data.
I know the data is in the table for all markets, since reports are rendered from these fact tables for all of these markets(also ran queries to check the fact table data).
I am unable to point out the reason why the query behaves like this. It responds to the date change, but not to the MarketName change.
I really appreciate if anyone can help me point out the problem.
I have many tables, and I just want to print the relationships between them. Â The ones without foreign keys to primary key relations are irrelevant. Â I made a diagram of all tables in sql server management studio, and it shows the key relations, but its a very large diagram horizontally and vertically. Â Is there a way to print the whole thing so that it doesn't take endless pages that I don't know how to piece together?
I have a pivot query returning data ordered by a timestamp. In any give second only a few of the columns will have data, the remaining columns are NULL. I would like to default the NULL data values to the previous NON NULL value by timestamp.
We have a WEB site and my BOSS wants that It should work like me (24x7). Any time when one SQL Server go down , another should take its place. What will be the best way to do this? Can any body guide me in DETAIL. or refer to me at any good material (STEP BY STEP)
Hello all, I am working on an Access 2000 project that is used to generate reports on archived sales data. This app uses linked tables that are pointing to some SQL 2K tables. Some of the queries are large and end up creating a table lock when executed, blocking all other users from using the app until it has finished executing the query. I would like to have queries that read data ignore this lock on the table but do not know of a way to do this using linked tables in Access 2000. I would use a NOLOCk table hint but need something else for this issue. Any ideas? Thanks
I have a DB of transaction log grown to 19GB. I have some questions. 1. Is there any way that we can minimize the entries to transaction log. 2. If i use Transactions in my stored procedure will it be usefull to automatically remove the transaction entries in transaction log once after the commit transaction is given. 3. Is there a way to reduce my current transaction log and not to grown in such a manner futher.
One process ( a service ) inserts data into this table , as well as updates certain fields of the table periodically.
Another process ( SQL Job ) updates the table with certain defaults and rules that are unknown to the service - to deal with some calculations and removal of null values where we can estimate the values etc.
These 2 processes have started to deadlock each other horribly.
The SQL Job calls one stored procedure that has around 10 statements in it. This stored proc runs every minute. Most of them are of the form below - the idea being that once this has corrected the data - the update will not affect these rows again. I guess there are read locks on the selecting part of this query - but usually it updates 0 rows - so I am wondering if there are still locks taken ?
UPDATE s SET equivQty = Qty * ISNULL(p.Factor,4.5) / 4.5 FROM Stock s LEFT OUTER JOIN Pack p on s.Product = p.ProductId AND s.Pack = p.PackId WHERE ISNULL(equivQty,0) <> Qty * ISNULL(p.Factor,4.5) / 4.5
The deadlocks are always between these statements from the stored procedure - and the service updating rows. I can't really see how the deadlocks occur but they do.
Another suggestion has been to try and use an exists before the update as below
IF EXISTS( SELECT based on above criteria ) ( UPDATE as before )
Does this reduce the locking at all ? I don't know how to test the theory - i added this code to some of the statements, and it didn't seem to make much difference ?
Is there a way to make a process ( in my case the stored procedure ) - give up if it can't aquire the locks rather than being deadlocked - which leads to job failures and emails etc ?
We are currently trying to filter down the data that is updated to be only the last few months - to reduce the amount of rows even analyzed - as the deadlocking does seem to be impacted by the number of rows in the tables.
I made a copy of our database and restored it to a new database. We want to strip all the content out except the users and roles. I have been able to delete everything except "system" tables, views and stored procedures.
Can a bitmap be added to a Varbinary field using the INSERT statement. If not what other method can I use to add a photo image (bmp or gif) to my Sql database?
I have not seen a response to this specific question so here goes. I had working code for loading an image into my smart device SQLCE db and for retrieving images. But after upgrading my application to SQLCE 2.0 I am unable to use my retrieve code. This is what was previously working,
Dim ImageBytes As Byte = rs.GetValue(rs.GetOrdinal("OLE"))
Dim ImageStream As New System.IO.MemoryStream(ImageBytes)
frmFlashcardActive.picboxBlobToPicBox.Image = New Bitmap(ImageStream)
Help!
This is what I successfully use to save the image to the database
Dim PictureInfo As New FileInfo("My DocumentsUSAFlag.bmp") 'OpenDialog.FileName
Dim PictureSize(PictureInfo.Length) As Byte
If PictureSize.GetValue(0) <= 69 Then
Dim fsPicture As New FileStream(PictureInfo.FullName, FileMode.Open)
Product No Grade Quantity A Good  A Normal  A Bad  B Good  B Bad  C Good C Normal C Bad
In Table 2, Product No divided by Grade. I want to lookup the Quantity from Table 1 to Table 2. The same Product No will have 1 value, the other value is 0. The result for Column Quantity should be like this:
Table 2:
Product No Grade Quantity A Good 1 A Normal 0 A Bad 0 B Good 2 B Bad 0 C Good 3 C Normal 0 C Bad 0
I am finding that the size of my SQL Server 7 tables are approx 75% LARGER than the same Visual FoxPro table. I am not considering indexes in either one. The only difference is that the SQL Server table has a timestamp that the Visual FoxPro table does not.
Is this normal? or did I do something wacko?
I upsized the data from Visual Fox to SQL by appending the info in the Visual Fox table into a remote view and then doing a TABLEUPDATE to send it to SQL Server.
I'm having to do some estimation on the size of the db that I will eventually create.
I have a 6.7 million row table that holds 5 integers and a varchar(8). Therefore my crude maths are that an integer is 4 bytes and a varchar is max 8 and therefore each line is 28 maxmimum bytes long. Multiply by 6.7 million and you get 1876000000 bytes or 183203KB or 179MB's. With me playing with this table the db has balooned from 23mb to 1,325mb. This doesnt work out. One possible explanation is if SQL Server does the same as Access and you need to compress to gain the space back. I wasn't aware of this though. I have filled and emptied the table several times over to test the import routine.
I therefore have a few questions. 1. Why has the db ballooned to this size? 2. How do I accurately work out future db/table sizes? 3. Is there a way to work out the size of a table? 4. Do SQL db's have to be compacted like Access's?
I know I can check individual table's properties for the size and row count of each, but is there a way of doing it for all tables within a database in SQL server 2005 ? I have about 69 tables in one..
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
I am using the QRCode4CS class [URL] .... to generate QR codes.
I can use the following code to successfully return a bitmap image to a picturebox in a Windows Form Application.
public class CreateQRCodeClass { public static Image CreateQRCodeImage(string inputString) { QRCode4CS.QRCode qrcode = new QRCode4CS.QRCode(new QRCode4CS.Options(inputString)); qrcode.Make(); [Code] ....
In trying to adapt the same code (below) to display a QR code in an SSRS report I get the error "There is an error on line 1 of custom code: [BC30311] Value of type 'System.Drawing.Image' cannot be converted to '1-dimensional array of Byte.'
Here is the custom code I am using.
Public Function QRCode(ByVal RetailerId As String) as Byte() Return QRCode4CSCreateQRCode.CreateQRCodeClass.CreateQRCodeImage(RetailerId) End Function
Here is the revised custom assembly.
public class CreateQRCodeClass { public static byte[] CreateQRCodeImage(string inputString) { QRCode4CS.QRCode qrcode = new QRCode4CS.QRCode(new QRCode4CS.Options(inputString)); qrcode.Make(); Image canvas = new Bitmap(86, 86);
[Code] ....
What data type can I successfully return to SSRS to display the image?
