SQLServerCentral / SQL Server 7,2000 / Strategies

Auto generated primary key

dilakshan31 — Mon, 12 Mar 2012 12:57:35 GMT

Ok guys, I'm a student, new to sql and really need help. How do u auto generate your primary key? I'm my case its 'event Id' note old sql. Think its 2003

Sql Server Load Balancer

walia_jagwinder — Sat, 18 Feb 2012 11:26:58 GMT

Hi all,My company is planing to implement load balancer for HTTP requests in case to increase the performance of website. We have two different servers, one is for website hosting and another is database server (sql 2008). As the website is having 80% of database operations, Does http load balancer will work? or Does we should have two load balancer , One if for HTTP requests and other is for sql server? Or can one load balancer will work for these two?

Apostrophes and Double Quotes - Should They be Allowed in table Text-Type Columns?

mtillman-921105 — Mon, 30 Jan 2012 13:01:43 GMT

As a best practice, should apostrophes and double quotes be removed from text in SQL tables?I can see how it should be allowed. For example, names such as "O'Hare" contain the apostrophe, so that is the best way to store them. The apostrophe is part of their name, after all.But if you allow it, will it trip up programming, not just in SQL, but 3rd party software using the data? And if they are allowed, do third party applications have to run each text string through a function to avoid errors? I'd also like to know if your data entry (or other data input) is validated so that apostrophes and/or double quotes are prohibited. Why or why not?

Data Warehouse question

David Lester — Fri, 07 May 2010 10:36:38 GMT

This is more of a 'what is a general best practice' question than a specific code or syntax question.Recently the data users here have started to define names of specific combinations of data, or want data broken out by sub-parts of a field. I am considering different ways of making sure the various report writers and ad-hoc users are arriving at the same datasets when quering for these. The simplest example is: We have a source sales table with around 7 million rows. There is a new need to report against how the sales order was given to the company, that being electronic submission, paper, or scanned. The way to determine this is from a single character position in the order#. There are a dozen values, which break down to the three categories.I am considering these options to make sure all users of the data get these categories correct.A user function, which takes in the order ID, and returns the category. The function really is just a case statement, so not doing anyother selects or queries.A calculated field in the table which again, uses a case statement to set the category.A new field in the table, which is populated as the records are added.In this which makes the most sense in your opinion/experience to do? There is a possibility of perhaps a dozen other groupings or flags which would work like this.The second situation is a grouping which will require self-joins, or lookup queries to arive at the correct flag. In these I could either create a function, or add fields to the table with the categories. In this situation type, there could be twenty or more flags.My inclination is to in all cases add either fields to the table, which are marked during the data loads, or to create a new table with the key from the sales data, and fields for the flags being used. However, I do understand there are differences in how one sets up a data warehouse vs a transactional system, and I have found many times when browsing through this forum amazing ideas which seem slightly counter-intuitive to me. So, I think it is worth asking others their thoughts on this, perhaps there is another option I have not thought of which is considerably better. Any thoughts? I am sure 'It depends', what sorts of things should be considered in such a depends case?Thanks!

Strategy for adding Constraints to legacy database

meade.swenson — Mon, 24 Jan 2011 08:01:34 GMT

I'm a senior developer working on a mid-sized SQL Server 2005 system (1000 tables) that is several years old. The original implementation did not make use of foreign keys (FK) and unique constraints (UQ), but left it to the developers' to ensure data integrity. As you would suspect, we are plagued with duplicate data in many parts of the system. New management is requiring the use of foreign keys and unique constraints help manage data integrity.We have scripts to scrub the data to the point where the FKs and UQs can be applied. The problem, however, is the software. Much of the legacy software was not written with such constraints in mind, so it is not uncommon for a process to fail due to a constraint violation, e.g., copying a row and then changing key information.Here's the question: Does anyone have a strategy for introducing FK and UQ constraints to a database and managing the subsequent software issues?One possibility: dedicate a developer to the problem, have him/her apply the desired constraints to a local database, exercise the software to test the constraints, and make the required changes when the software fails.Any suggestions would be greatly appreciated. TIA....mcs

I need help with my table design

dlam 18073 — Thu, 27 Jan 2011 18:17:56 GMT

Hi All,The database Iam working on is MSSQL2000.The database got a call log table [tblCallLog] (with 500k rows), and now I need to store some update details in some of the calls.The log table is not able to modify, so I think I need to add a table [tblLastCall] to store those changes.the [tblLastCall] table is going to store the latest call for each phone numbers.when I got a group of phone number details update, it will store into [tblLastCall] table, cuz only the latest call information will be use.Below is the tables:[code="sql"]CREATE TABLE [user].[tblCallLog]( [id] [int] IDENTITY(1,1) NOT NULL, << PK [dateCall] [datetime] NULL, [status] [nchar](10) NULL, [phNu] [int] NOT NULL, [firstName] [nvarchar](40) NULL, [lastName] [nvarchar](40) NULL, [address1] [nvarchar](40) NULL, [address2] [nvarchar](40) NULL, [address3] [nvarchar](40) NULL)[/code][code="sql"]CREATE TABLE [user].[tblLastCall]( [id] [int] IDENTITY(1,1) NOT NULL, << PK [dateCall] [datetime] NULL, [status] [nchar](10) NULL, [phNu] [int] NOT NULL, [firstName] [nvarchar](40) NULL, [lastName] [nvarchar](40) NULL, [address1] [nvarchar](40) NULL, [address2] [nvarchar](40) NULL, [address3] [nvarchar](40) NULL)[/code][code="sql"]CREATE TABLE [user].[tblNewNumbers]( [phNu] [int] NOT NULL, <<PK [firstName] [nvarchar](40) NULL, [lastName] [nvarchar](40) NULL, [address1] [nvarchar](40) NULL, [address2] [nvarchar](40) NULL, [address3] [nvarchar](40) NULL, [IsNew] [nchar](2) NULL)[/code]the [tblCallLog] table and the [tblLastCall] table is the same, but the Last one is used to store update details without modify the row in the callLog table.Question:I need to create two store procedures,one for Insert call logs - insert call log to [tblCallLog] (~2000 to 6000 rows need to insert) - insert call log to [tblLastCall] - delete old log for that phone number in [tblLastCall] (only store the latest call for each phone number)another one for update details - check [tblNewDetails] (~2000 to 4000 rows need to check) if that is a existing number in [tblLastCall] - if yes, update details - if No, set [tblNewNumbers].[IsNew] = YYes, I should provide some code, but my first question is - is this design OK? (two table contain some double information)cuz what i want to achieve is - when some of the numbes in [tblNewnumbers] exists in [tblCallLog] table,also has different details, and I need to store those new details.Then I got the plan above.

Moving data between databases

ducon — Tue, 17 Jul 2007 11:37:00 GMT

Hello,I have a couple of questions regarding a framework that I may want to change regarding the moving of data between databases on the same SQL Server.I will provide some background first, then describe my current method and then maybe you can help me figure out a better way to achieve this goal .I have a multi-user win32 application that uses SQL Server extensively. What happens is that through user interaction, system processing, etc... rows are inserted, updated and deleted pretty much constantly in the database.As time goes by the database grows larger and larger. This is mainly because I need to retain audit data for tracing purposes regarding transactions, inventories etc... So I need to be able to say who did what and at what time. This is available through reports that are embedded in the application so they use the same ADO datasource as the main application.Users complain after a while that some queries take a long time to run, despite regular index and database maintenance, simply because the database grows larger and larger. This also has some impact when they need to send me a database backup to investigate problems, find bugs etc... So in order to prevent this problem, I have designed an archiving functionality outside the application.There is another database for archiving purposes. It's an exact copy of the application database in terms of schema except for identity inserts which are OFF (since I want ID's to match).What we designed is a simple stored procedure that will look into the live database's schema, find tables that have certain timestamp fields that tell us that the record has been deleted. If we find one of these fields, then the table is eligible for archiving. For each of these tables, we will go day by day and build 2 dynamic SQL statements (using EXEC): one to INSERT INTO Archiving(SELECT * FROM LIVETABLE WHERE FIELD <= @MaxDate) and one to DELETE FROM LIVETABLE WHERE FIELD <= @MaxDate. Basically we want to archive all tables, but one day at a time (keeping at most X days of live data in the live database for reporting purposes, which is configurable by the users), and waiting 2 seconds between each table so that we can give some leeway and reduce locks on live tables for the application which has to keep running during that time.This stored procedure can be programmed using a simple Job with 1 step at a user's will.As you can imagine, this works great when this is set up when the live database is first created. However, most of the people I try to make implement this in their environment have waited about 2/3 years on average to implement this method. This means that the first time they want to run it, it takes days to run. Some tables have about 4,000,000 records by then and only 1,000,000 are to be kept so for each day, we need to move X records and this takes time just for locating the records to moveSo my question is this: how can you implement a solution that can work on multiple client sites to effectively move data from one database to another (on the same server), not just copy, but move, ie add to the archiving environment one and remove from the live environment without impacting performance of the live application too much? And it has to work on 2000 and 2005.Thanks a lot and don't hesitate to ask more questions regarding how it's currently designed if it is unclear.Greg

Alter constraints - non-clustered PK -> Clustered PK

schleep — Wed, 04 Aug 2010 13:03:04 GMT

Hi all, In SQL 2K (or SQL 2K8), is it possible to programmatically alter the PK constraints on the tables from nonclustered to clustered? When they're referenced by FKs?Or do I have to climb all the way down the referential tree, and drop and re-create the PKs from the bottom up?We're upgrading to SQL 2K8, and the lack of clustered indexes is becoming apparent.Thanks!P

n-hibernate and dynamic sql - DBA vs Developers

Knight — Wed, 20 Jun 2007 03:27:00 GMT

I'm currently working with a development team that are trying to implement n-hibernate as a design tool to speed up their development. My main problem with this application is that it uses dynamic sql and the developers are resisting using stored procs.

My db server environment is high volume and I'm greatly concerned that if they push through this approach it will not work in the live environment and create alot more admin for me in terms of security and will make changes to the SQL alot slower than if we were using stored procs.

Does anyone have experience of this sort of issue and how best to resolve this issue with developers?

My role is new to the company so resistance is an issue as they have not worked with a DBA before.

Cheers

updating 1,000 records takes 5-6 minutes I would like to decrease the time that takes

itsmeman — Tue, 05 Jan 2010 07:02:13 GMT

hello,i know this says sql 2005.. but the platform I am running on is sql 2000.i have a database with 1.17 million records.I need to make a change to 131,000 of these records.The best way determined to handle this change is through a cursor.To update 1,000 records it takes 5-6 minutes. I think this is unacceptable because to update 131,000 records it will take roughly 11 hours.Being new to table and performance handling, can someone direct me in the right direction to decrease the amount of time it takes to update 1,000 records?thanks.joshua

Define A Conditional Forigion Key

SQL Writer — Mon, 26 Oct 2009 03:50:55 GMT

Hi allCan Be Define A conditional foreign key on a table for exampleStatus Master---------------------Status ID Char(1)Description nVARCHAR(20)Table Name nVARCHAR(20)Status Master Data------------------------------Status ID description Table NameA Activate Client MasterD Deactivate Client MasterN New Order MasterI In process Order MasterC Complete Order MasterClient Master-------------------Client ID INTNAME nVARCHAR(20)Status ID Char(1)Client Master Data-----------------------------Client ID Name Status ID1 ABC A2 XYZ D3 PQR AOrder Master-------------------Order ID INTClient ID INTStatus ID Char(1)Order Master DATA------------------------------Order ID Client ID Status ID101 1 I102 2 C103 3 N104 1 INow we can implement a foreign key on status id field of both table client master and order master table .we wand that data in status id field in both table must be validate according table name given in status master.it means client master table may use only two status 'A' and 'D'.it can be possible??

Limiting view to only work when called from a stored procedure

Andrew in WV — Fri, 23 Apr 2010 10:41:10 GMT

I have a rather long, complex view that should only be called from inside a stored procedure (one right now, possibly more later). Because it takes so long to execute without a WHERE clause, and since it should really only be used inside procedures that specify the necessary conditions, I want to make sure this view is never called directly. Setting the correct access rights will guarantee this for the most part, but I would like to even restrict ad hoc queries by someone with administrative access to the database.To do this, I added the clause [code="sql"]WHERE @@PROCID <> 0[/code] to the end of the view definition. That way, if it is queried directly, it returns zero rows but it still returns the expected rows when called inside the procedure.Other than documentation, are there other potential problems this might cause? Is this overkill?(I know it needs to be documented, and that is an issue because it is too easy for someone to edit the view definition in the GUI and cause the comments to disappear.)AndrewEdit: Ok, I tried. When I preview this it looks the way I want it, but when I view the actual post it converts the SQL inequality to HTML entities.

ETL: 22 char unique key in source to 16 char unique key in target?

haggisns — Mon, 25 Jan 2010 07:26:39 GMT

Hi,We are doing an ETL from one database into another database.Our source data table has a primary key of 22 alpha-numeric. We need to load this into our target table (already with data) which uses16 alpha-numeric characters. We have managed to logically shave off 4 characters down to 18 chars, but if we go down to 16 characters for our target table we lose uniqueness.We do have other fields in the target table that we can make use of to hold data if this helps.The situation is that we are pulling Accounts receivable from one app to an existing AR application. We are doing an intial load and then we will perform a nightly load process afterwards as the source app will continue to be used. The target app is where we perfrom our backoff financials, BI, etc. In the future the target app will be made decomissioned.Just read a surogate ID article that might help but we need to be able to tie back our target table data to the source table in the source application as it is still being used.Are there any strategies for going from 22 len field to 16 and not lose uniqueness but still be able to resolve between the 2 tables/applications for example (auditing purposes).using SQL Server 2000Thanks

Need Architecture Solution

Swartkruit — Wed, 14 Oct 2009 02:35:51 GMT

Hi Everyone,Here is the situation:On the one side we have a SQL DB with staff (active & terminated). On the other side an Oracle DB, also with staff, but only active. Then the primary key for the two systems differ. On the SQL-side the key is CHAR (6) and on the Oracle-side the key is BIGINT or Numeric (Oracle). The two systems need to update each other. The dynamics of this combination is that when a staff member leaves and comes back, the key will always stay the same on the Oracle-side. On the SQL-side if the staff member leaves and comes back, the old record's status will become terminated when they leave and a new record is created when they come back. Then, a terminated record can also be updated a month or two in arrears from the Oracle-side and vice versa. My suggestion for the solution is a composite cluster with the 2 keys and a status field making it an ongoing unique combination i.e.SQL Key----- Oracle Key----- StatusPK-A----- PK-1----- APK-B----- PK-2----- TPK-B1----- PK-2----- APK-C----- PK-3----- APK-D----- PK-4----- APK-E----- PK-5----- APK-F----- PK-6----- APK-G----- PK-7----- AIf I'm completely wrong with my solution, please advise. If you guys have implemented or know of better real world solutions, please let me know as well.Regards,S

DTS Dynamic destination table

ChiragNS — Mon, 25 Jan 2010 05:58:55 GMT

Hi I need to design a DTS package where the destination sql server table is dynamic based on the input file recivied.The input files are based on different US states. So if the input file is 'CA.txt' then the destination table is 'CaliforniaK1_dtl' and if the input file is 'CA2.txt' then the destination table is 'CaliforniaK2_dtl'.There are 9 input files for 4 states. I do not want to create a total of 36 packages for this scenario.Thanks in advance.

Strategic Advice for Unique Requirement

sqlislife — Wed, 14 Oct 2009 11:16:02 GMT

Hi Experts,I have a very unique requirement and I need some advice on the best strategy to tackle it. I have 5 tables with a combined data of about 358 million rows (22GB). out of those 5 tables, 1 of them (2GB) is actively queried. I need to consolidate all 5 tables into one table that would be queried from time to time. Here is what I think should be done i.e. my ideas-Consolidate the other 4 (non queried table) first into one table-Move the 5th active table data to this new tableAre there better ways to achieve this? What about locking on the actively queried table when moving data? Would I really need to partition (date range) the 22GB data or indexes would do just fine for querying?Any suggestions from you experts would be greatly appreciated. Thanks

Copying Tables structure from SQL 2000 to Local database.

razausmani — Wed, 30 Dec 2009 00:24:21 GMT

Dear Friends,I have created my own DB which included a table named "Tables_Information". In this table i have created 8 columns which includes, Seq No, Module Name, Table Name, Table Desciption, Table Fields, Fields type, primary key as boolean, Foreign key as boolean.I want to copy all these information from SQL 2000 DB to my local table fields. Any method / procedure to fulfill this requirement???Regards,Raza UsmaniSoftware Engineer

Get a listing of table names on a particular file group

Michael-618813 — Thu, 03 Dec 2009 06:24:26 GMT

Hello.Is there a way to get a listing of tables on a particular file group from the INFORMATION_SCHEMA views? I've poked around in the system tables and found a table called sysfilegroups. I don't know (yet) if/how I can join this table to INFORMATION_SCHEMA tables to get what I want.Thanks in advance for any assistance that anyone can provide.

Database Design Help

ahsanzafars — Thu, 19 Nov 2009 02:13:33 GMT

Hello,I want to design a database in which editing an information requires an approval. and only when the updated information is used when the approver approves it. This means that i have to maintain two copies of data one is the original information( that will be used until the new information is approved) and other is the updated information.Could anyone please help me on this that how i will be doing this. as i have many tables and i dont want to keep a pending information table for each original table. Any help in this regard is appreciated.

Sharing data across multiple schemas

Brian McGee-355525 — Thu, 01 Oct 2009 02:32:10 GMT

I keep hitting this scenario, and would like to know what people think is the best approach.I have a main database schema, containing about 1000 tables and 100 dictionary tables (lookup data which is not modified by sql code). I have about 10 other schemas, some of which require access to this lookup data.The approach I have taken historically is to copy the dictionary tables on the basis that they are static anyway, however invariably they get out of synch. Yes I could add tighter controls to ensure people don't change one without changing another etc, however I am wondering is there a preferred technical solution to sharing dictionary data across multiple databases.They will not all be on the same server. I had thought about creating a separate database just for the dictionary tables and putting one instance on each server, and using replication to keep them up to date from a 'master', but then I can't create foreign keys!Ideas?

Select clause SQL Quary

saurabhvashistha — Fri, 03 Jul 2009 03:00:41 GMT

Lets Create a tableCreate Table test(Name varchar(50), SalaryAmount money)insert into test(Name, SalaryAmount) values(A, 10000)insert into test(Name, SalaryAmount) values(b, 11000)insert into test(Name, SalaryAmount) values(d, 110000)insert into test(Name, SalaryAmount) values(f, 10000)insert into test(Name, SalaryAmount) values(g, 9800)insert into test(Name, SalaryAmount) values(h, 10000)insert into test(Name, SalaryAmount) values(k, 8000)insert into test(Name, SalaryAmount) values(l, 10000)insert into test(Name, SalaryAmount) values(n, 15000) Now i Have to write a query which give me result like if Salary is greater then or equal to 10000 grade A else b likeResult should beName GradeA Ab Ad Af Ag bh Ak bl An b How do I accomplish this can anyone suggest

Adding compression to SQL2K Log shipping

c.kestas — Thu, 05 Feb 2009 01:48:20 GMT

Hi All,We use the standard log shipping in SQL2K to replicate over a wide area network.Although the mechanism works flawlessly 90% of the time, occasionally we have an issue with the size of transaction files to be shipped. If we could just compress the files before shipping and uncompress before restore it would be ideal. We have 10 databases scattered around europe and the saving on bandwidth would be substantial if we could just employ compression.As far as I know, SQLMaint in 2K does not support compression, so we started exploring other possibilities. One idea is to insert a second step on the source database to compress the file after it has been saved on disk. The question is how does one get hold of the filename generated in the backup step? The same question applies to the destination step. We need to insert a step to uncompress the file just copied, but then again, what's the file name? If this approach doesn't sound right, then by all means, we are open to suggestions.Another solution perhaps would be to use "Folder Monitoring" software on both the sending and receiving sides. I just don't like the idea of having independent tasks running in parallel to to the job.

Schema Naming Conventions

ckempste — Wed, 11 Jul 2001 02:13:00 GMT

Not again you sigh... :)Well, its on for young and old and im in the thick of it yet again. Table and column naming conventions are what I am after as far are recommendations and tried and tested schemas. I have gone for the following for sometime now:Tables : name as required to represent the business, eg. tradeP.Keys : post-fix with _id, eg. trade_idF.Keys : the joining column is listed as part of the column nameeg. traderto_trade_id this example means the table “trade_rto” joins to “trade” over “trade_id”The first portion (column prefix) should shorten to <= 8 characters in length with no spaces or underscore characters. For example: Employer => emp_<column name> Employer_site => empsite_<column name> Organisation -> org_<column name>The 8 character restriction is for the tables prefix, not the entire length. Even so, the entire length should not exceed 30 characters if possible.Another schema design we have in another app database uses the following:The other uses a slightly different approach. The only difference in this method is the naming of columns, which is especially evident in the key-column referencing. For example: Training_Product ----------------------- Course Training_product_id (pk) Training_product_id (fk)In this case, as the training product table is the parent, all other referring tables use the same primary key column name for the foreign key column name. This is a common scheme, but unfortunately does not allow a developer to “follow the relationship model” from one table to the next and thus, for large data models, can be time consuming when creating large complex queries. Another problem is that extensive use of table aliasing is required in such a scheme.I have also heard of schemes where the data type of the column is in the naming of the column. This seems very strange and something I wouldnt like to explore for a variety of reasons.What I havent gone into is issues of replication, and inter-related schemas with the same table names.Ideas?CheersChris

Need help in generating a procedure

nithin.cool — Tue, 28 Apr 2009 00:04:15 GMT

I wanted to create a procedure which automatically sends email to people on their birthdays.The procedure has to be scheduled in a windows 2000 server. I am using sql server 2000. Could anyone suggest me how to do this?The code should not ask for any inputs during runtime.

Data Model of BPM applications

adeel alvi — Sat, 11 Apr 2009 01:15:42 GMT

Hi guys , we have created a simple asp.net/sqlserver 2000 based Buisneess Process Management tool in which we have a feature that user can create simple one column form and attached to the process and define rules and path.We save the dynaimcally created forms controls values in EAV design tables but as the transactions is increasing we are have the performance issue on reporting on that custom forms value , my question is whats the alternative to this problem in which main req of the BPM solution is that user will create forms dynamically ,and what do u guys thing the implementation of big vendors of BPM like Skelta or Ultmus manages this problem. do u thing xml can save me ?? if yes then how or how u guys will design the model of BPM applications

A Case For Concatenation (Building Delimited Strings ) In T-SQL

The Dixie Flatline — Tue, 24 Feb 2009 10:42:28 GMT

I have heard some criticism of doing concatenation (building delimited strings ) in T-SQL. Two reasons given for this criticism are (1) to protect the resources of the SQL server by farming the load of string manipulation out to the UI, and (2) to "do things where they make sense". Basically, other languages are better at string manipulation and other tasks, so why bother doing it in SQL? Just return the rows and let the UI handle it.This makes obvious sense when building strings. SQL passes a FirstName|LastName|OverdueAmt as separate columns to a UI that can then build the string it wants, whether that string is John SmithSmith, JohnDear John orDear Mr. Smith we currently show your account as having an overdue balance of $250.In the last instance especially, it is foolishly wasteful to add all the constant information at the server then pass it over the network to the application.However, a common requirement is to build a delimited string from a single column value repeated for multiple rows. This is something easily accomplished in T-SQL with very few lines of code, so I consider it to be a draw with respect to string manipulation capability. That leaves the issue of workload on the server.The code at the bottom generates a table of unique first names, last names and department numbers. The schema for the tables containing the first names and last names are provided. I populated them by simply typing in 50 or so names off the top of my head, in some place duplicating last names such as Smith or Jones. But you could just as easily populate them with random strings. Department number is a random number between 1 and 20.The resulting table (#temp2) will be populated with approximately 22,000 unique name/department combinations. The rest of the code compares SQL concatenation of this table with simply returning the values. On my laptop, the concatenation actually runs faster than the simple display of the data. Furthermore, the result set of the concatenation is approximately 1200 rows, instead of over 22,0000. This is, in effect, a compression of the data, because the Firstname/Lastname columns aren't being repeated an additional 20 times. When output was directed to text files, the concatenated version was around 110k in size and the simple display was over a meg. That is nine times the data which has to be buffered and passed to the UI code. If temporary or permanent holding files are the target of this output, there would also be additional logging involved. To me it seems that concatenation at the SQL server may ultimately be reducing the load on the server. What am I missing here?-------------------------------- concatenation test------------------------------set nocount on;--- pseudo random number generation for numbers between 1 and @X SELECT TOP 100000 IDENTITY(INT,1,1) AS RowID, ABS(CHECKSUM(NEWID())) % 50 + 1 AS FKey, ABS(CHECKSUM(NEWID())) % 50 + 1 AS LKey, ABS(CHECKSUM(NEWID())) % 20 + 1 AS Dept INTO #temp FROM Tally; select distinct firstName,lastname,dept into #temp2 from #temp join Lastnames on lastNameID = Lkey join FirstNames on firstNameID = Fkey select count(*) as [#temp2 rows] from #temp2 declare @timer datetimeset @timer = getdate() print 'Display Only'set statistics time on;select * from #temp2 ORDER BY LastName,firstName,deptset statistics time off;print datediff(ms,@timer,getdate()) set @timer = getdate() print 'Concatentation' set statistics time on; select firstName,LastName ,stuff(( SELECT ',' + cast(dept as varchar(2)) FROM #temp2 t2 WHERE t2.LastName = t1.LastName and t2.firstName = t1.firstName -- must match GROUP BY below ORDER BY dept FOR XML PATH('') ),1,1,'') as [Departments]from #temp2 t1GROUP BY LastName,firstName -- without GROUP BY multiple rows are returnedORDER BY LastName,firstName set statistics time off;print datediff(ms,@timer,getdate()) drop table #tempdrop table #temp2

Referring to external database dynamically

mattaustin — Wed, 18 Feb 2009 06:35:14 GMT

Hi All,Imagine the situation....You have a production environment consisting of a number of proprietary databases and a configuration database that you control, all running on SQl Server 2000. Since you are not allowed to release stored procedures to the proprietary database for fear of invalidating your support agreement, you have a large number of stored procs on your configuration database (lets call it config_db) that refer to tables in your proprietary database through hard-coded names. All well and good so far.Now, you want to restore your environment (all databases) to a development environment where the database names are different to in production. Suddently the external database name references in your stored procedures stop working!So, you can...a.) painstakingly go through your stored procedures and re-release them with the correct database names - not ideal since there are several hundred stored procs.b.) convert all the stored procs in production to use dynamic sql so that the database name is determined by use of a function or something similar. However, the internet is littered with dire warnings about the over-use of dynamic sql in stored procs for performance reasons, especially where the sp is called many times.c.) apparently there is a possibility of using synonyms for this in SQL Server 2005 but we are not going to be able to upgrade for some time yet for political and budgetary reasons.Does anyone else have a better suggestion for achieveing true portability of stored procedures between environments? This problem is consuming a large amount of time and meaning that we are restoring our production DBs to dev as a last resort when really this should be done as a matter of course before any new developmental work is carried out, so any advice would be greatfully received.Thanks in advance,Matt

Need strategy for Disaster Recovery

Arun Jebakumar — Sun, 01 Feb 2009 23:34:10 GMT

I have production instances and the Disaster Recovery instances in different locations. When I try to implement log shipping, I realize that I cannot full backup of the databases. My database sizes are over 100 GB, so its not possible to transfer them fully daily over the network.I need to configure log shipping, as well as I need to perform full backup of the databases daily. Is it possible by some way?

Enterprise solution

darren.lalonde — Fri, 03 Oct 2008 07:27:12 GMT

Hi there,We currently have one big SQL database hosted in one place with 80 sites attaching through citrix.the database has around 1000 users over these 80 sites and occasionally get locks which casue the whole of the 80 sites to stop working.we would like to split the 80 sites into clusters of say 6/7 sites therefore having around 30 different databases. We still need one central database for reporting and one for lookup management which would be read only on sites.I am assuming that if each of the sites have there own person table the id's will be an issue if trying to re-group for reporting in one database?What would be the best strategy...?Is Replication the way forward ? Or is a linked server for the codes and some sort of DTS push for the reporting a better way to go.

Approving Changes

dlongnecker-802303 — Wed, 14 Jan 2009 10:01:48 GMT

Heyo,Anyone who's seen my posts on here know's I'm a DBA intern for a big company who's been learning alot about MSSQL Server the past year or so. This is the first time I'm posting a question beyond how to write a particular complicated query, and this is a real stretch for me.Right now I'm working on an in-house application directory which stores information about applications that can't be queried or otherwise generated. The data includes information such as who wrote the apps, who uses them, what the app does, what other apps it relies on, where it's hosted, etc. When application developers make changes, they have to submit changes to this directory to be reviewed and approved by managers and various other beaurucratic processes.The problem I have is the higher ups want to view the changes exactly as they will appear in the database before they approve them, and commit the changes to the DB. The other DBA's and I have been discussing exactly how to do this including duplicating all the tables, duplicating the entire database, etc. The changes can be complicated including inserts, updates, and deletes on several tables at once. Does anyone have recommednations or know of any papers online or something discussing strategies on issues like this?Thanks!

upgrade to 2005 or 2008?

george sibbald — Sat, 03 Jan 2009 15:00:54 GMT

Question: whether to upgrade SQL 2000 to 2005 or go straight to 2008?About a year or so ago Steve was advocating making the jump direct to 2008, http://www.sqlservercentral.com/articles/Administration/3094/ whilst around the same time MS were leaning towards going to 2005 first, then 2008. I was wondering, now that 2008 is out, what would you do?To come clean, I ask because it is a decision I have to make, and I am interested in the opinion of the forum. My environment is about 90% SQL2000, 10% 2005, spread across 70 odd servers and about a 1000 databases. The main complicating factor is the use of consolidated servers supporting multiple apps.Its early days yet and I realise an in-depth analysis will need to be done on compatibility issues with the numerous applications but the thought of being able to go straight to 2008 and then not worry about an upgrade for 5 years or more is quite tempting. Off the top of my head a possibility is leave the existing 2005 servers where they are and just upgrade the 2000 servers to 2008, to spread the load and risk a bit.Your thoughts, intentions?

handling data for within application

JKSQL — Wed, 07 Jan 2009 09:57:01 GMT

Currently in our application we have three levels. We are allowed one company (C), many businesses (B) under that company, and then many facilities (F) under a business. There is information that can be configured at any level. Meaning a contact can be at the Comp., bus., or Facility. if it is done at the F level other facilities can not see the contact. If it is Done under the C level everyone can see it.It is just not contacts that can be configured at different levels. It is a majority of the system architecture that allows this. All this information is keyed in Guids so there is no way for duplication at any level. Here is a couple catches that I have come up on. Between facilities you can transfer transactions to other facilities. Another one is that say a contact is set up at the C and now they want at the B level to have different contact information. All pre-existing transactions need to be remapped programmatically. This is not a great example but in real life it could be millions of recordsSo now for my question. What is the best way to handle this type of information. There are three ways we are thinking of saving this information within the system1) Save it at the level which the user sets it as. Meaning user wants a contact at the C,B,F levels. Problem here is when looking for that contact the program will need to scour the DB at each level to see where it should get the information since it will not know which configuration block it is at. I am thinking that will be slow and cumbersome. Also if they decide midstream that a costing block needs to be at a different level then how do we handle historical records. Seemed confusing to me.2) No matter where the data is saved we should replicate the information down to the facility. So if they save in the C the same information is replicated to the B and F. now we have duplication within the tables and have to maintain changes to the original. This works as an advantage when they want to turn on something at the business level so historical data is not an issue.3) No matter where it is created it is only saved at the facility level. Here we have duplication across F but it would eliminate the other levels.This is what we are thinking. I do not think any of these are good or will even work. I am hoping that someone has done something like this before and can lead me in the right direction. If there is something that needs to be defined better or cleared up please ask...Thanks for the help.

Load XML Data from various resources

Joe Contreras-290946 — Sun, 04 Jan 2009 17:08:36 GMT

Hi,I'd like to know how I can do the following:1. Receiving various XML files from about 50 companies2. Each XML file will contain the same data, however the "field nodes" in the XML file are different, for example, one XML can be something like thisXML File1 XML File2 3. Load those different files into the same table or tablesI know I can use bulkcopy using either a C# or SSIS, however the design issue is how can I design this and determine that the field nodes in the different XML files contain the same type of data?Any thoughts or ideas or links would be great.:unsure:Thanks,

Manipulating Tables, and trying to maximize its purpose?

Tom.Bauto — Sun, 07 Dec 2008 20:30:46 GMT

I need help from our Genius DBA's!Can i login another user when i was actually using SA account? is it possible in query?I was thinking like this:as I was logged on as SA and I created a table named dbo.Product, then I created a new user named User1 with the same access as I do, then I will shift to that user and create a table named User1.Product. There I have created table with the same name with different schema. But what I'm trying to reach is when every time I add a new record in Product table only the logged on user will add record to which I have created table.Scenario:Logged on user: User1, added new record in Product table values 'U-Prod1', and 'U-Prod1'then user: SA, added new record in Product table values 'SA-Prod1', and 'SA-Prod1'when User1 add record it will basically saved to User.Product table and SA will be saved in dbo.Product table, so right there I could get my own clustered tables based on the logged on member, only that I could only create table maximum to SQL's table limit. but wont be a problem.I just want to know how will I be able to do that query.

Manipulating Tables

Tom.Bauto — Sun, 07 Dec 2008 20:28:37 GMT

Preserving data-set for later use in printable version (keep resultset same)

Shamshad Ali — Fri, 05 Dec 2008 07:05:41 GMT

I have a simple classic ASP website using SQL Server 200, with busy visitors hits on site. The admin has reporting section from which by giving date range as parameters he/she can view users activity.we have logic on these reporting pages that he can generate report based on date dange as parameter and it will be displayed on browser first, there there is an option to generate its HTML (printable version) and export to Export to EXCEL. The problem is when user generate report, with same parameters lets say it shows 500 users hits, but when user click printable version or export to excel same query runs and it gives 509 hits. That is geneuine issue because 9 users hits site later. Now that is issue, we want to maintain state based on admin user. we were thinking to use temp table logic, but would it be helpful? I need suggestions if someone already faced this issue and fixed this via some logic, Please share.ThanksShamshad Ali.

"primary key" in Table Variable

Paul -244830 — Wed, 28 Jun 2006 14:01:00 GMT

I ran into a problem recently with an unexpected solution that I wanted to post on SQLServerCentral. The reason I am posting it here is that I want to know WHY it worked.I have a stored proc with a simple table variable, something like this:DECLARE @t TABLE (someID Int primary key)The proc is only about 100 lines and is fairly straight forward. On my dev box, the query consistently runs around 1.5 seconds. On our test server is ran a consistent 1 second. On the production box, it consistently timed out. While we were trying to debug the issue, a coworker sent the proc to a friend of his who is a lead DBA at myspace.com of all places. One of his coworkers suggested removing the "primary key" declaration from the table var. All of us were stymied as to why this would make any difference, but once it was removed, it ran fine on the production machine. I thought this might have been due to a lack of memory available to the SQL server in production, but this was not the case. The production SQL server had another Gig of RAM available to it.Anyone know why this would make any difference?Paul Perrick

TEST

cidr — Wed, 08 Oct 2008 08:02:55 GMT

TEST

Server Farm - Load Balancing

Mark Worsnop — Tue, 16 Sep 2008 09:17:51 GMT

I am trying to build a system (Farm) where we can load balance RDP users that in turn use an application that accesses SQL 2008. The RDP part is set up well and is good. But now I am trying to figure out a way to have SQL Server on each machine, but have a common database on the SAN iSCSI box. I don't think there is a way to do this, is there?The only option I can see at this time is to have SQL Server installed on both servers, but have one of them the active and the 2nd SQL stopped. This keeps more load on the 1st server obviously. Then it will have to be a manual process to start the 2nd SQL if the 1st server fails. Does clustering do load balancing or only fail over? I am wondering if I should make these servers into a cluster and SQL into a cluster too? I looked at clustering a long time ago, and I seem to remember that one machine did the work, and the 2nd one took over if the 1st one failed. Any ideas?

Are Stored Procedures Evil?

Tim OPry — Thu, 17 Apr 2008 06:27:12 GMT

I am working on a MySQL project and in doing some research, ran across this site/article. I must admit that I am one of those that uses stored procedures whenever possible - triggers though, only when absolutely necessary (which is rare for me). I did think he made several interesting points and would be interested in what some of you think:[url=http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html][/url]