I used to work in a place where a common practice was to use Pair Programming. I remember how many small things we could learn from each other when working together on the code. Picking up new shortcuts, code snippets etc. with time significantly improved our efficiency of writing code. Since I started working with SQL Server I have bee...
Given the following: declare @a table ( pkid int, value int ) declare @b table ( otherID int, value int ) insert into @a values (1, 1000) insert into @a values (1, 1001) insert into @a values (2, 1000) insert into @a values (2, 1001) insert into @a values (2, 1002) insert into @b values (-1, 1000) insert into @b valu...
How do you deal with source control management and automated deployment (configuration management) of database tables. I work in a SQL Server environment and it's pretty easy to script out drop and create files for stored procedures/triggers/functions even jobs. It's also easy to handle scripting out the creation of a new db table. Ho...
Hi, I'm sorry if the question is long-winded and/or unclear. I will try and make it clearer with any feedback I get. I've got two tables: TableA ID, Name TableB ID, SomeColumn, TableA_ID (FK for TableA) The relationship is one row of TableA - many of TableB. Now, I want to see a result like this: ID Name SomeColumn 1....
One of my DBs have grown closer to permitted size. Inorder to find out the table containing the max data, i used the following query: exec sp_MSforeachtable @command1="print '?' exec sp_spaceused '?'" It returned the culprit table comprising the max data. As a next step, i want to cleanup the rows based on the size. For this, i would...
The situation: we have a large database with a number of denormalized tables. We frequently have to resummarize the data to keep the summary tables in synch. We've talked on and off about using computed columns to keep the data fresh. We've also talked about triggers, but that's a separate discussion. In our summary tables, we denormali...
I have two tables containing Tasks and Notes, and want to retrieve a list of tasks with the number of associated notes for each one. These two queries do the job: select t.TaskId, (select count(n.TaskNoteId) from TaskNote n where n.TaskId = t.TaskId) 'Notes' from Task t -- or select t.TaskId, count(n.TaskNoteId) 'Notes'...
Hello! I have an MSSQL2005 stored procedure here, which is supposed to take an XML message as input, and store it's content into a table. The table fields are varchars, because our delphi backend application could not handle unicode. Now, the messages that come in, are encoded ISO-8859-1. All is fine until characters over the > 128 stan...
I have a VARCHAR column in a SQL Server 2000 database that can contain either letters or numbers. It depends on how the application is configured on the front-end for the customer. When it does contain numbers, I want it to be sorted numerically, e.g. as "1", "2", "10" instead of "1", "10", "2". Fields containing just letters, or lette...
How to create a database using T SQL script on a specified location? Let's say, I want to create a SQL server database on D:\temp\dbFolder. How to do this? ...
I was writing a (seemingly) straight-forward SQL snippet that drops a column after it makes sure the column exists. The problem: if the column does NOT exist, the code inside the IF clause complains that it can't find the column! Well, doh, that's why it's inside the IF clause! So my question is, why does a piece of code that shouldn't b...
What are some hidden features of SQL Server? For example, undocumented system stored procedures, tricks to do things which are very useful but not documented enough? Answers Thanks to everybody for all the great answers! Stored Procedures sp_msforeachtable: Runs a command with '?' replaced with each table name (v6.5 and up) sp_ms...
As compared to say: REPLICATE(@padchar, @len - LEN(@str)) + @str ...
I have [UserAliases] (UserId, Alias) table with multiple Aliases per user. I need to query it and return all aliases for a given user, the trick is to return them all in one column. Example: UserId/Alias 1/MrX 1/MrY 1/MrA 2/Abc 2/Xyz I want the query result in the following format: UserId/Alias 1/ MrX, MrY, MrA 2/ Abc, Xyz Thank you...
Is it possible to disable a trigger for a batch of commands and then enable it when the batch is done? I'm sure I could drop the trigger and re-add it but I was wondering if there was another way. ...
I would like to do a lookup of tables in my SQL Server 2005 Express database based on table name. In MySQL I would use 'SHOW TABLES LIKE "Datasheet%"', but in T-SQL this throws an error (it tries to look for a SHOW stored procedure and fails). Is this possible, and if so, how? ...
If I need to copy a stored procedure (SP) from one SQL Server to another I right click on the SP in SSMS and select Script Stored Procedure as > CREATE to > New Query Editor Window. I then change the connection by right clicking on that window and selecting Connection > Change Connection... and then selecting the new server and F5 to run...
SQL is not my forte, but I'm working on it - thank you for the replies. I am working on a report that will return the completion percent of services for indiviudals in our contracts. There is a master table "Contracts," each individual Contract can have multiple services from the "services" table, each service has multiple standards fo...
The Database Tuning Advisor is recommending that I create a bunch of statistics in my Database. I'm something of a SQL n00b, so this was the first time I'd ever come across such a creature. The entry in MSDN was a little obtuse - could someone explain what exactly this does, and why it's a good idea? ...
I have a data warehouse containing typical star schemas, and a whole bunch of code which does stuff like this (obviously a lot bigger, but this is illustrative): SELECT cdim.x ,SUM(fact.y) AS y ,dim.z FROM fact INNER JOIN conformed_dim AS cdim ON cdim.cdim_dim_id = fact.cdim_dim_id INNER JOIN nonconformed_dim AS dim ON d...