I have 2 Oracle questions How do I translate this SQL Server statement to work on Oracle? Create table MyCount(Line int identity(1,1)) What is the equivalent of SQL Servers Image type for storing pictures in an Orace database? ...
I have created a user defined function to gain performance with queries containing 'WHERE col IN (...)' like this case: SELECT myCol1, myCol2 FROM myTable WHERE myCol3 IN (100, 200, 300, ..., 4900, 5000); The queries are generated from an web application and are in some cases much more complex. The function definition looks like this:...
Are there any good resources out there for T-SQL coding standards? ...
Is it possible to create an 'Entity' that will be an abstraction of a relationship between tables that live in two different tables, in two different databases, on two different machines, and even possibly using two different dbms? For example, if I have a SQL Server db on one machine that stores all my customers, and I have an Oracle d...
Our application needs to be able to render barcodes to PDF documents that will be accessible over the internet. Our technology stack includes Sql Server Reporting Services so we would like to leverage it and it's ability to render to PDF; however, we are not able to get it to embedded the FREE3OF9.ttf font in any consistent manner. I h...
SQL Server Edition: SQL Server 2005 w/ SP3 and 2008 Is there a built-in SQL Server stored procedures that will retrieve following information? Or a DMV (Dynamic Management View) would be great as well. I am interested mainly on how to find out FILEGROUP data of a table specifically. But it'd be better if there was a sproc that will ret...
I have two SqlDataSource controls on a page. One loads high level data and the other loads more details based on which high level item you choose. It is a part of a large search that has over 900,000 records and I am looking for ways to speed it up. Whether it is options I can add onto the SqlDataSource, things I can do to the sql que...
In my database I have 2 tables A and B A has columns ID, Title, and B_ID. B_ID allows nulls. B has columns ID and SomeText ID in both is primary key identity A also has a unique index on B_ID This seems like a basic way to say that A can have 0 or 1 B rows associated with it, and each B is associated with exactly 1 A. Is that correct s...
I have a system that involves numerous related tables. Think of a standard category/product/order/customer/orderitem scenario. Some tables are self referencing (like Categories). None of the tables are particularly large (around 100k rows with an estimated scale to around 1 million rows). There are a lot of dimensions to this data I ...
Im working on this large DB which has a lot of the business knowledge embedded in the SPs[I know!] and there is a lot of chaining between the SPs. i.e one stored proc calling another. Im want to find out a list of stored procedures which update a particular column. How would I do that. Using showplan_All as outlined in http://stacko...
In my current project I've been inherited with lots of long (1200+ lines) SQL Server stored procedures with some horrible indentation and formatting which makes them almost unreadable. Is there some tool that I can use to automatically format these and make them more readable? I don't want to go through it manually and indent it. ...
I've got a search form that could potentially return thousands of records; I'd like to show a message if the query returns more than 500 or so and make the user refine the search to get fewer results. Am I stuck with doing a Select Count before running the actual query? What's the best practice here? ...
Can someone explain the implications of using "with (nolock)" on queries, when you should/shouldn't use it? For example, if you have a banking application with high transaction rates and a lot of data in certain tables, in what types of queries would nolock be okay? Are there cases when you should always use it/never use it? ...
I'm storing papers in SQL Server 2005 and am looking for a way to paste in the text of a paper and then search for potential plagiarism (copied content) in the database. What's the best way to go about this? Is there a way to get a gauge for the extent to which something is similar to something else using full-text indexing, for several...
I have a table with a couple thousand rows. The description and summary fields are NTEXT, and sometimes have non-ASCII chars in them. How can I locate all of the rows with non ASCII characters? ...
I am creating a server level trigger in SQL 2008 to log table creation and drops. I need to log the database that the table was created in/dropped from. First I created a column with a default value of db_name(), but this always recorded master. Next I tried using this in my insert statement: EVENTDATA().value('(/EVENT_INSTANCE/Datab...
I have a sql query with 50 parameters, such as this one. DECLARE @p0 int, @p1 int, @p2 int, (text omitted), @p49 int SELECT @p0=111227, @p1=146599, @p2=98917, (text omitted), @p49=125319 -- SELECT [t0].[CustomerID], [t0].[Amount], [t0].[OrderID], [t0].[InvoiceNumber] FROM [dbo].[Orders] AS [t0] WHERE ([t0].[CustomerID]) IN (...
I have the following query Select field1 as 'node1/field1', field2 as 'node1/field2', (Select field3 as 'child1/field3', field4 as 'child1/field4' From table2 FOR XML PATH(''),TYPE,Elements) From Table1 FOR XML PATH('Root'),Elements This produces: <Root> <node1> <field1>data1</field1> <field2>data2<...
...specifically, the fn_listextendedproperty system function in MSSQL 2005. I have added an Extended Property to my database object, named 'schemaVersion'. In my MSVC application, using ADO, I need to determine if that Extended Property exists and, if it does, return the string value out of it. Here is the T-SQL code that does what I ...
Hi all, We are currently using Watin to do UI testing on our web application. In effect we are doing integration testing from top to bottom since we are using a test database and not mocking. In order to make sure the test database is in an expected state we have previously been using SQL Server's snapshot feature to rollback the data...