Hi. I'm having some difficulty in understanding the following WHERE clause in a T-SQL (SQL Server 2000/2005) query: update #tempTable SET Total_Avg=isnull(TerminationReason,'terminated'), Individual_Subscriptions=null, Business_Subscriptions=null, Other_subscriptions=null, -- snip. 10 more fields set to NULL. PMI...
Lets assume I want to save the count of datagrid rows which can be max. 24 because each row is 1 hour. To save the row index in the database a tinyint field would be totally enough. But back in my mind I remember slightly that databases are optimized for integers?! So is it worth to use tinyint? ...
This question is for ASP.NET and SQL Server developers. What are your best practices with respect to setting up your development and test environment? I'm interesting in the following issues: How many tiers do you recommend and what goes on on each tier? Just dev, test, and production or perhaps dev, test, staging, and production? W...
Right now I'm storing a number of records in SQL Server with a DATETIME column that stores the current timestamp using GETUTCDATE(). This ensures that we're always storing the exact date without having to worry about questions like "well is this 2:00 my time or 2:00 your time?" Using UTC ensures that we know exactly when it happened rega...
We have a web application that uses forms authentication to authenticate a user based on a user table in the database. (I.e. no active directory or SQL server user accounts are involved here). The web application accesses the SQL server using a service account. However, for auditing, authorization and other purposes, our stored proced...
I'm using Microsoft SQL Server 2005. I think I need a sub-query. I'm looking for one (1) row per customer, with the AvailableAction field be a concatenation of all the Actions for each customer. use tempdb GO IF DB_ID('myDatabase') IS NOT NULL DROP DATABASE myDatabase go CREATE DATABASE myDatabase GO USE myDatabase GO create table ...
I want to write an efficient query which returns a list of fruits by type, the lowest price for the type of fruit and the name of the fruit. Right now, I have a query which return me the fruit type and the lowest price for that type (see below). But I am unable to get the name of the cheapest fruit. Any idea how I can achieve that? Than...
I have a sticky SQL issue and I'm not the best with SQL... I have a table that has a varchar(40) column that has data in the following two formats: nn-nnn1nnn00000 nn-nnn-nnn-0000 The second data type is outdated; however because they are outdated they need to be viewed first in order. It was recommended to me to substring the last 5...
Possible Duplicate: Incorrect syntax near insert For some reason I can't get the tables populated. It worked before but I don't know why it won't run now. CREATE DATABASE Mort; Go USE Mort; ----------------------------------- /* **This script creates the ** Employee table with the foreign key */ -------------------------...
I am a student this is homework... The tables are there but data isn't being inserted. Thanks for any advice Msg 156, Level 15, State 1, Line 181 Incorrect syntax near the keyword 'INSERT'. USE Mort; Go INSERT INTO Employee (Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, Job_title, Hire_date,...
I'm still learning the ropes of OLAP, cubes, and SSAS, but I'm hitting a performance barrier and I'm not sure I understand what is happening. So I have a simple cube, which defines two simple dimensions (type and area), a third Time dimension hierarchy (goes Year->Quarter->Month->Day->Hour->10-Minute), and one measure (sum on a field c...
We are searching disparate data sources in our company. We have information in multiple databases that need to be searched from our Intranet. Initial experiments with Full Text Search (FTS) proved disappointing. We've implemented a custom search engine that works very well for our purposes. However, we want to make sure we are doing ...
I saw on a website, a guy had returned his database table information as an html table. It was pretty neat. His code is as follows: select 2 [@cellpadding] ,2 [@cellspacing] ,1 [@border] --This returns the header ,(select th from (select 'Database Table' th union all selec...
I am a student this is homework. I'm getting tired and confused. Any advice will be appreciated. I have two tables. Employee has the following columns: Last_name First_name Address Phone Job_title(FK) Wage Job_title has job_title(PK) EEO classification Job_description Exempt_Non_Exempt I need to select the employees’ last ...
I was given this SQL query by my boss and told to improve/optimize it DECLARE @pol_0 int, @pol_1 int, @pol_2 int, @pol_3 int, @pol_4 int, @pol_5plus int, @peril_0 int, @peril_1 int, @peril_2 int, @peril_3 int, @peril_4 int, @peril_5plus int, @loc_1 int, @loc_2_10 int, @loc_11_100 int, @loc_101_1000 int, @loc_1001_5000 int, @loc_...
I've seen mention of the Oracle WITH clause a few times around here for aliasing a subquery like this: WITH myData AS (SELECT id, text FROM SomeTable) SELECT myData from SomeOtherTable Does any version of SQL Server support this? If not, is there a particular reason they don't? Performance? Potential for incorrect usage? ...
I have a scenario in which i receive files from a source and i blindly dump the data in the files to a staging area DB into number of tables. Now i need to translate the data in the Raw Data tables to a format that my Primary database model would understand and eventually do a move of the translated tables to my Primary Database from the...
Does anyone know how can I make change for existing date value in SQL Server into new value? Example: Declare @StartDate DATETIME; SET @StartDate = '2010-07-07 00:00:00' Which I hope to declare another variable @DATETIME2 DATETIME based on @StartDate value changed into '2010-07-07 08:00:00' instead of default manually hard code it ...
How does one put a CHECK constraint on a column such that its range of acceptable values come from another table, without hardcoding? Here's a simplified example: OneManyTable RoleID TaskID 10 Val1 10 Val2 20 Val1 20 Val2 MetaDataTable pkID Class Value 1 A Val1 2 A Val2 3 B ...
What are some sql server management studios tools (preferably free) for the mac that can be used to do development work on MS Sql Server? ...