Tag Info

Hot answers tagged

7

You have a heap. Heaps don't clear out space with DELETEs in most cases. You could truncate the table, or you could put a clustered index on the table. Heaps are great for insert-heavy systems, but not great if there are lots of deletes.


6

You really need to look at the definition of sp_spaceused to find your answer of why unused is zero. exec sp_helptext 'sp_spaceused'; go Take a look at a snippet here of the stored procedure: begin /* ** Now calculate the summary data. * Note that LOB Data and Row-overflow Data are counted as Data Pages. */ SELECT ...


5

You have to break it up into batches because the [Column] does not exist since the statements are being parsed as one batch so it cannot see the new column, hence you receive the error. Once you break it up into batches with GO statement the column will be added to the table and then your next batch updating the new column will be successful. Full example: ...


4

If you do 'NewID' as [field/@id] you will get a field element with an id attribute. On the next line you add B.ID as [field] ot get the value of ID as the node value to the field node you created on the line before. <field id="NewID">1</field> After that you want a new field node and to create that you can use a node without a name that has no ...


4

Restore the backup you have from 15.12.2014 to a new database and then you can delete the rows from your production database that does not exist in your restored backup database. There is no hidden datetime information in the table that you can use.


3

First off, you do not want to use char(50). Use varchar(50) or just text. Read more: Any downsides of using data type “text” for storing strings? Assuming the following rules: Basic slugs never end with a dash. Duplicate slugs are suffixed with a dash and a sequential number (-123). Note that all of the following methods are subject to a race ...


3

Whenever I make a change to a table, does it get written to both the database file AND the transaction log file? It is written to both, but it is written differently to each. The changes are made to the data pages in memory and are eventually flushed to disk via the checkpoint process. the changes are sent to a log buffer and hardened to disk at some ...


2

Yes, it looks like you're using Ola Hallengren's backup scripts which operate in hours, so 14 days * 24 hours = 336 hours. Every time this is run, a full backup of the user databases will be taken and any copies older than 2 weeks will be deleted. Edit: Based on the OP's edit, you are fine to schedule a job executing your first script as a replacement ...


2

Can anyone tell how exactly apply works and how will it effect the performance in very large data APPLY is a correlated join (called a LATERAL JOIN in some products and newer versions of the SQL Standard). Like any logical construction, it has no direct impact on performance. In principle, we should be able to write a query using any logically ...


2

2nd query: Of course you get duplicate rows. 1 row per producto each fabricante is connected to - multiplied with the number of rows in pais each combination is connected to. 1st query: An explicit JOIN binds before (groups of) comma-separated items in the FROM list. This is why you cannot reference fabricante in the JOIN condition between pais and ...


2

To get partition elimination, your queries need to be explicit about the bit data type, for example: ...WHERE Archivable = CONVERT(bit, 0) -- NOT ...WHERE Archivable = 0 Without this, the risk of truncation in the implicit conversion means partition elimination is not applied. The implicit conversion in the CREATE PARTITION FUNCTION statement is not a ...


2

Here is a working FOR XML EXPLICIT example. They are a bit harder to code, but I tend to build them up, section by section so they're not so bad: USE tempdb GO SET NOCOUNT ON GO IF OBJECT_ID('dbo.Borrowers') IS NOT NULL DROP TABLE dbo.Borrowers CREATE TABLE dbo.Borrowers ( ID INT IDENTITY PRIMARY KEY, FirstName ...


2

If your data structure is fixed and out of your control, then the best way to do this is to use a recursive common table expression (CTE) like in this question. If you can change the structure then there are ways to make such queries considerably more efficient. What you have there is often called a "naive tree" - while it allows easy construction and easy ...


2

UPDATE ... SET (a, b, c) = (1, 2, 3) ; Yes, your understanding is correct and this is perfectly legal SQL syntax. As @a_horse_with_no_name mentioned in the comments, you can also use it in conditions (WHERE, HAVING, CASE WHEN, ...). Examples: WHERE (a,b,c) = (1, 2, 3) WHERE (a,b,c) >= (1, 2, 3) WHERE (a,b,c) IN ((1, 2, 3), (1, 1, 1), (2, 2, 2), (4, ...


2

Your index should be on (TrackerId ASC, [Date] DESC) INCLUDE (Position) so that it can easily locate the most recent one for each Tracker. But I really don't like the query from EF. Edit: ...because "most recent" should mean "latest datetime", not "latest identity value"


1

You're looking for a pivot. Either of these queries will work: SqlFiddle /* case */ select CustomerId , FirstName =max(case when [Key]= 'FirstName' then Value end) , LastName =max(case when [Key]= 'SecondName' then Value end) , Age =max(case when [Key]= 'Age' then Value end) , Gender =max(case when [Key]= 'Gender' ...


1

In addition to Mark Iannucci's comments, I'd recommend making ArtistID a field in Files, not Albums, to support soundtracks and other multi-artist collections. You may retain an AlbumArtistID in the Albums table, if you think it would be valuable. If you want to be really thorough, you could consider having several artist fields in Files: ComposerID, ...


1

I recommend using unique keys instead of artist or album names. Doing this will improve the database design because currently a change in an artist's name would require updating each entry in your audio_files table for that particular artist. You and I likely agree it would be strange for an artist to change their name, however they have a penchant for ...


1

"a boolean column stating whether or not a group has any users" Use EXISTS: CREATE VIEW group_info AS SELECT g.name, NOT EXISTS (SELECT 1 FROM users u WHERE u.group = g.id) AS empty FROM groups g; This returns 1 row per group, no matter whether there are users or not - not one row per user like you had, but probably didn't want - so we don't need ...


1

To add single quotes in a string literal, just double it, or use the chr function. E.g. 'hello '' world' represents the string hello ' world. 'hello '||char(39)||' world' is the same. '''' is a string literal representing a single single-quote. To get your fixed end time, just concatenate it. select ''''||to_date(:From_date,'dd-mon-yyyy')||'''' from ...


1

Figured it out SELECT temp1.[file], temp1.[ChangeDate] AS [AddedDate] ,temp1.[OldFolder] ,temp1.[NewFolder] ,(SELECT MIN(temp2.[ChangeDate]) FROM [Changes] AS temp2 WHERE temp2.[OldFolder] = temp1.[NewFolder] AND temp2.[ChangeDate] > temp1.[ChangeDate] AND temp2.[file] = temp1.[file] ) AS [RemovedDate] FROM [Changes] AS temp1


1

I don't think that this completely satisfies your request, but it is a start. If you want further ordering, I think you need to go into more detail with the sorting rules. Also, the example data from your fiddle has "five" with a created date older than "four". SqlFiddle select * from table1 order by coalesce(position,2147483647) , created; ...


1

Apparently, creation of CDATA nodes is possible only in the FOR XML EXPLICIT mode. Moreover, Microsoft implementation of XML does not respect them, in accordance with W3C recommendations. Here is a link: How to get [CDATA] with FOR XML PATH? So, if you truly need this, you'll have to write an ugly monster, and don't even think about the TYPE option - it ...


1

You should be able to use SQL Server's proprietary and admittedly awkward self-reference form of delete: DELETE RemoteTable FROM OPENQUERY(...) AS RemoteTable ...


1

Check for DBCC OPENTRAN for any transaction on these tables. If NOT then you are free to KILL these sessions as these are only select statements. Find the sessions by querying sp_who2 and kill by syntax KILL :-)


1

I recommend using a Calendar table or Date Dimension (whichever name you prefer). Here is an answer with using a quick CTE. /* Date Range CTE */ -- Updated based on @AaronBertrand's articles linked in the comments -- Basically ends up being the same query as the last half of @AaronBertrand's post declare @FromDate date; declare @ThruDate date; set ...


1

Well, you should have a Numbers table or a Calendar table. I'll start with a Numbers table: CREATE TABLE dbo.Numbers(Number INT PRIMARY KEY CLUSTERED); INSERT dbo.Numbers WITH (TABLOCKX) (Number) SELECT TOP (1000000) Number = ROW_NUMBER() OVER (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2; Then: DECLARE ...


1

Unless you have cross-database ownership chaining enabled (off by default) and have the same User in both databases, then giving access to an object in one database does not imply anything permission-wise to other databases. Of course, I am not recommending that cross-database ownership chaining be enabled, and neither is Microsoft. Instead, you should ...


1

I'd say that this is pretty standard stuff ; Create table employees (id int primary key, name varchar(100) -- Plus some additional columns ) Create table responsibilities (id int primary key, description varchar(100) ...


1

If you are mostly loading the data, and seldom querying it, then no need to split the table. Any "home-grown" attempt at splitting the table is bound to cause grief; use Oracle's (extra-cost) partitioning option if your queries are usually date-ranged; but be careful; partitioning does not necessarily improve performance. It can if the queries include ...



Only top voted, non community-wiki answers of a minimum length are eligible