DBA Stack Exchange Archives - Page 6 of 7 - Andy M Mallon

Nonclustered index storage on clustered columnstore

January 6, 2017 Andy 0

Question In SQL Server, a non-unique nonclustered index on a rowstore table incorporates the base object’s bookmark (RID or clustering key) at all levels of the nonclustered index structure. The bookmark is stored as part of the nonclustered index key at all index levels. On … [Read More]

Does the ‘reads’ column in SQL Profiler mean the same as IOPS?

December 21, 2016 Andy 0

Question I just executed a query that went pretty fast and the SQL Profiler said it used 30.000 reads. Now I’m looking at this page: https://cloud.google.com/compute/docs/disks/performance and see that a standard persistent disk has 3000 reads IOPS. Does that mean that my query would take … [Read More]

How to add 1100 columns in SQL Server

December 4, 2016 Andy 0

Question I need 1100 columns in a single table.I already have 928 columns in same table, now I need to add new 150 columns. I know SQL Server allow maximum 1024 columns. Now I will not create new table. If I create new table and … [Read More]

Is it possible to change the family_guid in MSSQL?

August 24, 2016 Andy 0

Question So I have a bit of a problem with some databases I’m trying to migrate to amazon RDS We have about 20 Databases that are all derivatives of others that all share the same family_guid because they are based on backups of others. The … [Read More]

Why is this explicit cast causing problems only with a Linked Server?

August 17, 2016 Andy 0

Question I am querying data from a linked server through a view on the origin server. The view has to include a couple of standardized columns, such as Created, Modified and Deleted, but in this case the table on the source server doesn’t have any … [Read More]

Does it ever make sense to have a clustered and non clustered index for the same column

August 16, 2016 Andy 0

Question Does it ever make sense to have a clustered and non clustered index for the same column I inherited a database and a web service that goes with it. Looking at the indexes for duplicates I see an two indexes that point at the … [Read More]

Why is sql_text “select @@trancount” in open but sleeping transaction?

August 12, 2016 Andy 0

Question Using SQL Server Management Studio, open a query window and execute In another window execute I see select @@trancount What’s up with this? Who’s counting transactions? Is this a Management Studio thing? asked 2016-08-12 by Michael J Swart Answer I don’t think it’s a … [Read More]

Convert row into concatenated string with column names included

April 6, 2016 Andy 0

Question I have a table that is several hundred columns wide. Is there a way to convert each row into a single concatenated string with the column title included without having to list each single column in the query? I’m doing this because the columns … [Read More]

SQL Server: How to export database with linked servers

April 4, 2016 Andy 0

Question I’m a web developer and I want to export a database to work on it in my local machine. I’m using generate script with data. the script is generated but without the linked servers used by some Views. Is their a way to generate … [Read More]

Reduce MDF file size

April 1, 2016 Andy 0

Question I am working with a 500 GB SQL Server database. I need to reduce the size of the database data file after removing a large amount (50%) of the data. I tried re indexing, it’s good the database has de-fragmented. But the DB size … [Read More]