I know, I quite dislike the catch-all survey type questions, but I couldn't think of a better way to find out what I think I need to know. I'm very green in the world of database development, having only worked on a small number of projects that merely interacted with the database rather than having to actually create a new one from scra...
I want to add 1 year to a datetime-type column in every single row in a table. Adding using an UPDATE statement is easy for numeric types. ex: UPDATE TABLE SET NUMBERCOLUMN = NUMBERCOLUMN + 1 I'd like to do the same thing with a DATETIME-type... UPDATE Procrastination SET DropDeadDueDate = DropDeadDueDate + ? ...but I'm not sure wh...
I have a temp table, that isn't going away. I want to see what is in the table to determine what perhaps bad data might be in there. How can I view the data in the temp table? I can see it in tempdb. I ran SELECT * FROM tempdb.dbo.sysobjects WHERE Name LIKE '#Return_Records%' To get the name of the table. I can see it's columns ...
What does the warning mean? Why is the second example worse than the first? SELECT product_id, prod.name name, sample_id FROM lims.sample JOIN lims.product prod USING (product_id) vs. SELECT product_id, prod.name name, sample_id FROM (SELECT sample_id, product_id FROM lims.sample) JOIN lims.product prod /* ADV...
I am writing a PHP script that runs on a cron and pulls JSON data from an API [ title (text), path (text), visitors (integer) ] and stores it in a SQLite database. Every time it runs, if it sees an existing title, it should add the new visitors count to the existing visitors. If not, it should add the new data as a new row. Here's a sim...
What is the best index or ideas to improve date range lookups? (or query change) begin tran t1 delete from h1 where histdate between @d1 and @d2 and HistRecordType = 'L' insert into h1 select * from v_l WHERE HistDate between @d1 and @d2 commit tran t1 it is far slower than histdate = @d1 I have a...
In order to better visualize the relationships in a complex database, I'd like to create a graph/chart that shows the tables, and their relationships. Hibernate was used to create the database, and I have access to both the x.cfg.xml file and a Create schema. Is there a tool that reads in SQL and creates a graph for visual analysis? A...
I am facing a problem that running a stored procedure is taking too much resources which sometimes causes a time out on the server (especially when the CPU usage is more than 90%). Can anyone suggest what the best and quickest way is to spot the block which takes much resources, and also suggest a good way to solve it, please? I am us...
I'm making a Win app on the 3.5 framework. I'd like to include a database file with the app. This would a single-user db. I looked at the 2 different types of db files I can add, the sdf and mdf. Thw sdf (compact) db is missing some functionality I need, so I prefer the mdf. The description on the mdf file "Service oriented database f...
Hi, I have a co-worker who is working on a table with an 'amount' column. They would like to get the top 5 amounts and the sum of the amounts in the same query. I know you could do this: SELECT TOP 5 amount FROM table UNION SELECT SUM(amount) FROM table ORDER BY amount DESC But this produces results like this: 1000 (sum) 100 7...
Hi Everyone, I'm looking for some decent examples/samples using SSIS to do some ETL from one SQL Server database to another not necessarily within the same instance. The idea is to migrate rows of data with their heirarchies (relationships) from one OLTP database to another. There are some advantages SSIS offers us which makes it a go...
Hey guys. This is a follow-on from this question: After getting the right data and making some tweaks based on requests from business, I've now got this mini-beast on my hands. This query should return the total number of new jobseeker registrations and the number of new uploaded CV's: SELECT COUNT(j.jobseeker_id) as new_registrations,...
Consider a database with tables Products and Employees. There is a new requirement to model current product managers, being the sole employee responsible for a product, noting that some products are simple or mature enough to require no product manager. That is, each product can have zero or one product manager. Approach 1: alter table ...
I have a simple table with two columns (well two columns of interest, plus just an ID)... I have an ajax search which is just looking for keywords... which are then sent to my real search... the ajax search doesn't care what they are, but they need to be distinct... How can I merge the two columns together: City, Country Krakow, Polan...
Hi, i need a postgres function to return a virtual table ( like in oracle ) with custom content. The table would have 3 columns and an unknown amounts of rows.. i just couldn't find the correct syntax on the internet.. imagine this: CREATE OR REPLACE FUNCTION "public"."storeopeninghours_tostring" (numeric) RETURNS setof record AS DEC...
I know there have been numerous questions here about inline sql vs stored procedures... I don't want to start another one like that! This one is about inline (or dynamic) sql. I also know this point has become more or less moot with Linq to SQL and its successor Entity Framework. But... suppose you have chosen (or are required by your...
Deletes on sql server are sometimes slow and I've been often in need to optimize them in order to diminish the needed time. I've been googleing a bit looking for tips on how to do that, and I've found diverse suggestions. I'd like to know your favorite and most effective techinques to tame the delete beast, and how and why they work. un...
In regard to static data table design. Having static data in tables like shown: Currencies (Code, Name). Row example: USD, United States Dollar Countries (Code, Name). Row example: DE, Germany XXXObjectType (Code, Name, ... additional attributes) ... does it make sense to have another (INTEGER) column as a Primary Key so that all For...
I have a datagridview that I would like to validate using the cellvalidating event. however as the user doesnt navigate between cells or rows in the datagridview. just enters data in a cell in the datagridview and then clicks a save button the cellvalidating event doesnt get fired. any help would be greatly appreciated. ...
What kind of sql tricks you use to enter data into two tables with a circular reference in between. Employees EmployeeID <PK> DepartmentID <FK> NOT NULL Departments DepartmentID <PK> EmployeeID <FK> NOT NULL The employee belongs to a department, a department has to have a manager (department head). Do I have to disa...