I have recently converted a table with many columns that are now using sparse columns.However I have now found that a lot of my queries are not working.It seems to be due to the fact that I am using a lot of #Temp tables in my queries along with the SELECT... INTO... method to get data into the temporary table.This worked previously fine, however the sparse columns are not coming across to the #Temp table.Here is an example of what previously working fine:-
(This obviously just grabbed everything in the Client table)
Select *
INTO #temptable1
from ClientI have tried the following without any success (Knowing I now need to specify the column names)
Select ClientId, Val1, Val2, Val3, Val4, Val5, Val6
INTO #temptable1
from ClientVal3, Val4, Val6 and Val6 are sparse columns.
The query runs fine, however when I try and do a select of any of the sparse columns from #temptable1, it just says they do not exist.After reading up. It would seem I can not use the SELECT... INTO... however I have not found any other alternatives that I can convert my code to?I have a lot of quite complex queries that use this method and I am looking for something that I can convert them all over to.
I have some huge tables (think 200+GB for a single table) which are excellent candidates for sparse columns. The tables have many columns which are defined with decimal datatypes (13,2) with a large percentage of them (over 50% in most cases- some as much as 99%) being 0.00. Since this is very expensive in terms of storage my idea is to set all the 0.00 values equal to NULL then set them as sparse. Across 100 or so identical databases, I have 5 such tables, with 20-40 columns in each table.
1.) three steps for each column in each table in each db.
Step 1: update table to allow for nulls
Step 2: update tabe set column=null where column =0.00
Step 3 update table set sparse columns
2.)
Step 1: Create entirely new table with sparse column definitions
Step 2: copy entire table, transforming 0.00 to null for affected columns via SSIS
Step 3: drop original table, rename new table to original name
Hi, I€™m trying to create a VERY wide table, with 1,000 columns of type varchar(MAX), nullable. The CREATE TABLE statement (both in SQL 2005 & 2008), gives the following warning:
Warning: The table "WIDE_TABLE" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
When I insert data into the table, filling all columns with small, 10-byte string values, I get the following error:
Msg 50000, Level 16, State 1, Procedure sp_pivot, Line 118
Cannot create a row of size 15034 which is greater than the allowable maximum of 8060.
I€™d like to verify this observation: each row is created with 2000 bytes of offset data (2 byte * 1000 columns), 125 bytes for null bitmap (1000 columns / 8 bits) and some more €œwasted€? row information. This leaves less than 6K for the data itself. But since not all columns can fit within the page, forwarding pointers in the row need to be created, 24 byte per column, which very quickly add up to more than 8K, thus the error. So the 8K limit is met for much less columns than the max 1024 column restriction.
Furthermore, in SQL 2008, SPARSE columns will not solve the problem (maybe save some €œmetadata€? space in case the columns are null, but if not, I€™m with the same problem again, or even worse, since now each value takes more storage space. The max 30,000 columns in 2008 is only for cases where the column values are really sparse€¦
Is this the right observation? if so, is there a workaround besides splitting to multiple tables?
As I understood, if SPARSE is used on a column, which have many NULL marks, then the storage could be efficently used (we need less spaces to save NULL marks, hence a table which has many NULL marks with SPARSE property needs less storage than the same table, but without SPARSE. I created two table as follow:
/******* Table with Sparse ******/CREATE TABLE Sprstb( unsprsid INT IDENTITY(1,1) NOT NULL, Firstname varchar(20) NOT NULL, Lastname varchar(20) NOT NULL, Tel int NOT NULL, adress nvarchar(60) SPARSE NULL)/***** Table without Sparse*******/CREATE TABLE Unsprstb(unsprsid INT IDENTITY(1,1) NOT NULL,Firstname varchar(20) NOT NULL, Lastname varchar(20) NOT NULL, Tel int NOT NULL, address nvarchar(60) NULL)
I have populated the Sprstb with 5 Milion records. It needs 509,961 MB storage. Then I have copied this table into Unsprstb
SET IDENTITY_INSERT [dbo].[Unsprstb] ON Insert [dbo].[Unsprstb](unsprsid,Firstname,Lastname,Tel,adress) SELECT unsprsid,Firstname,Lastname,Tel, adress FROM [dbo].[Sprstb] SET IDENTITY_INSERT [dbo].[Unsprstb] OFF The Unssprstb need only  466,031MB !
That means the Table with SPARSE column need more storage, Why?Â
By the way, in table Sprstb column address has  1666198  Null mark (from 5000000)
I have a table that includes the html-output of different parts of my pages. This table grows very big very fast, and rows older than 24 hours are useless.
My question is if it is possible to have temporary rows, whose are automatically deleted after these 24 hours? And then how to accomplish that?
Greetings!I could really use some suggestions on how to improve on thefollowing, it at all possible:Table 'Customer'---------------------ID GUID PK....Table 'Facility'-----------------ID GUID PKCustomerID GUID FK (FK to Customer GUID)....Table 'Rate'----------------ID PKOwnerID GUID Nullable FK (FK to Customer, Facility GUID PK)OwnerLevel INT Contraint 1-3<Rate Data>Table 'Rate' is a sparse hierarchy of data. There are 3 possiblelevels in the hierarchy as follows:OwnerID <NULL>OwnerLevel 1This indicates Global rate data.OwnerID <Customer.ID>OwnerLevel 2This indicates Customer-specific rate data.OwnerID <Facility.ID>OwnerLevel 3This indicates Facility-specific rate data.Now, a given Customer need not have an entry in the Rate table. If aCustomer does not have an entry, it is supposed to 'inherit' Globalrate data. A given Facility need not have an entry in the Rate table.If a Facility does not have an entry, it is supposed to inheritCustomer-specific rate data, and in the absence of an entry for theFacility's parent Customer, it is supposed to inherit Global ratedata.The challenge is that I want to write a view to give me back theappropriate rate record for Customer and Facility. Here's what I'vedone so far.View _Rate--------------SELECTRate.*,NULL AS TargetIDFROMRateWHERERate.OwnerID IS NULLUNIONSELECTRate.*,Customer.ID AS TargetIDFROMRateCROSS JOINCustomerWHERERate.OwnerID IS NULLOR Rate.OwnerID = Customer.IDUNIONSELECTRate.*,Facility.ID AS TargetIDFROMRateCROSS JOINFacilityWHERERate.OwnerID IS NULLOR Rate.OwnerID IN (Facility.CustomerID, Facility.ID)View view_Rate--------------------SELECT_Rate.*FROM_RateINNER JOIN(SELECTTargetID,MAX(OwnerLevel) AS OwnerLevelFROM_RateGROUP BYTargetID) AS Filtered_RateON_Rate.TargetID = Filtered_Rate.TargetIDAND _Rate.OwnerLevel = Filtered_Rate.OwnerLevelThe combination of these two views gives a resultset that contains 1record for every Target ID as follows:TargetID <NULL>OwnerID <NULL>OwnerLevel 1This indicates Global rate data established at the Global level.TargetID <Customer.ID>OwnerID <NULL>OwnerLevel 1This indicates Customer rate data for the specific Customer identifiedby Customer.ID is inherited from the Global rate data.TargetID <Customer.ID>OwnerID <Customer.ID>OwnerLevel 2This indicates Customer-specific rate data for the specific Customeridentified by Customer.ID (not inherited).TargetID <Facility.ID>OwnerID <NULL>OwnerLevel 3This indicates Facility rate data is inherited from the Global ratedata.TargetID <Facility.ID>OwnerID <Customer.ID>OwnerLevel 2This indicates Facility rate data for the specific Facility identifiedby Facility.ID is inherited from the Facility's parent Customer'sCustomer-specific rate data.TargetID <Facility.ID>OwnerID <Facility.ID>OwnerLevel 3This indicates Facility-specific rate data for the specific Facilityidentified by Facility.ID (not inherited).I know this is a lengthy post, and a complicted query scenario, butI'm not willing to accept that my solution is the best solution justyet. Please consider that I really need this functionality in a VIEWas much as possible.Thank you for your learned consideration.I eagerly await your replies.Darryll
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed. After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename) drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does. I duplicate a table that is going to be modified by using 'select into temptable' I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA' then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02) SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02', FROM TableB where ColumnB = 003860 Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02) Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02', From #temptableabcd Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!
Is it possible to insert data into a table from a temporary table that is inner join? Can anyone share an example of a stored procedure that can do this? Thanks, xyz789
Banti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference. pls reply. first: create table ##temp ( name varchar(25), roll int ) insert into ##temp values('banti',1) select * from ##temp second: create table #temp ( name varchar(25), roll int ) insert into #temp values('banti',1) select * from #temp
both works fine , then what is the difference waiting for ur reply Banti"
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select Test_Name AS 'Test_Name', Table_Bird.Animal AS 'Birds', Table_Mammal.Animal AS 'Mammal', Table_Reptile.Animal AS 'Reptile, Table_Fish.Animal AS 'Fish' From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.
Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:
I am using SQL 2000 I have the following code. When saving it, I am getting an error: There is already an object named '##tbl' in the database
This is although #tbl is dropped. Is threrea way to avoid this error? the only work around I found was to create a string with the SQL command and call EXEC, but I don't like this solution as it prevents early compilation of the procedure.
declare @x int set @x=1 IF @x=0 begin create table #tbl ( abc int ) drop table #tbl end
IF @x=1 begin create table #tbl ( abc int ) drop table #tbl end
I have to create a temporary table for generating a report in VB. Pls help how to check the temporary table name in database. I want if exits than drop and create a new one.
The above database is running on Microsoft SQL Server 2000 and i would like to query for a report that looks something as below:
Description | Period 01 | Period 02 | Year to Date ========================================================= Sales | 20,000.00 | 10,000.00 | 30,000.00 Total Sales | 20,000.00 | 10,000.00 | 30,000.00
The above report would list 4 columns, with the last column being a calculated field as a sum of Period01 + Period02 Amount, sorted by GL_ID and group under a summation row called Total Sales & Total Cost.There would be a net amount appearing as Profit (Total Sales-Total Cost).
Guys, hope someone out there can help me with the sql command for the above report?
Since the second table is somehow only temporary table (having at the front ajax script), i need the third table to store user's ID and all the info from second table.
What would be the easiest way to do it, since the first table contains only one row per user and the second one 40 rows per user.
Declare @Temp Table(id_customer int, id_product varchar(10)) Insert Into @Temp(id_customer, id_product) Values(12, 'ABC104') Insert Into @Temp(id_customer, id_product) Values(12, 'ABC143') Insert Into @Temp(id_customer, id_product) Values(12, 'ABC103') Insert Into @Temp(id_customer, id_product) Values(13, 'ABC102') Insert Into @Temp(id_customer, id_product) Values(14, 'ABC101') Insert Into @Temp(id_customer, id_product) Values(15, 'AABC10') Insert Into @Temp(id_customer, id_product) Values(15, 'AABC11')
select * from @Temp Declare @results VarChar(100) Select @results = IsNull(@results + '; ', '') + IsNull(id_product, '') From @Temp where id_customer = 12 Select @results as all_products
and when i run "Select @results as all_products" what shall i do to get id_customer along? if i do "select id_customer, @results as all_products" I get error in return :(
Hi All, DECLARE @MyTableVar table( EmpID nvarchar(10) ) select login_id from cpm into @MyTableVar Above syntax is not working.... Actually i want to table variable and store the result returned by stored procedure. How to do that... Thanks and reagards A
I'm trying to drop a temporary table. I keep getting this error: Cannot drop the table '#temp_table', because it does not exist in the system catalog. I tried the following but it did not work, so help would be appreciated. if object_id('tempdb..#temp_table') is not nulldrop table #temp_table
Hello once again, My last question concerns (temporary) tables. In a web form (VWD Express, Asp,Net 2.0, VB, .mdf db, Ajax) I have two gridviews. One gets its data from textboxes and dropdownlists as the user clicks an ok button. This data is then sent to a "junk" table in the db. The other gridview retrieves this data "automatically" and displays the data, filtered and grouped by some SQL statements. When the user finally clicks an Insert record button, the data from this second gridview gets inserted in another table in the db and the data from the first "junk" table is disposed. I guess it shouldn't be necessary to insert, retrieve, and delete the data in and from the "junk" table. Even though it does work like a charm, I guess it would be better if I could do it wit hsome kind of "temporary table", array or the like so that the db wouldn't need to be updated to this extent. Still, I really like the simplicity of using gridviews and the rest of the page's code is complex enough, so I don't really want to substitute this behaviour for tons of hand-written, hard-to-follow, and hard-to-integrate code. What are my options? Any help would be greatly appreciated. Pettrer
Im trying to find a table and Drop in T-SQL using this script.
/* Start */ Use Students IF exists (Select * from information_schema.tables where table_name ='##Exams_result) drop table ##Exams_result go Create table ##Exams_result..............etc
/* end */
But I cant find my temporary table on this way... Any sugestion?
Luiz Lucasi lc@culting.com Rio de Janeiro - Brazil
When we are writing a big query that against to 25+ tables (it has lots of lookup and lots of business logic in it so it will have lots of left outer joins, right outer joins, inner join, UNION, group by, and lots of SQL server functions) should we put everything in one select statement? Or we should create lots of temporary tables and then join them together?
My friend told me that I should use one statement instead of lots of temp tables in order to reduce the physical read. He does not like the temp table idea since there will be lots of user running the query in the same time and it will use lots of space. But I don’t believe that is true. (I believe when you put everything in one statement, SQL server still needs to create the result set in the temp db.) There will be lots of join going on and it could hurt the performance.
I believe that 1.We should create the temp tables and then join the temp tables together later to get the final results. we can create the index on the temp table at runtime. 2.If the order of columns in the index does not match to our where clause, then the index is useless. 3.If there is a SQL function in the where clause, the index is useless. (for example, where Datepart(yy, orderdate)=’1998’) 4.If there is a NOT in the where clause, the index is useless.
What do you think? Are you agree with me?
Following is the query. Is there any better way to do this?
Select d.Description as Division, c.Description as Region, b.StateCode as State, b.FacilityNumber, b.Name as FacilityName, (Case When a.ExitDate Is Not Null Then Convert(Varchar(10),a.ExitDate,101) Else 'Pending' End) as PriorStdSurveyExit, (Case When a.ExitDate Is Not Null And a.IJValid > 0 Then Convert(Varchar(10),a.ExitDate+89,101) Else '' End) as ThreeMonthWindow, (Case When a.ExitDate Is Not Null Then Convert(Varchar(10),a.ExitDate+179,101) When a.IJValid > 0 Then Convert(Varchar(10),a.ExitDate+179,101) Else 'Pending' End) as SixMonthWindow, (Case When a.ExitDate Is Not Null Then Convert(Varchar(10),a.ExitDate+450,101) When a.IJValid > 0 Then Convert(Varchar(10),a.ExitDate+450,101) Else 'Pending' End) as FifteenMonthWindow From ( Select a.FacilityNumber, a.ExitDate, IsNull(b.FacilityNumber,0) as IJValid From ( Select b.FacilityNumber, Max(a.ExitDate) as ExitDate From Survey a Inner Join Facility b on (a.FacilityID = b.FacilityID) Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID) Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID) Where a.VerbalResultDate is Not Null And c.Description Like 'Standard%' And d.Description Like 'LTC%' And a.ExitDate <= @InDate Group By b.FacilityNumber ) a Left Join ( Select Distinct b.FacilityNumber From Survey a Inner Join Facility b on (a.FacilityID = b.FacilityID) Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID) Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID) Inner Join ComplianceRatingCode e on (a.ComplianceRatingCodeID = e.ComplianceRatingCodeID) Where a.VerbalResultDate is Not Null And (c.Description Like 'Standard%' And d.Description Like 'LTC%') And e.Description Like 'Category 4%' And a.ExitDate <= @InDate Union Select Distinct b.FacilityNumber From ( Select b.FacilityNumber, Max(a.ExitDate) as ExitDate From SurveyCitation e Inner Join Survey a on (a.SurveyID = e.SurveyID) Inner Join Facility b on (a.FacilityID = b.FacilityID) Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID) Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID) Inner Join Citation f on (e.CitationID = f.CitationID) Inner Join SSLetterCode g on (f.SSLetterCodeID = g.SSLetterCodeID) Where a.VerbalResultDate is Not Null And d.Description Like 'LTC%' And a.ExitDate <= @InDate And g.SSLetterCode in ('J','K','L') Group By b.FacilityNumber ) a Right Join ( Select b.FacilityNumber, Max(a.ExitDate) as ExitDate From SurveyCitation e Inner Join Survey a on (a.SurveyID = e.SurveyID) Inner Join Facility b on (a.FacilityID = b.FacilityID) Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID) Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID) Where a.VerbalResultDate is Not Null And c.Description Like 'Standard%' And d.Description Like 'LTC%' And a.ExitDate <= @InDate Group By b.FacilityNumber
) b on (a.FacilityNumber = b.FacilityNumber) Where a.ExitDate >= b.ExitDate
) b on (a.FacilityNumber = b.FacilityNumber) ) a Right Join Facility b on (a.FacilityNumber = b.FacilityNumber) Left Join Region c on (b.RegionID = c.RegionID) Left Join Division d on (c.DivisionID = d.DivisionID) Order By d.Description, c.Description, b.FacilityNumber
when i run in query analyzer , using "exec SP_TEST" , it work and display result
but when run Exec Master..xp_CmdShell 'bcp "exec mydbtest..SP_TEST " queryout C:TEST.TXT -c -Slocalhost -Usa -Ppassword'
it give error "SQLState = S0002, NativeError = 208" and show Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TMP_CUSTOMER'.
I created the #Temporary table in MS SQL. Now i want to drop this #Temporary table, but i want to check first before i drop the this table. How to check the Temporary table exist or not in MS SQL?
