We are developing ETL jobs and our consultant have been using "old style" SQL when joining tables select a.attr1, b.attr1 from table1 a, table2 b where a.attr2 = b.attr2 instead of using inner join clausule select a.attr1, b.attr1 from table1 as a inner join table2 as b on a.attr2 = b.attr2 My question is that in the long run, i...
Hi All, I am working on a DB Structure and it seems like I have ended up with a lot of many to many tables. The reason I have am getting so many of them is that my base table is items and each item can have multiple occurrences of the search criteria that will be surfaced to the users. So I normalized all of the search criteria and us...
I have a need to generate all combinations of size @k in a given set of size @n. Can someone please review the following SQL and determine first if the following logic is returning the expected results. And secondly, is there a better way? /*CREATE FUNCTION dbo.Factorial ( @x int ) RETURNS int AS BEGIN DECLARE @value int IF ...
You would think it would be easy to find out what 1 means, but google is not being helpful (I would guess that it does not see the @@ part and error is to generic. MSDN (at least on the @@Error page and Concept page) is also unhelpful. Any help here? ...
Hi, This is in SQL Server 2008. I plan to create two tables to represent a Bill of material. The Item Master will have data as follows :- [ID] [Item Code] [Item Description] 1---- A001 ---- Assembly 1 2---- B001 ---- Sub Assembly 1 (Child of Assembly 1) 3---- B002 ---- Sub Assembly 2...
I have a depricated stored procedure which should no longer be called from code, but there is some system which is still calling it. This is a production server so I have very limited indirect access to it for performing diagnostics. Is there any way to determine the machine which is calling a particular stored procedure from within th...
I am having some issues when running sp_executesql on a database table. I am using an ORM (NHibernate) that generates a SQL query that queries one table in this case. This table has about 7 million records in it and is highly indexed. When I run the query that the ORM spits out without the sp_executesql, it runs very quickly and pro...
How can I see all disk usage of all my databases on a given SQL Server in one single query. I have around 15 different databases on my server and I want to see which one is using the maximum disk space. I know I can see reports of Disk Usage per database in SSMS or logon to the server and see the size of MDF/LDF files but this seems li...
What is the most common naming convention for SQL Database tables? Was it historically one way and now it's better to do another way? What are the most common practices now? ...
I have deployed my web application on www.aspspider.com (which also support MS SQL server db), while connecting to the database, it is giving me an error. So what changes should be made in the connection string, so as to make the connection possible. Error Message: A network-related or instance-specific error occurred while establishin...
Hello everyone, I am new to SQL Server 2008 Full Text index function. I am learning from this tutorial, http://www.codeproject.com/KB/database/SQLServer2K8FullTextSearh.aspx. I am using SQL Server 2008 Enterprise. In the first step of the tutorial, it is mentioned that I need to "Create a Full-Text Catalog", but I did not find the New ...
Hello everyone, I heard SQL Server 2008 uses less disk space for the same amount of stuff (i.e. more compact format, and more disk space efficient, I mean storing MDF/LDF file) compared to previous version, like SQL Server 2000. Any document to prove that or describe that feature? I just need a high level learning, do not need too much ...
i have been asked to write a query which in should rank customers base on the quantity of orders they have. the main important factor is that if two customers have been ordered the same quantity they should have be in same rank. i want to know the way to handle this query. for this i have began as below: select tble1.customerid, RANK()...
i want to define a scaller fucntion which in that im giong to return the result into a variable but i do not know how to do this. CREATE FUNCTION dbo.Funname ( @param int ) RETURNS INT AS declare @returnvar int select @returnvar = select colname from tablename where someconditions = something return(@returnvar) i want to make a funct...
I'm trying to save a binary file stored in a SQL database with a SaveDialog, the function RetrieveFile retrieves the specified file from the database as a Byte array, here's what I have so far: Private Shared Function RetrieveFile(ByVal filename As String) As Byte() Dim connection As New SqlConnection("Data Source=SERVER\SQL2008;Ini...
there is a time slot table. it exists on two or more computers. the table maintains the reservation of the time slot for the complete year. assume that the connection between the servers breaks. user on site1, enters data like :-- timeslot(3:8) reserved for(this) where timeslot is the primary key user on site2 does the same. pro...
Hi, I have an SQL server 2008 database instance on one machine. Now I want to copy this database to another machine. I use the script wizard inside SQL Management Studio to generate a SQl-script with the schema and data. The script-file is rather big (around 17 GB). Then I run the sql-script on the target machine it results in a : Ms...
I'm using SQL Server 2008. I've got a column NVARCHAR(MAX) in a table which I want to make sure is unique. The table has 600,000 records and grows every day by 50,000 records. Currently before adding an item to the table I check if it exists in the table and if not I insert it. IF NOT EXISTS (SELECT * FROM Softs Where Title = 'exampl...
Hello, I'm using SQL Server 2008. I have a NVARCHAR(MAX) column called Title and i want to add an unique index for it. Because the column is bigger than 900bytes , I decided to create a HashBytes computed column (based on recommendation on StackOverflow). How do i create the HashBytes column? alter table Softs add TitleHash AS (has...
I am running a batch of statements on several columns and tables and want to retrieve information on what errors occur. The statement is a type change (varchar to nvarchar) and when it fails, it seems to return 2 errors. Msg 5074, Level 16, State 1, Line 1 The object 'DF_XXX_YYY' is dependent on column 'YYY'. Msg 4922, Level 16, State ...