For databases, does choosing the correct data type affect performance?
And if so, why? I mean, is a tinyint faster to search than int? If so, what are the practical differences in performance? ...
sql
How do I convert an arbitrary list of strings to a SQL rowset?
I have a simple list of strings, which may be of arbitrary length. I'd like to be able to use this list of strings as I would use a rowset. The app in question is running against SQL Server. To be clearer, if I do SELECT 'foo', 'bar', 'baz' I get 'foo', 'bar', and 'baz' as fields in a single row. I'd like to see each one of them as a...
How to do regex HTML tag replace in SQL Server?
I have a table in SQL Server 2005 with hundreds of rows with HTML content. Some of the content has HTML like: <span class=heading-2>Directions</span> where "Directions" changes depending on page name. I need to change all the <span class=heading-2> and </span> tags to <h2> and </h2> tags. I wrote this query to do content changes in ...
In SQL how to I exclude a record if there are more than 3 characters after a dash..
In SQL how do I exclude a record if there are more than 2 characters after a dash.. Example I only want to return records that match the following AA00000-0 but the table also has recoreds like AA0000-000,AA0000000-00 I need to return only records that have a single digit after the dash ...
Is it poor practice to build an SQL query using WHERE 1=1 AND ...
I'm writing a PHP script that builds an SQL query by concatenating the string and adding conditions to the WHERE clause as needed. Would it be better practice to use WHERE 1=1 so that the first condition is satisfied and the script can just concatenate an AND x = 'y' to the query, or should I write the extra code to check if a clause ha...
Convert a value based on range
I need to convert a number to another value based on a range: ie: 7 = "A" 106 = "I" I have a range like this: from to return-val 1 17 A 17 35 B 35 38 C 38 56 D 56 72 E 72 88 F 88 98 G 98 104 H 104 115 I 115 120 J 120 123 K 123 129 L 129 infinity M The values...
How do you think while formulating Sql Queries. Is it an experience or a concept ?
I have been working on sql server and front end coding and have usually faced problem formulating queries. I do understand most of the concepts of sql that are needed in formulating queries but whenever some new functionality comes into the picture that can be dont using sql query, i do usually fails resolving them. I am very comfort...
Multiple rows with a single INSERT in SQLServer 2008
I am testing the speed of inserting multiple rows with a single INSERT statement. For example: INSERT INTO [MyTable] VALUES (5, 'dog'), (6, 'cat'), (3, 'fish) This is very fast until I pass 50 rows on a single statement, then the speed drops significantly. Inserting 10000 rows with batches of 50 take 0.9 seconds. Inserting 10000 rows ...
Why are joins bad when considering scalability?
Why are joins bad or 'slow'. I know i heard this more then once. I found this quote The problem is joins are relatively slow, especially over very large data sets, and if they are slow your website is slow. It takes a long time to get all those separate bits of information off disk and put them all together again. sourc...
How to allow the user to select an input file for my SQL AdventureWorks web application?
I have a SQL and VS 2008 web application that I need to capture the full file path (directories and file name) from the selected file on the client computer. And I want the user to be able to run this application from a Mozilla Firefox browser. This is the trick cause IE supports file path but apparently not Firefox. So user selects a ...
Read multiple tables from dataset in Powershell
I am using a function that collects data from a SQL server: function Invoke-SQLCommand { param( [string] $dataSource = "myserver", [string] $dbName = "mydatabase", [string] $sqlCommand = $(throw "Please specify a query.") ) $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=...
how to enter values in rowguid column?
can anyone please tell me the right way to insert values in the rowguid column of the table? I m using sql server management studio ...
Best solution for a comment table for multiple content types
I'm currently designing a comments table for a site I'm building. Users will be able to upload images, link videos and add audio files to the profile. Each of these types of content must be commentable. Now I'm wondering what's the best approach to this. My current options are: to have one big comments table and a link tables for eve...
Problems using an id from a model inside a custom sql query in Rails
Hi there, I want to do a model class which associates to itself on Rails. Basically, a user has friends, which are also users. I typed the following inside a User model class: has_many :friends, :class_name => "User", :foreign_key => :user_id, :finder_sql => %{SELECT users.* FROM users INNER JOIN friends ...
Are stored procedures and triggers the same in Sql Server and MySQL?
Hello, I have two stored procedures and one trigger for a class I am taking that are done in sql server 2005. I want to recreate them in mysql with phpmyadmin. Everytime i put them in I get a 1064 error. Is this becuase phpmyadmin does not handle stored procedures or triggers well, or is it becuase the syntax from sql server 2005 to m...
SQL Server 2005 IsNumeric Not catching '0310D45'
I've got this value '0310D45' I'm using isnumeric to check if values are numeric prior to casting to a bigint. Unfortunately this value is passing the isnumeric check. So my query is failing saying: Msg 8114, Level 16, State 5, Line 3 Error converting data type varchar to bigint. What is the simplest way to handle this. I was thinki...
MySQL COUNT() multiple columns
Hello, I'm trying to fetch the most popular tags from all videos in my database (ignoring blank tags). I also need the 'flv' for each tag. I have this working as I want if each video has one tag: SELECT tag_1, flv, COUNT(tag_1) AS tagcount FROM videos WHERE NOT tag_1='' GROUP BY tag_1 ORDER BY tagcount DESC LIMIT 0, 10 Howeve...
Associating tags with entities, store the tag once or not ?
Consider I have a table with notes which could be associated with zero or more tags, how would I decide to do create table notes ( id int , -- primary key -- other fields ); create table tagmapping ( noteid int, -- refers notes.id tagid int, -- refers tags.id ); create table tags ( int id, -- primary key tagname varchar(255) ...
SQL Server denormalization question - store user data in 1,3, or 21 tables?
A section of our web site calls for asking the user 20 multiple-choice questions about themselves (a "profile"). This part of the web site will be frequently viewed, and occasionally updated. The web site is expected to develop a high amount of traffic, so some consideration is being given to try and prepare for performance issues. Th...
SELECT set of most recent id, amount FROM table, where id occurs many times
I have a table recording the amount of data transferred by a given service on a given date. One record is entered daily for a given service. I'd like to be able to retrieve the most recent amount for a set of services. Example data set: serviceId | amount | date ------------------------------- 1 | 8 | 2010-04-12 2 ...