How To Load Some Tables/views To Memory?
Feb 12, 2008This is a SQL2000 sp4 Ent. machine.
I was heard that loading certain tables/views (the most frequently used ones) could help in performance.
How could i do that?
thanks
david
ADVERTISEMENT
How To Load A Table Into Memory ?
Nov 7, 2006How can I load a table into memory in SQL Server 2000 or SQL Server 2005 ?
How can I keep the table always in memory in SQL Server 2005 ?
with all my thanks
Views Vs Tables
Oct 16, 2007I am an SQL beginner / intermediate.
My question is assuming everything is equal is a view of a 4 table join slower than the SQL query of the 4 table join when running reports?
Views Vs Tables
Jun 29, 2001My client has a three tier system.
and they are using only views to assess the database and doing about 5 joints what are the advantages and disavantages of using only view vs hiting the table dirrectly
Views Vs. Tables
Aug 24, 2000Are views used for securiry reasons, such as restricting access to certain columns/rows or is there performance advantages to using views. There is disagreement in my dept.
I created the following view PAYACTIVE:
Select * FROM PAYMENTS WHERE STATUS = 'ACTIVE'
The PAYACTIVE view contains 260 rows. The PAYMENTS table contains over 2 million rows. I executed the following commands:
Select * FROM PAYACTIVE
Select * FROM PAYMENTS WHERE STATUS = 'ACTIVE'
They seem to take the exact same amount of time. In other words, selecting from the view seemed to not help performance. Am I conducting a valid test?
Views Vs Tables
Jun 29, 2001My client has a three tier system.
and they are using only views to assess the database and doing about 5 joints what are the advantages and disavantages of using only view vs hiting the table dirrectly>
Emily
256 Tables In Views
Aug 30, 2005I have a SQL script that I attempted to write into a view. If I run the script in SQL Query Analyzer it works perfectly. When I attempt to run execute as a view I receive the following error message: "[MS][ODBC SQL Server Driver][SQL Server] The query and the views or function in it exceeded the limit of 256 tables."
My objective is to use the recordset from the view to create a crystal report but have been unable to replicate the "query" effect directly in crystal. (unfortunately I am also a new crystal user)
All my searches have been fruitless. I remember seeing something about SQL Server 2000 supporting 1000 tables and older versions supporting 256 in an Access forum but have not been able to find that thread a second time.
Any provided solution would be greatly appreciated. (I am getting tired of hearing the "gears" in my head grinding. Is smoke from the ears normal?)
SELECT SubID, SubMenuDesc, ItemID, ItemDesc, 0 AS ModID, 'n/a' AS ModDesc, round(ItemPrice/100,2) as ItemPrice
FROM vwSubMenuNorm
WHERE (ItemPrice <> 0)
UNION
SELECT vwSubMenuNorm.SubID, vwSubMenuNorm.SubMenuDesc, vwSubMenuNorm.ItemID, vwSubMenuNorm.ItemDesc, vwModifiersNorm.ItemID AS ModID,
vwModifiersNorm.ItemDesc AS ModDesc, round(vwModifiersNorm.ItemPrice/100,2) as ItemPrice
FROM vwSubMenuNorm INNER JOIN
vwItemNorm ON vwSubMenuNorm.ItemID = vwItemNorm.ID INNER JOIN
vwModifiersNorm ON vwItemNorm.MOD1 = vwModifiersNorm.ModID
WHERE (vwSubMenuNorm.ItemPrice = 0) AND (vwModifiersNorm.ItemPrice <> 0)
UNION
SELECT vwSubMenuNorm.SubID, vwSubMenuNorm.SubMenuDesc, vwSubMenuNorm.ItemID, vwSubMenuNorm.ItemDesc, vwModifiersNorm.ItemID AS ModID,
vwModifiersNorm.ItemDesc AS ModDesc, ROUND((vwSubMenuNorm.ItemPrice + vwModifiersNorm.ItemPrice)/100, 2) AS ItemPrice
FROM vwSubMenuNorm INNER JOIN
vwItemNorm ON vwSubMenuNorm.ItemID = vwItemNorm.ID INNER JOIN
vwModifiersNorm ON vwItemNorm.MOD1 = vwModifiersNorm.ModID
WHERE (vwSubMenuNorm.ItemPrice <> 0) AND (vwModifiersNorm.ItemPrice <> 0)
Permissions On Views And Tables
Aug 12, 2005I am using a Microsoft Access ADP to get to data on SQL Server 2000. I would like to protect the base tables from being edited directly, but allow the views and SPs to handle all the work of getting data in and out. From what I have read in numerous articles and boods, I should be able to grant permissions just on the Views and NOT on the tables. However, the ONLY way I can make my views 'updatable' is by graniting UPDATE permissions on the TABLE! Worse yet, if I DENY permissions to UPDATE, INSERT, and DELETE in the view, but allow them in the table, the view allows the updates anyway, apparently not looking at the fact that it should be DENIED.
:confused:
Create Views Not Tables
Jun 9, 2004I want to allow a group of users to create views but not be able to create new tables or stored procedures... how can I do this ??
Thanks, John :eek:
Tables/Views In DTS Package
Jan 5, 2005Is there an easy way to find all instances of a table/view name in packages?
View 1 Replies View RelatedLinking Tables With Views
May 12, 2008I want to link in a table from another sql server in a view, how is this done?
View 1 Replies View RelatedUpdating Tables Using Views
May 18, 2007Is there any gud topic on "updating tables using Views".Plz let me know
View 3 Replies View RelatedPermissions With Views And Tables
Jul 23, 2005We are attempting to implement security on top of a shrink-wrapped softwarepackage and are trying to get row-level security. Here's the scenario:1. Table dbo.BOOK contains all the information about books in everydepartment.2. There are a large number of developed reports that run queries like"select * from BOOK..."3. We wish to have each Department only be able to see their books - withoutchanging the existing reports.Our thought was to create a series of views:create view Dept1.BOOK asselect * from BOOK where Dept=1....and then create Roles for each Dept. We'd then remove rights to dbo.BOOKand grant rights to DeptN.BOOK as appropriate for each role. We startedtesting this and seemed to get it working, but are now having problems. Isthis possible? Is there another, better solution?Thanks!
View 5 Replies View RelatedNeed Some Help With Design Of Tables/views
Jul 23, 2005I have an applicaton in which I collect data for different parametersfor a set of devices. The data are entered into a single table, eachset of name, value pairs time-stamped and associated with a device.The definition of the table is as follows:CREATE TABLE devicedata(device_idintNOT NULL REFERENCES devices(id),-- id in the devicetabledatetimedatetimePRIMARY KEY CLUSTERED,-- date creatednamenvarchar(256)NOT NULL,-- name of the attributevaluesql_variantNOT NULL-- value)For example, I have 3 devices, and each is monitored for two attributes-- temperature and pressure. Data for these are gathered at say every20 minute and every 15 minute intervals.The table is filled with records over a period of time, and I canperform a variety of SQL queries.I have another requirement which requires me to retrieve the *latest*values of temperature and pressure for each device.Ideally, I'd like to use the data I have collected to get thisinformation, and I suppose I can.What I need is the SELECT statement to do this.I'd appreciate it very much, if someone can help provide that.Conceivably, I could use a SQL server View for making this easier forsome of my users.One alternate technique I thought was to create another table which I*update* with the latest value, each time I *insert* into the abovetable. But it seems like a waste to do so, and introduces needlessreferential integrity issues (minor). Maybe for fast access, that isthe best thing to do.I have requirements to maintain this data for several months/year ortwo, so I am dealing with a large number of samples.Any help would be appreciated.(I apologize if this post appears twice)
View 9 Replies View RelatedAbout Security Between Views And Tables
Nov 13, 2006Hi,
I've got a table T1 and a view V1 based on T1's rows (but not all rows).
I've granted users to do update and select on my View V1.
But i don't want users directly update my table T1.
then, i log as a standard user in Enterprise mgr, and open my view V1. I modify a value in a field, and when i validate, an error occurs saying i'm not allowed to make Select neither Update on table T1.
Is there a way of doing this ? (sql 2000).
Load 4 Different Csv Files Into 4 Different Tables?
Feb 10, 2015I have written bulk insert and doing this manully but need to automate it and file location changes evertime .
BULK INSERT dbo.alpha
FROM 'C:posted 2015-01-16HOSPITALHOSPFY2010hosp_2010_ALPHA.CSV'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '',
ROWS_PER_BATCH = 10000,
TABLOCK
)
BULK INSERT dbo.RPT
FROM 'C:posted 2015-01-16HOSPITALHOSPFY2010hosp_2010_RPT.CSV'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '',
ROWS_PER_BATCH = 10000,
TABLOCK
)
how to proceed?
DTS Load To Multiple Tables
Feb 17, 2006I have SQL Server 2003 Standard and am attempting to use DTS for a data load/transformation and I€™m not sure if I am using the right tool for the job. I have a somewhat denormalized Access database that has to be loaded into a normalized SQL Server database. Values from one row in any of the source tables generally need to be separated and inserted into several destination (SQL Server) tables. There are no unique ids in the source data since it is coming from a 3rd party and the tables are not related to others. I€™ve created a DTS Package and have the beginnings of several Transform Data Tasks. Each destination table has an Identity id column, which is calculated automatically. The roadblock I€™ve run into is that I can€™t figure out how to take each newly created ID and insert it into a new row in another table as a foreign key. Basically, I have to move data from a single input row to new rows in multiple destination tables and create ids (PK, FK) that tie these tables together. The Transform Data Task only allows me to reference one source and one destination, not multiple destinations. I hope this makes sense. Any suggestions would be appreciated.
Here€™s a simple example that may help illustrate the problem. My database is much more complex.
Input table is called parcels and each row has three columns: Address, Owner, and Legal_Description.
Output database has two tables that will receive this data: Parcel table will have the Address, Owner and Parcel_ID (auto calculated). Legal table will have Legal_Description, Legal_Desc_ID (auto calculated), and Parcel_ID.
When the row is inserted into the Parcel table, the newly auto calculated Parcel_ID has to be captured. Next create a row into the Legal table and insert the Parcel_ID so the two rows are related.
How can I do this through DTS? Thanks for any suggestions, code snippets, or references.
How To List Tables,indexes,views Etc...
Jun 16, 2003Hi,
I want to list the table names in a database "mydev_db".What would be the query ?.
I want to run a similar query to find out the indexes,views,stored_procs etc.
Regards,
Copernicus
Comparing Data In Tables/views.
May 13, 2008I am looking for an efficient mechanism to compare data between 2 tables/views.
Rationale:
I use a proprietary tool to data transfer between 2 databases. The tool itself uses Microsoft SSIS (integration service) to transfer data. I want to make sure that the data is transfered properly. Both the source and target database are not live database. The source and target database are in seperate servers.
Comparing Two Tables Or Views Easily
Jan 15, 2003Based on the discussion at:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23038
and BSmith's idea (no relation), I came up with this silly stored proc to compare any two tables or views. Supply the table names and the columns you wish to compare and have fun!
The key to the GROUP BY approach is that it handles NULLS quite well which JOINS have trouble with.
CREATE PROCEDURE CompareTables(@table1 varchar(100),
@table2 Varchar(100), @T1ColumnList varchar(1000),
@T2ColumnList varchar(1000) = '')
AS
-- Table1, Table2 are the tables or views to compare.
-- T1ColumnList is the list of columns to compare, from table1.
-- Just list them comma-separated, like in a GROUP BY clause.
-- If T2ColumnList is not specified, it is assumed to be the same
-- as T1ColumnList. Otherwise, list the columns of Table2 in
-- the same order as the columns in table1 that you wish to compare.
--
-- The result is all records from either table that do NOT match
-- the other table, along with which table the record is from.
declare @SQL varchar(8000);
IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList
set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList +
' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' +
@t2ColumnList + ' FROM ' + @Table2
set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList +
' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList +
' HAVING COUNT(*) = 1'
exec ( @SQL)
- Jeff
Using Updatable Views When Moving Tables From One DB To Another?
Dec 3, 2007Hello,We are researching whether the following scenario would be possible:In an upcoming application release, we have to move some tables (Logtables, look up tables, and a couple of secure tables) from database Ato database B.Rather than wait and do everything all at once, and have no roll-backplan should it fail, we'd like to create database B now, and startmoving those tables one by one over to it.To ensure compatibility with the existing code-base, we'd like todetermine whether we can use updatable views to allow the current codeto continue to run against the existing DB.Essentially, we would do this:Given a table named LogTable In database A, we'd copy all ofLogTable's data to database B. (We'd look at the transaction log tocopy any changes made on rows modified after copying started.)Then, we'd turn off the site for a few moments, and:In database A, we'd rename LogTable to LogTable-Old, and create a viewcalled LogTable which points to DatabaseB.dbo.LogTable.When we turn the site back on, updates and selects to LogTable wouldphysically pull from database B from now on.I have already verified that performing selects and updates against aview that refers to another physical database actually does work inSQL 2K5.My question is are there any pitfalls or things we should be aware ofthat anyone else has experienced trying to do something like this?Does it sound feasible?Thank you,Josh
View 3 Replies View RelatedCan Not Create Views, Tables Etc * URGENT*
Jul 20, 2005Hi,I am not getting option as 'new view' and 'new table' when I rightclick onviews and tables option in VisualStudio.net IDE server explorersqlservers database to create new objects.Looks like some setup issue in my database.Thanks for your help in advance.RgdsCV
View 1 Replies View RelatedIndexed Views Vs Temp Tables
Jul 20, 2005With my understanding of indexed views and according to books I read"indexed views" are supposed to perform much better than "temp tables"(temp table having primary key and indexed view with clustered indexon the same keys).But when I tried in my system I am getting opposite results. WithIndexed Views it takes 3 times more time.Any body has any reasons for that? Or my understanding was wrong?thanksRaghu Avirneni
View 2 Replies View RelatedShould I Create Indexes On Views Or Tables
May 22, 2007I have a pretty large database that has tables that will contain millions of rows of records. I will predominantly be using Views just to select the data. (I will not be performing any updates or inserts). I propose creating indexes on the views. My question is - if I create indexes on my views, do I have to create them on the tables as well? Is it good practice to create indexes on tables by default even if I am not going to be performing select statements directly on my tables but via my indexed views? Any advice is appreciated.
Thanks
Ran
How Load Tables Form One Database To Another
Aug 1, 2001Hi,
My problem is I only can put the database table to another database.I use example to ********. For example. My database name is Career. The user has a database name called Online. The Online has already some tables in the database they created before. I just need load my database career tables to the Online database. My database career has seven tables. These tables has a reltionship for each other. I just need to load these table to the Online database, But don't build relation to the tables that already exist in the Online database.
Besides, I want to know how load the stored procedure from my database to another database.
DTS Load Master File To Different Tables
Feb 24, 2004We need to load a "master" flat file to SQL Server tables. The file is a dump from mainframe. Based on a field called "record_type", each record in the file has different columns. I would use the following as an example (the real file is much more complicated than this, but you get the idea):
For instance, my file has:
20M02221984PAPepsi1000
23F11121987MD1000
01M09182003TXCocacola1100
34F03041970DC900
If "M", the fields are "age", "gender", "birthdate", "state", "salary"
If "F", the fields are "age", "gender", "birthdate", "state", "company", "salary"
We need to load the file (only one file) into two different tables, M_table, and F_table. But I have researched and discovered in DTS the source (TEXT file) can not be queried against to filter on the gender field.
Since each record may have different number of fields, I cannot really load the flat file into a "staging" table.
Does anyone has any idea on how to achieve this? Thanks in advance!!!
Load Text Files In To Tables
Oct 15, 2007Hi all,
I have around 140 text files in a folder where all the file entries are delimited by tabs. Currently i am copying the data and then pasting it in to the tables and it is perfectly working fine. I have already created tables for those 140 files. But now i want to load those files in to the table at a single shot. How can i do it? I am using SQLServer 2005.
Sarvan
Load XML Files Into Tables Via SSIS
Jul 19, 2007Hi
I am new to SSIS and am looking to load XML files (with a DTD definition) into tables via a SSIS package. I have created a XML task and am able to load the XML and output it to file. I have also stripped out the DTD definition and am able through a dataflow using XML source an OLE db Destination load and map the XML to table sin my DB. But have no idea how to get the data in when it has a DTD definition included. I either want to put each file into a row in a table then query it. Or from the SSIS package input the relevant info into a set of staging tables or the real tables.
Any help/pointers would be appreciated.
Pin'ing Tables In Memory
Mar 20, 2007We seem to remember (but may be dreaming) that one can force SQL Server to permanently keep a table in memory (apart from stuffing more memory into the box). However, we can't find any links/hints on that. Any idea?
View 4 Replies View RelatedUnable To Create/edit Views, Tables.. From VS .NET
Mar 21, 2005Hi,
I'm working with few examples from a 70-315 exam prep book.
I'm trying to view the Table design... edit/ create views from VS. NET environment.
But when i right click on the View node under Data Connection tree i get only "Refresh" & "Properties". According to this book.. i should be able to create new views from here.
I'm working with a SQL Server 2000 local installation. VS .NET is connected to the DB, as i tried other examples and i'm able to read from the DB.
My SQL server installation is a 120 days eval installation... has this got anything to do with the behavior ??
I also reied to establish a connection with SQL server 'sa' login... i could connect, but again i wasnt able to create 'views' access tables design.
Any help would be appreciated.
Thanks.
How Can I Use The GetSchema To Get A List Of Only User Tables And Views.
Apr 26, 2006How can I use the GetSchema method new in 2.0 to get a list of only user tables and views.
View 6 Replies View RelatedPrint List Of Queries, Tables, Views And Sp
Oct 29, 2007I just started a new job and 1st time on sql server, how can i print list of queries, tables, views, stored procedures and functions?
View 6 Replies View RelatedAltering Views Will It Reflect The Original Tables.....
Aug 17, 2005phanindra writes "Hello all,
I am phanindra....
I have a question sir/madam,
suppose we have an view named "VIEW" for a table named "TABLE"
will the values in the TABLE alter if we change it in VIEW
thanks in advance
phanindra"