TechNetTop Questions - September 10, 2000
On This Page
SQL Server: IDENTITY_INSERT
"Little Problem with Novell Banner Pages"
SQL: DTS Pump from Oracle to SQL Server
"Bitmap Index On SQL Server"
Next, a Good 'Ol NT Group Question
SQL Server: IDENTITY_INSERT
Q: Hello!
I have created a few tables with identity columns. Since I want to insert few complete lines (including the value of identity column) I set identity_insert to on. But on some tables MS SQL Server replies that identity_insert for that table is already on. Even when I explicitly set it to off and then in the next line to on again it complains about it.
There are no constraints or relations defined on tables yet. Any idea what is going on?
This script fails and I can't see where is the problem.
CREATE TABLE [SPLOSNA_STRAN] (
[ID_SPLOSNA_STRAN] [int] IDENTITY (1, 1) NOT NULL ,
[REF_TIP_SPLOSNE_STRANI] [int] NOT NULL ,
[IME_STRANI] [nvarchar] (50) NULL ,
[KODA_STRANI] [nvarchar] (255) NULL ,
[ANNONIMOUS] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [FUNKCIJE_POS_OBJ] (
[ID_FUNKCIJE_POS_OBJ] [int] IDENTITY (1, 1) NOT NULL ,
[POS_OBJ] [nvarchar] (50) NULL ,
[DLL_KOMPONENTA] [nvarchar] (50) NULL ,
[DLL_OBJEKT] [nvarchar] (50) NULL ,
[DLL_FUNKCIJA] [nvarchar] (50) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT SPLOSNA_STRAN ON
GO
INSERT INTO SPLOSNA_STRAN
(ID_SPLOSNA_STRAN,REF_TIP_SPLOSNE_STRANI,IME_STRANI,KODA_STRANI,ANNONIMOUS)
VALUES(1,8,'Spisek uporabnikovih aplikacij','POSSEZNAMUPOAPP',0)
GO
SET IDENTITY_INSERT FUNKCIJE_POS_OBJ ON
GO
INSERT INTO FUNKCIJE_POS_OBJ
(ID_FUNKCIJE_POS_OBJ,POS_OBJ,DLL_KOMPONENTA,DLL_OBJEKT,DLL_FUNKCIJA)
VALUES(1,'LOGIN','SSObjects','LoginObj','DrawMe')
GO
Thanks,
Miha Strohsack
Faculty of Computer and Information Science
University of Ljubljana, Slovenia
A: Miha,
It seems like you only can have IDENTITY_INSERT turned on for one table at a time. So, you have to turn it off before turning it on for some other table:
SET IDENTITY_INSERT SPLOSNA_STRAN ON
GO
INSERT INTO SPLOSNA_STRAN
(ID_SPLOSNA_STRAN,REF_TIP_SPLOSNE_STRANI,IME_STRANI,KODA_STRANI,ANNONIMOUS)
VALUES(1,8,'Spisek uporabnikovih aplikacij','POSSEZNAMUPOAPP',0)
GO
SET IDENTITY_INSERT SPLOSNA_STRAN OFF --NOTE!!!
GO
SET IDENTITY_INSERT FUNKCIJE_POS_OBJ ON
Regards,
Tibor Karaszi, SQL Server MVP
Tibor, you hit the nail squarely on the head. One more time: When asking questions or presenting a problem that you're experiencing, including the exact version/service pack of the product that we're dealing with will almost always help in getting to the correct answer – in a much more timely manner.
Here is more detail on this SET IDENTITY_INSERT ON issue:
If one would consult SQL Server 7.0 Books OnLine using the query "set identity_insert", the results clearly state:
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.
Stepping back quite a bit.... to SQL Server version 6.0, it is good to know that there was a problem when attempting to perform a SELECT INTO from a table that has been ALTERed to add an IDENTITY column. If you did happen to try to do this, the SELECT INTO would fail with the following error message:
Msg 511, Level 16, State 2
This is a problem in SQL Server version 6.0 and has been corrected in U.S. Service Pack 3 for SQL Server version 6.0.
More details can be found in Knowledge Base article # 136531: "SELECT INTO From a ALTERed TABLE Cannot be Performed"
"Little Problem with Novell Banner Pages"
Q: Hi,
We've got Windows NT Workstation and using Novell 4.11 as a file server. Whenever I print a document it prints a banner page with my name on it, etc. How do I turn this off? I went to CSNW in the control panel and made sure that "print banner" was not checked. Where else might it be telling the printer to print a banner page?
Thanks,
Chris
A: Here is the first stab at trying to help Chris:
How are you connecting to the printer? If it's a Windows NT workstation capture and you have a newish Novell client, try right clicking Network Neighborhood, select Novell Capture Printer Port, choose the port which is captured and click on the settings button. There is a tick box here, which will enable or disable banners.
James
Next, Todd pipes up with...
Check under the properties for the printer (either on the workstation, or in the NDS printer/cue definition.) If you are using Bindery instead of NDS, check the printer/cue properties there.
Todd,
MCSE, ASE
Thanks go out to James and Todd.
All good stabs, considering the absent Windows NT configuration information.... like what NT version? What Service Pack is applied? Did this problem just start happening, or has it been an ongoing, annoying problem forever and just now it is really getting under your skin – so much that you need to fix it before you go bananas? (Deep breath... One... two... three...)
This is an example why it is critical to provide as much NT configuration information as possible. Not trying to be rude, but just stating a fact that by not providing complete NT version / SP info quite frequently delays getting to the nub of the problem. OK I feel better now.
Odd problems like this one – where something worked like it was supposed to and then for no apparent reason (other than a Service Pack or some other software was installed) it breaks, oftentimes can be traced back to that software. In this case it could very well be related to the Service Pack that broke something that worked just fine before.
The following is provided under the assumption (guess?) that this "NT Workstation" is Windows NT version 4 and has Service Pack 5 installed.
232559 - Novell Print Banner Always Printed After Applying Service Pack 5
SQL: DTS Pump from Oracle to SQL Server
Q: Hello,
I'm using DTS to pump data from Oracle database to form up a data mart star schema. My question is how can I handle DTS package while Oracle getting more transactional records and update? Do I need to pump all data from transactional to data mart every time? Please advice.
Regards
Apichart
A: And here's what SQL Server MVP Wayne has to say about this:
No you do NOT have to import all of your fact records every time. If you can identify WHICH records in the OLTP system are new, just add them into the datawarehouse. You may add a field to the data warehouse fact table. It should be a flag which identifies NEW records. You may then use the DTS OLAP ADDIN to do an incremental update of your cube, and add a WHERE clause to show the records that should be added to the cube "Where newdataflag = 1". The last step in your DTS package would be to set the flag in the DW back to 0.
Wayne Snyder
Wayne Snyder MCSE, MCDBA, MCT, SQL SERVER MVP
"Bitmap Index On SQL Server"
Q: Hi There,
I was reading about how bitmap indices reduce the size and increase the performance of data warehousing apps (on low cardinality cols), but I was surprised to find not even a single mention about Bitmap Index on SQL Server website. I guess Oracle and DB2 have them and had them for a long time. Does this mean that SQL Server (even SQL Server 2000) lacks the most important feature for implementing successful data warehousing apps.
I am trying to implement a data warehousing app which has columns that have low cardinality (Ex "AGE" A(10-20),B(20-30),C(30-40),D etc) with about 100 mil Rows. Should I dump SQL Server in favor of Oracle or DB2 to achieve better performance? Or is there any other way for achieving comparable performance in SQL Server.
Thanks In Advance,
RK.
A: First reply, from Kalen:
After just a bit of reading, are you really educated enough to say that support for bitmap indexing is "THE most important feature for implementing successful data warehousing apps"? Can you explain how you came to this conclusion and why you believe no other solutions can give superior performance? Have you looked at any benchmark numbers to draw your conclusions about who has the better performance?
Deciding when and where to create bitmap indexes is quite a chore for a DBA and requires extensive knowledge of the database engine's optimization techniques. As your data changes, the columns which might once have been likely candidates for bitmap indexes may not be so ideal any longer, and your apps may suffer until you can change your design.
SQL Server's optimizer takes care of this functionality internally and dynamically. Bitmap filters are created automatically when processing the results of hash joins or parallel query results to drastically reduce the data volumes. Index intersection, an optimizer capability added to the product in version 7.0, can reduce data volumes even further, without you having to do ANYTHING, and the optimizer is dynamic so that as your data and your queries change, the best plans will continue to be selected. The SQL Server optimizer also has several techniques for processing the star joins commonly used in data warehousing that it will choose automatically as your data and your queries demand them.
If you have particular queries that you are having trouble with, feel free to post them and the group here will be able to offer specific suggestions.
Bitmap indexes are just one way to get good performance. An exceptional optimizer is another way.
HTH
Kalen D.
R.K. responds thusly:
I am sorry the answer is No. I am a VC++ programmer and not a SQL DBA.
Can you explain how you came to this conclusion and why you believe no other solutions can give superior performance? Have you looked at any benchmark numbers to draw your conclusions about who has the better performance?
Yes, on IBM and Oracle's web site they have benchmarks on how bitmap indices improve speed and performance on low cardinality rows. There are quite a few whitepapers that explain how bitmap indices improve performance. IBM has patented a type of bitmap index called Encoded Vector Index , this is available in DB2 and is supposed to be much superior to b-Tree.
I understand that it is quite a chore but when you have few hundred gigs of data in a warehouse you better be able to squeeze the last bit of performance from the expensive hardware.
The table I am using has a unique row ID and about 600 Attributes, I want to be able to run adhoc Queries on this table to get the counts. There are about 400 Mil Rows and the table size is approx 700 Gigs. I have a 8 Proc Dell on a FC - SAN and the database is striped across all the LUNS using filegroups and index and data are in different filegroups.
Ex :
---------------------------------------------------------------
RowID Age A1 A2 A3 A4 A5 A6 A7 ... A600
---------------------------------------------------------------
1 A A A B 01 C D Q A
2 D C C A 12 F W W B
3 C A E B 33 C D A C
---------------------------------------------------------------
I tried creating indices on the various attribute collections and the size of some of the indices are in the rage of 10 ~ 15 gigs. So even when i issue queries to just hit the index (Indexed fields on select and where) It takes a very long time.
It would be great, if you could suggest any optimization techniques.
Thanks For Responding,
RK
Next, Paul jumps in and asks a very pertinent question...
Are you using straight SQL Server or OLAP Services for this data?
Regards
Paul Hatcher, MCSD
Graduate Associates Ltd
Finally we get to the bottom of all this...
Regular SQL Server. Should I be using the OLAP Service?
Thanks
RK.
I would suggest so, the point about OLAP Services is that it's optimized for a data warehouse scenario whereas normal SQL Server is a more focused towards OLTP.
You're going to need a lot of disks though :-)
Regards,
Paul Hatcher, MCSD
Ahhhh... now it becomes clear! Using the OLAP Service is the best "optimization" technique in this situation that could be made. Here's a bit more information on bitmap indexes and SQL Server 7.0 & 2000:
First of all, neither SQL Server 7.0 nor SQL Server 2000 support storing indexes as bitmaps as some products do, but it uses bitmaps extensively to reduce data volumes to be shipped around in parallel query plans as well as to be spooled to temporary files in hash joins. This is pretty much the same usage of bitmaps that IBM calls "dynamic bitmap indexes."
However, both SQL Server 7.0 and SQL Server 2000 support index-intersection joins, which should provide most of the benefits of bitmap indexes.
So for example, if you have a large table with a Gender column (2 values) and a StateId column (50 values), you can create individual indexes on each of these columns. If then you have a query that says WHERE Gender = 'F' and StateID = 'WA' , SQLServer is capable of using both indexes and intersecting the matching rows from each (using a join) to find the qualifying rows.
Also, both SQL Server 7.0 and SQL Server 2000 support efficient strategies for processing Star-queries, which is another area where bitmap indexes are used at times.
And lastly, SQL Server 2000 now supports bitmap join filtering - this technique greatly improves the efficiency of processing joins when run in parallel.
The key thing about bitmaps is not the fact that bitmaps are used to represent collections, but the ability to consider (in the optimizer) and to exploit fast index intersection. SQL Server 7.0 does that, and SQL Server 2000 is getting even better at it.
If one really wants to compare two products then it is best to get the products past the paper benchmark - get your hands dirty - create tables, run queries on both - decide based on real world issues - not check boxes.
Next, a Good 'Ol NT Group Question
John Grandy writes:
Q: For a domain account to be able to log on to the PDC, what Groups and/or User Rights does it require, at minimum ?
John Smith replies:
A: The Default setting for logon to domain controller are:
Acccount Operators
Administrators
Backup Operators
Print Operators
Server Operators
To which John (G.) , of course, has the ever-popular follow-up question(s):
Hi John,
You are saying that **only** if an account is a member of one or more of the groups you list, then log-on to the PDC will be possible?
There is no other way to make it possible ?
Is the situation different for a BDC?
Also, what permissions are required for installing software on a PDC (since there is no local log-on, I can not make an account a member of the local administrators group).
To which John (S.) replies:
By default, those are the only groups that have the Log on Locally right on a domain controller. To change this, run User Manager for Domains and select the Policies/User Rights menu option. Add the desired groups to the list. The changes you make here will be applied to all the domain controllers in the domain. As far as installing software is concerned, it all depends on what that software installation has to do. Certain things, like writing to various parts of the Registry, installing services, and registering DLLs require administrative access, and there's no way around that. If all the install program does is decompress some files into a directory in the Program Files subdirectory, and create a shortcut on the current user's desktop, then anybody can do it.
That sounds like it pretty much sums things up – thanks John (S.)!
That's it for today - hope this helps!