Can't delete a login due to db ownership — but no dbs are owned by this login I have a SQL Server 2014 instance that's installed on an OS that I've migrated from one domain to another. The new domain and user account are from separate forests, but they bear the same descriptive names (e.g. DOMAIN\User).

Naturally, I can't log into the database with the old domain account, so I need to delete that. The trouble is that I can't.

I keep getting the error message that the login owns one or more databases. But it's very clear that it doesn't—I've cleared mappings and reassigned dbo in all databases.

Yet the error persists. This is very odd.

Is there a way to determine what's preventing me from deleting this login so I can proceed to add the same-named account from the new same-named domain?

--EDIT--

Correction: I'd reassigned dbo in all databases that displayed a login in the Login name field. That field was empty for the two RS databases, incorrectly leading me to believe that they weren't owned by the login that I was trying to delete. The script provided by @hot2use did the trick.

You should check the ownwers of the databases with the following script:

SELECT d.name, sl.name
    FROM sys.databases as d JOIN sys.syslogins AS sl
        ON d.owner_sid = sl.[sid]

You might then find out that a database is linked to on of your Windows/AD accounts.

There is a slight difference between a SQL Login (native or Windows account) having the role db_owner and an account being the database owner.

If you do find a database with an account linked to the Windows/AD account you want to get rid of, then execute the following command while connected to that database:

USE <databasename>
GO
sp_changedbowner '<new_account>'

  IMHO: Changing the owner to 'sa' is a viable option.

Good luck.


DB2 database -> does it continue to be platform-specific? (iSeries, LUW, z/OS) Wikipedia says DB2 is no longer Platform-specific:

  Historically and unlike other database vendors, IBM produced a platform-specific DB2 product for each of its major operating systems. However, in the 1990s IBM changed track and produced a DB2 "common server" product, designed with a common code base to run on different platforms.

If this is the case, why are there different drivers to connect to DB2 for iSeries and DB2 for Linux, UNIX and Windows? (I'm not sure if DB2 for z/OS has its own driver as well.)

I don't think your assumption about "different drivers to connect to DB2 for iSeries and DB2 for Linux, UNIX and Windows" is correct. There is a single IBM Data Server Client that is used to connect to DB2 for LUW, DB2 for System i, DB2 for z/OS, and Informix servers.

However, what you quote seems to reference DB2 server software, not drivers, and there are still different versions of DB2 servers for LUW, System i, and z/OS. They used to have quite different SQL dialects, but these differences are being reduced.


How to Create Live connection between Amazon Redshift and Amazon RDS (MySQL) I have a transaction table in MySQL which get updated almost every second. The table has about 20 million records and counting. I have been running queries directly on this table in my app development which has been having serious impact on performance especially when I have to call some queries using aggregate functions.

I want to connect the MYSQL to Amazon Redshift.

The problem is I want my connection to Redshift to be live. ie. Get Redshift updated with every insert in my tales in mysql.

How do I create the live connection to Amazon Redshift?
Is there any other way I can manage data without using Amazon Redshift?

In order to create a live connection from MySQL to Redshift you need to choose an appropriate database replication method. Among all existing methods, the Change Data Capture (CDC) is the most suitable for your case.

In this method, after a dump of the initial state of the database, the log of each database operation is continuously streamed and loaded into the data warehouse, and consolidated on a schedule (configurable) and the biggest advantage is that it enables near real-time replication.

A tool that I had a good experience with is Alooma, it can support near real time connection between Amazon Redshift and MySQL data base.


Must numeric JSON keys be quoted? I've noted that key-value values may be unquoted when numeric in TSQL JSON strings, but it seems the key component must always be quoted.

select 1, isjson(''), 'empty string' union
select 2, isjson('{}'), 'empty braces' union
select 3, isjson('{1:2}'), 'unquoted both, numerals both' union
select 4, isjson('{1:"2"}'), 'unquoted key, numerals both' union
select 5, isjson('{"1":2}'), 'unquoted value, numerals both' union
select 6, isjson('{"1":"2"}'), 'quoted both, numerals both' union
select 7, isjson('{a:b}'), 'unquoted both, alpha both' union
select 8, isjson('{a:"b"}'), 'unquoted key, alpha both' union
select 9, isjson('{"a":b}'), 'unquoted value, alpha both' union
select 10, isjson('{"a":"b"}'), 'quoted both, alpha both'
order by 1
;

Results:

1   0   empty string
2   1   empty braces
3   0   unquoted both, numerals both
4   0   unquoted key, numerals both
5   1   unquoted value, numerals both
6   1   quoted both, numerals both
7   0   unquoted both, alpha both
8   0   unquoted key, alpha both
9   0   unquoted value, alpha both
10  1   quoted both, alpha both

The above demonstrates this but my questions are:

Must this always be so? (Is there a configuration that can override this behaviour?)
Is this behaviour specified by JSON or SQL Server?
What is the rationale behind this design decision?
Would there be a performance benefit if SQL Server automatically cast unquoted numerals as integers?

The JSON notation definition follows the following schema:

The definition of the string is the following:

You can see that the quotes are mandatory both at the beginning and at the end.

The definition of the value is the following:

Note that here you can either supply a string or a number, the number being:

Conclusions:

Keys must have quotes both at the beginning and at the end.
Quotes can be avoided on the value end if you are supplying numbers.

I can't answer why JSON adopted this particular schema and an response here might be opinion based.

SQL Server will always get a performance increases when treating integers over string data types (like VARCHAR or NVARCHAR) since they are faster to operate with and compare, but make sure the data type is actually a numeric type and not a number stored as a string.


Redo log size results into performance degrade We are continuously doing heavy insert and update query, but after 10 hours data base is returning answer late in minutes normally it takes around 1 ms. I observed that "free buffer wait" and "redo log check point complete" is taking most of the time.

Database have redolog size of 512 MB and 4 group defined and each have four files. So do we need to add more size or DBWR or LGWR thread has throughput issue (How I can identify), or hard limittation.

Thanks

"Free buffer wait" event usually means the buffer cache is not big enough. Increasing the size of the SGA will alleviate this wait event by allowing more changes to happen before all buffers are dirty.

"Redo log check point complete" wait event happens when a block that has been changed in the buffer cache has not yet been written to the disk. The online redo log that contains this change cannot be archived until this has happened.  Adding extra log files or increasing the size of the online redo logs will alleviate this wait event in the same way as increasing the size of the SGA - more changes can be made before this error is encountered.

However, both of these events have the same root cause - too many dirty blocks in the buffer cache. This is caused by poor DBWR performance as you suggest above. Try adding extra DBWR processes. If this doesn't help then you probably have an I/O problem.


SSMS Tools Pack Alternative Are there any tools out there comparable to SSMS Tools Pack 2012? The licensing ($30 per machine, $100 for any number of machines... for 3 months) leaves a lot to be desired and I wasn't sure of what other options are available.

For example, one thing that I really miss is the "Save every query you run". When tinkering and researching, it's been invaluable to have a running backup of different versions of queries as I run them. Or when I realize I don't have a backup of a query I was working on 2 months ago.

Clarification: SQL Server Management Studio's have no official addon support, but there are some tools out there. SSMS Tools Pack is one that I've been very fond of (2005, 2008 versions), but the licensing fee for 2012 is horrible. (I would pay for a reasonable license, but that's not the question here.)

I've found SSMS Boost for example that has some cool additions to SSMS that seem worth it.

What other addins are available for SQL Server 2012? I'd love to have something that saves a query when I hit F5 like SSMS Tools Pack does, or anything out there besides the two tools listed?

Some more info from SSMSBoost developer.
As soon as you mentioned my project, I allow myself to write some words about the project. The main reason I have written the tool was that I have missed productivity features, concerning T-SQL Development and DBA - oriented tasks.
For example:

frequent connection switching between different servers
writing 1000 times a day "select * from "...
export grid results to excel/xml
keeping a list of favorite connections
having a warning from SSMS when connecting to live-environment

all these and some other daily operations are adressed in SSMSBoost and have been constantly improved. Currently I release new version every 30-40 days. Over last 3 versions I have also got a lot of positive/constructive user feedback and lot of features were improved. Your proposal to save every executed query will be also soon implemented. As I wrote - if you want to have something special write me an e-mail (support@ssmsboost.com)


Slony Replication: Will data eventually be 100% replicated if daemon dies for long period? I am using Slony to replicate a Postgres database.

If my slony daemon exits for whatever reason, and doesn't start up hypothetically until 1 month later, will all the data that has accumulated in the master be replicated eventually to the slave? Of course, it might take a long time for that to happen, but will any data be lost as a result?

This is a fairly expert-level question and I could be wrong, but I think you are going to have a lot of issues in the scenario you suggest.

The basic issue is that while Slony is designed to handle periods of inactivity, you have a lot of stuff that is going to be queued for replication.  If you make any DDL queries in the mean time, those can't get queued, and slonyk will not work to replicate them, so you will have a significant number of problems here.

My recommendation is not to use Slony for things where you expect long periods of inactivity (say, more than a couple of days).  The limitations you will run into keeping this running are significant.

So yes, your data will eventually get replicated, maybe....  It depends on whether other problems surface in the mean time.  I wouldn't recommend it in that case.


SQL Server 2012 Transactional Replication Latency Warning Not Working I have transactional replication setup on SQL Server 2012.  I have the default alerts setup and they're working with the exception of the alert for latency.  The alert for latency only fires when I have Replication Monitor open.  Is this by design?

I discovered the job "Replication monitoring refresher for distribution" was disabled on the distributor.  I enabled the job and started it.  The alert appears to working correctly.


Should I set a variable in the same line in which it was declared? I frequently see the syntax

Declare @var int;
Set @var = 1;

But you can set the variable at declaration time

Declare @var int = 1;

Is there a gotcha to the latter option? Am I missing something that is internal or doesn't work in all cases where you should not declare and set the variable in the declaration?

I ask because of subtitle gotcha's that I hear which I would not expect. Like Aaron Bertrand lower casing data types which is something that msdn also does not state but has been found in the community.

It was not possible until SQL Server 2008. Before this version you should declare the variable, and then set its value.

According MS DOCS:

  THIS TOPIC APPLIES TO:
    SQL Server (starting with 2008)
    Azure SQL Database
    Azure SQL Data Warehouse
    Parallel Data Warehouse


SQL Server: Can you join implicitly using key relationships? If I have two tables;

Table A
id | etc
1  | etc
2  | etc
etc

And

Table B
id | A_id | etc
1  | 2    | etc
2  | 2    | etc
3  | 1    | etc
etc

The relationship between Table A and Table B is defined in the database as a foreign key with cascading delete. Do I still have to explicitly declare join columns between these tables? Several ORMs that I have previously worked with do not require this (for example the PHP Silverlight ORM), and I was thinking that if you requested unambiguous column names, why not? It seems to me that defining FK relationships should automatically define which columns to join a table one

For example, I want to know if I can write this query:

SELECT * FROM [Table B] B LEFT OUTER JOIN [Table A] A ON B.A_id = A.id

As

SELECT * FROM [Table B] B LEFT OUTER JOIN [Table A] A

You always have to specify the columns you are joining on, regardless of any foreign key relationships.

Why? Because otherwise you couldn't do a full outer join if a foreign key existed between the tables, which would lead to identical queries producing different results for non-obvious reasons.

Also, what if you explicitly join on a different field? Would SQL behave as if you had included both fields in the join statement? Would you have to somehow tell it to ignore the foreign-key relationship for this one query?

FYI, the term "implicit join" in SQL actually refers to something entirely different, a SELECT statement made using a comma instead of a JOIN keyword:

 SELECT *
 FROM table_A, table_B
 WHERE table_A.key = table_B.key

vs an "Explicit" join:

 SELECT *
 FROM table_A JOIN table_B
 ON table_A.key = table_B.key

(This "old style" of implicit joins still works, but is not recommended.)


Conditional Connection in SSIS based on Availability Group status I'm working on an SSIS package that will refresh dev databases from production backups. The package has been in place for years, but we're deploying Availability Groups on the dev instance, so the tasks have to be changed to work with both the primary and secondary replicas.
Background: All instances are SQL Server 2014 SP2. One production instance (PD1), two dev instances (DV3 & DV7) and one utility instance (DV1), where the SSIS package is deployed to SSISDB and where the SQL Agent job runs.
In the SSIS package, after the full backups are obtained and put on a network share from production, the next set of tasks (in a separate SSIS package) needs to figure out which node hosts the primary replica. I'm following the steps listed here to go about this work, and everything works great if it's hard-coded (which, of course won't work if/when a failover occurs).
The only way I've been able to think about approaching this is, at runtime, evaluate which instance is primary and then proceed with the tasks (which, by the way are all "Execute SQL Task" objects--requiring their own connection definition). I've seen a few examples of how to use expressions in ConnectionStrings and variables, but can't figure out how to set the value returned from the below query in SQL Server to return the correct instance to set the connection string in the package.

    select cs.replica_server_name
from sys.dm_hadr_availability_replica_states rs
join sys.dm_hadr_availability_replica_cluster_states cs
    on rs.replica_id = cs.replica_id
join sys.dm_hadr_name_id_map n
    on rs.group_id = n.ag_id
where rs.role = 2
and n.ag_name = 'DVAG001'

Ideally, there would be two of these connection managers, one for the primary and one for the secondary, which I could then use in the "Execute SQL Task" objects. Then everything would work perfectly.
There very well may be other ways to do this, but I'm not very experienced in SSIS or development outside of TSQL.

UPDATE: The above process is done every day...which is why I'm using an SSIS package and SQL Agent. So, the whole process has to be automated and have all the values necessary--especially the determination of the replica states (primary and secondary)--inside the package so that no manual intervention is required.

Let's assume that you have an availability group with only one secondary replica (you could easily expand this sample for more replicas)

1. Connections

You will need 3 connections:

AG listener (to run the query that will return which server is holding the primary an secondary)
Primary (to be parameterized with the primary server)
Secondary (to be parameterized with the secondary server)

2. Variables

Create 2 variables with data type String

ServernamePrimary
ServernameSecondary

3. Query

To map a query result set to a variable using Execute SQL Task, your query needs to return only one row. You will map each column to the variable that you wish. Knowing that, I've changed you query to the following:

WITH CTE_AGStatus as (
    select cs.replica_server_name, rs.role
    from sys.dm_hadr_availability_replica_states rs
    join sys.dm_hadr_availability_replica_cluster_states cs
        on rs.replica_id = cs.replica_id
    join sys.dm_hadr_name_id_map n
        on rs.group_id = n.ag_id
    where n.ag_name = 'DVAG001'
)

select  ServernamePrimary = MAX(case when role = 1 then replica_server_name end)
,       ServernameSecondary = MAX(case when role = 2 then replica_server_name end)
from CTE_AGStatus

(Feel free to change it as you wish, just remember to return only one row)

4. Mapping the result set to variables

On the execute SQL Task Editor, paste the query above, set the OLEDB connection 'AG listener' and change the result set to 'Single row'

Then go to the 'Result Set' tab and map the columns to the variables

5. Change the connection strings

Click on the 'Primary' connection manager and go to the 'Properties' tab, then click on 'Expressions', select the property 'ServerName' and add your variable on the Expression.

Repeat the process to the 'Secondary' connection manager

6. Check the process

To validate, you could add other steps and use breakpoints to check how your variables are running. The next steps are up to you. Now you have one connection pointing to each server of your AG.


How to concat one column of multiple rows (group by clause used in query) I had some issue while populating some data as per my requirements and that is as follows..

Table data:

---------------------------------------------
EMPID  PROJ_TYPE  HOURS  ST_DATE    ED_DATE
---------------------------------------------
1      DEV        10     2013-1-1   2013-5-1
1      DEV        34     2013-5-2   2013-8-1
1      DEV        23     2013-8-2   2013-10-1
1      TEST       12     2014-1-1   2014-3-1
1      TEST       25     2014-3-2   2014-6-1

Here I want to fetch the employee data with hours, So as simple I wrote

SELECT EMPID,
       PROJ_TYPE,
       SUM(CASE WHEN PROJ_TYPE='DEV' THEN HOURS ELSE 0 END) DEV,
       SUM(CASE WHEN PROJ_TYPE='TEST' THEN HOURS ELSE 0 END) TEST
FROM TABLE1
GROUP BY EMPID, PROJ_TYPE

Hence it is returning two rows as expected but here the twist I want only one row per employee. If employee have multiple PROJ_TYPE then it should combine both project values and return only one row.

Finally my result set should look like:

----------------------------
EMPID  PROJ     DEV  TEST
----------------------------
1     DEV/TEST  67   37

Hope I am clear please comment if any doubt on my question.


Passing in multiple instances into the dbatools command GET-DBADBFILE I am attempting to gather all of the dbfile information on all of our sql instances in one of our environments. It is a fairly large environment and would be tedious to perform one instance at a time. I thought there would be an easy way to do this with Powershell but I seem to be running into a roadblock.
I have a list of instances stored in a table/view [vSQLInstances]. If I pass those values into a variable and then use that variable as the SQLInstance parameter, I get an error.
Script I'm using...
$sqlinstance = Invoke-DbaQuery -SqlInstance dbasql01\sql2019 -Database DBAEstate -Query "select sqlinstance from vSQLInstance;"

Get-DbadbFile -SqlInstance $sqlinstance | Select-Object sqlinstance, database, logicalname, size

The error I receive:
Get-DbaDbFile : Cannot process argument transformation on parameter 'SqlInstance'. Cannot convert value
"@{sqlinstance=PRODSQLD\D}" to type "Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter". Error: "Failed to interpret
input as Instance: @{sqlinstance=PRODSQLD\D}"
At line:11 char:28
+ Get-DbaDbFile -SqlInstance $instancelist
+                            ~~~~~~~~~~~~~
+ CategoryInfo          : InvalidData: (:) [Get-DbaDbFile], ParameterBindingArgumentTransformationException
+ FullyQualifiedErrorId : ParameterArgumentTransformationError,Get-DbaDbFile

Yes, that is expected because you are passing in a DataRow type. Instead you could pass it as a string type $sqlinstance.sqlinstance (the column name) as a parameter for Get-DbaDbFile to get the results.
Get-DbaDbFile -SqlInstance $sqlinstance.sqlinstance #...

For example...


How many requeries/second can my server handle? I have a dedicated server and my provider doesn't want to tell me how many requeries/second MySQL can handle.

Is this just in relation with MySQL or also with other things?

Operating system: CentOS 6.5 with Parallels Plesk 12 (64-bit)
server: UNIX socket
version: 5.1.73

I can provide more info if asked, thank you.

It answer is - It depends on many many many factors. How many cubes can you fit in a box is a similar question in a non technical sense.

What are the queries, What are the specs of the server, is it dedicated mysql or does your webserver go on there too, Is it overclocked any ? Whats the temperature ? Do you cache querys ?

The list goes on. The best answer is to just test it with your set up and a similar set of queries and see. We can not give a better answer


Mysql replication problem after export/import on already working replication I have 2 databases in master - slave replication.

I had some errors in the replication due to inserts in the slave, so I corrected it and executed:

stop slave;
set global sql_slave_skip_counter=2;
start slave;

After it, I saw that my change wasn't correct so I decided to export the database and import it to the slave.

Now the show slave status seems ok:

 Slave_IO_State: Waiting for master to send event
                  Master_Host: ip
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 7218177
               Relay_Log_File: slave-relay-bin.009595
                Relay_Log_Pos: 252
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 7218177
              Relay_Log_Space: 553
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
 Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:

But if I change anything on the master I don't see the change in the slave.
Any suggestions of what can be wrong?

I would check the export process.

If you use mysqldump, create this script on the Slave and execute it on the Slave:

#
# Dump the Master
# Use the coordinates of the Master at the point the mysqldump starts
#
DUMPFILE=/path/to/dumpfile.sql
MYSQL_USER=masterroot
MYSQL_PASS=masterrootpassword
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"
SQL="SELECT GROUP_CONCAT(schema_name) FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL}" | sed 's/,/ /g'`
echo "STOP SLAVE;" > ${DUMPFILE}
MYSQLDUMP_OPTIONS="--master_data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --add-drop-database"
mysqldump -h ipaddrofmaster ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} >> ${DUMPFILE}
echo "START SLAVE;" >> ${DUMPFILE}
#
# Load the Slave
#
MYSQL_USER=slaveroot
MYSQL_PASS=slaverootpassword
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"
mysql -h localhost ${MYSQL_CONN} < ${DUMPFILE}

This script will move all user-defined database. It will not import the mysql schema from the Master. This is designed so that the Master can be from an earlier version of MySQL than the Slave and will still import the data. All grants should be left in tact.

You need to make sure of the following:

the Master and Slave are using the same binlog_format (Being Cautious)
the Master and Slave are using two different values for server_id

If Slave_IO_Running is Yes, Slave_SQL_Running is Yes, you are definitely writing on the Master, and the Relay_Log_Space is not moving, then one of the following might be happening

the Slave ran out of diskspace because MySQL will freeze solid with no diskspace
The I/O has silently stopped heartbeating (Derek Downey mentioned this in hist old post Can we capture only Slave_IO_Running in SHOW SLAVE STATUS in MySQL)


Redis remove slave from replication along with sentinel I have a 3-nodes Redis replication and a 3-nodes Sentinel. I want to remove one slave.

I tried stop slave and remove slaveof 10.128.130.139 6379 from configuration. But it became slave again after I started it. And slaveof was back again in config.

After reading SLAVEOF, I tried invoking SLAVEOF NO ONE. But it became slave again after 1 min.

I guess Sentinel reconfig it to slave. How can I remove one slave in my case where Sentinel exists?

I should read sentinel doc carefully. It's mentioned in sections Adding or removing Sentinels and Removing the old master or unreachable slaves.

These are steps:

Shut down the redis slave to be removed.
Remove slaveof statement from conf

# Generated by CONFIG REWRITE
slaveof 10.128.130.139 6379

Send a SENTINEL RESET mastername command to all  Sentinel instances. One after the other, waiting at least 30 seconds between instances.

Now the redis slave is standalone, and sentinels forget this slave.


How to transfer table in SQL Server 2008 from one computer to another I want to transfer my table in SQL Server 2008 from one computer to another... Is it possible?

How to transfer from one computer to another? I am doing my final year project. In my project, if the table contains more number of rows means it will divide the table into smaller partition and transfer that partition to another computer.. is it possible to transfer?

There are many ways, you could:

1) Dettach the database and copy the database files (mdf and ldf) over to the other computer then attach it.

2) Backup the database to a .bak file, copy that over and restore on the other computer.

3) Use the Import Export wizard to move just the table into a new database on the other computer.

4) If you need to do this regularly then maybe you could write an SSIS package to do it.

The easiest way is option 1.


If a rowstore index is stored in a b-tree data structure, what kind of data structure is used for a columnstore index? My understanding of how regular rowstore indexes are stored is in a type of b-tree data structure, but I was wondering because of the different nature of columnstore indexes, what type of underlying data structure is used?

We usually think of an index as a way of rapidly finding all references to a given piece of information. In a textbook given a topic we can find all pages that mention that topic. In a database given a column value we can find all rows that have that value.

Calling a columnstore an "index" in this sense is a bit of a misnomer. It is not intended to provide fast lookup for a specific column value's rows. Rather it is intended to quickly provide results of aggregates over large datasets. As such it is a data format in itself. The columnar data store, sometimes called the decompositional storage model (DSM), is well-known and venerable. Many proprietary and open-source DBMS offer it.

The basic idea of a column store is that values for a column are stored contiguously on disk. Then an aggregate on that column can efficiently pull that column, and only that column, from disk reducing the number of disk blocks that must be read. Further, since all the data values in a block are from the same domain compression on that block is likely to be very efficient, further reducing the number of blocks required. Compression can be much better than a generic ZIP - dictionaries, run-length and delta encodings can be used.

For various reasons it is better to break up rows into large groups before compressing and storing them. SQL Server calls these groups "row groups", which are made up of "column segments". Each row group holds just over one million rows. When the segments are constructed the server extracts some metadata such as min, max and (perhaps) sum for that column. This metadata is sometimes called zonemaps. At run time these zonemaps can be compared to the query predicates and, if the predicate does not lie between the segment's min and max values, processing can entirely avoid reading those blocks from disk. This is called segment elimination and is analogous to partition elimination. Moreover some queries can be answered directly from the zonemaps e.g. "select min(column) from table" can be answered by comparing the "min" segment summary values from the zonemaps.

I would note that B-Trees are not the only structure for indexing rowstores, it's just it is the only one available to database developers using SQL Server.


Replication members count limitation in MongoDB Is it possible to configure only one secondary and one primary in MongoDB Replication?

Yes! Of course!
But in the situation where you lose one of the nodes (primary or secondary), remaining node state will be secondary and you cannot insert/update/delete data.
If you can add (one) arbiter to that replica set, your DB will keep working even you lose one node.
Basically arbiter don't need many resources, smallest VPS node what you can find is enough. (512MB memory, 1 CPU, 10GB diskspace) OR you can even add that arbiter to that application machine what is connecting to that mongoDB.


Help me choose a RAID level combination for a SQL Server 2008 instance I am going to rebuild one IBM 3400 server from scratch.  This server is dedicated to a SQL Server 2008 instance running on Windows 2008 R2.

I am going to make new RAID configuration.  I have 6 SCSI 73 GB drives inside the machine and an IBM ServerRAID 8K controler.  What would be a good way to set the RAID levels? Should I have two, three or one field on my controler?

I am considering to make one of following solutions:

Use all the disk and make a RAID 10 pool.
Use 4 disks for a RAID 1e pool and use it to store the database data and OS, and use the other 2 disks in a RAID 0 pool and use that to store the database logs.
Some other combination.

Is a larger stripe unit size better?

This server will be a subscriber to a replicated database.  Its primary task is going to be reporting and data retrieval, with only the replication agent making writes. The size of the database is around 90 GB.

I vote for option 1. Bear in mind that RAID 0 means "no protection" - do your logs matter? (yes they do).

It also has the benefit of simplicity

The SQL Server docs say:

  For optimized I/O parallelism, use 64 KB or 256 KB stripe size.

But it is usually good to go with the controller default IMO


How do I exclude unchanged fields in Flashback query resultset? Is it possible to exclude unchanged fields in Flashback query resultset?
Consider I have following table
create table first_table
(
    id int generated as identity,
    name NVARCHAR2(1024),
    age smallint,
    notebook nclob,
    userpic clob,
    salary float
)

If the table has very frequent updates (e.g. on notebook field) following versioned query
select ROWID, VERSIONS_OPERATION, VERSIONS_STARTSCN, VERSIONS_STARTTIME, VERSIONS_XID, id, name, age, notebook, userpic, salary
from FIRST_TABLE versions between scn 1469193 and 1482882;

will pull heavy userpic value for every row even though it's the same.
Can I somehow avoid that and instead get NULLs for unchanged values ?

Is there a way to excluded unchanged fields from versioned response?

Not really.
Flashback uses the information found in UNDO. Flashback Data Archive stores the results from the Log files in a table.
So, you get ALL of the data when you do a Flashback query.
The common method is to use LAG + DECODE.  But, I'm afraid that this will problematic for your LOB columns.
Personally, I'd pawn off the problem to the UI (Display Tier).


Atypical problem with ORA-01445 I have problem with following query Q1:

SELECT ADDRESS.STREET
     FROM  MYSALES.MYADDRESS ADDRESS
 INNER JOIN MYSALES.MYCOUNTRY MCOUNTRY ON MCOUNTRY.MYCOUNTRY_ID = ADDRESS.MYCOUNTRY_ID
 INNER JOIN MYGLOBAL.COUNTRY COUNTRY ON COUNTRY.COUNTRY_ID = MCOUNTRY.COUNTRY_ID

When we translate this to good old notation, Q2:

SELECT ADDRESS.STREET
FROM MYSALES.MYADDRESS ADDRESS
    ,MYSALES.MYCOUNTRY MCOUNTRY
    ,MYGLOBAL.COUNTRY COUNTRY
WHERE
    MCOUNTRY.MYCOUNTRY_ID = ADDRESS.MYCOUNTRY_ID
    AND COUNTRY.COUNTRY_ID = MCOUNTRY.COUNTRY_ID

The problems start when I wrap in view:

SELECT * FROM (Q1) or SELECT * FROM (Q2) results with error:

  ORA-01445: cannot select ROWID from, or sample, a join view without a
  key-preserved table
  01445. 00000 -  "cannot select ROWID from, or sample, a join view without a key-preserved table"

But for query Q3:

 SELECT * FROM  MYSALES.MYCOUNTRY MCOUNTRY
     INNER JOIN MYGLOBAL.COUNTRY COUNTRY ON COUNTRY.COUNTRY_ID = MCOUNTRY.COUNTRY_ID

Result of SELECT * FROM (Q3) is correct.

So the problem is in Q4:

  SELECT * FROM MYSALES.MYADDRESS ADDRESS
    INNER JOIN MYSALES.MYCOUNTRY MCOUNTRY ON MCOUNTRY.MYCOUNTRY_ID = ADDRESS.MYCOUNTRY_ID

When I wrap the last query in a view, it fails, with the above error.

The columns that end with _ID, are should be  primary or references key.

Could you please guide me a hint where should I look for solution ?

The solution of the problem is that table MCOUNTRY did not have the primary key. It was only configured with unique constraint that was called is the same way that primary keys. Even when the unique column was used in query Oracle could not provide expected result.

Something obvious but still lesson to learn, unique constraint is not the same as primary key where it comes to joins.

Have a nice day.


Couldn't install SQL Server 2012 on machine with Windows 7 SP1, VS 2010 SP1 I am trying to install SQL Server 2012 RTM on my pc, I have installed Windows 7 SP1, VS 2010 SP1 but it again and again giving this error:

  A network error occurred while attempting to read from the file:
  D:\Microsoft SQL Server 2012
   RTM\1033_ENU_LP\x64\setup\x64\sqlncli1.msi
  Error help link:
  http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.2100.60&EvtType=sqlncli.msi%40PublishProduct%401316

When I go to the Microsoft link I find

  We're sorry
  There is no additional information about this issue in the
  Error and Event Log Messages or Knowledge Base databases at this time.
  You can use the links in the Support area to determine whether any
  additional information might be available elsewhere.

How can resolve this, at first i thought adding current user in Display Replay controller making the problem, then uninstalled everything and again tried installing but couldn't succeed. Can anyone suggest what may be causing these problem and possible resolve for it.
I have also SQL Server 2008 R2 installed which I want to keep simultaneously with 2012

Additional Information's:

Its on local drive i.e on my harddisk, so there should be no error and Its the RTM version of SQL 2012.
I find below logs on C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\

Feature: SQL Client Connectivity SDK Status: Failed: see logs for details Reason for failure: An error occurred during the setup process of the feature. Next Step: Use the following information to resolve the error, and then try the setup process again. Component name: SQL Server Native Client Access Component
Component error code: 1316 Component log file: C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20121022_164723\sqlncli_Cpu64_1.log Error description: A network error occurred while attempting to read from the file: D:\Microsoft SQL Server 2012 RTM\1033_ENU_LP\x64\setup\x64\sqlncli1.msi Error help link: go.microsoft.com/…

This problem not yet solved, although other people installed SQL2012 with this installer on their PC's. I think something wrong with my PC or some settings i am using.
Please can anyone help me out?

Here's the troubleshooting options I'd try:

Disable antivirus. Man, I hate saying this, but I've seen issues where antivirus took too long to scan a file, and it caused installation problems.
Re-download the installation media. I know you've seen other users run this same setup and it's worked, but I've seen enough corrupt downloads that I would try this anyway. It doesn't cost you anything.
If D is a network share (a mapped drive), copy the installation files locally before you start.


"Replicate" sql structure between testing and staging on same database instance On my local development server I have a database set up which gets hooked into Entity-Framework and is then used for unit testing. Let's call it COMPANY\Database

On the same server (and same database instance) I then have another database - COMPANY\DatabaseStaging which has the exact same structure but persistent test data used for constructing a front end site around.

So, to hopefully clarify, the structure/flow is something like:

Create or modify a table:

  COMPANY\Database.Product

Update entity framework, write some business logic, unit tests, etc against the table.

Manually mirror/replicate the changes:

  COMPANY\DatabaseStaging.Product

Put in some slightly more persistent test data and do front end/ASP.NET stuff.

Now, this doesn't seem very elegant to be manually updating the COMPANY\DatabaseStaging database to make it reflect changes in COMPANY\Database. Is there a better way of doing it?

There are many free tools - native and opensource available for comparing schema and data between 2 databases (on same or different servers):

Open DBDiff ==> This is on Codeplex and works great.
SQL Admin Studio ==> This is now a free tool.
Hidden Gem from SQL 2005 and up :  tablediff.exe (you can find this in the COM directory of your SQL Server install folder) Compare schemas: Regular or Strict
Powershell - You have to write your own code or build up on existing ones
If you are using SQL Server 2012, SSDT is also an option.
I have not tried this one, but just to put it in place
SqlDiffFramework

From my experience, its worth to invest in Redgate's Schema and data compare tools as they have commandline options and they integrate well with Powershell as well.


Get all database, table and column names and column definitions I need to check the data is identical between SQL Server.

For that I need following details. Let me know if this is possible.

All Database Names
table Names
column Names
Column Max Length in a table

╔══════════╦══════════╦══════════════╦══════╗
║ Database ║  Table   ║    column    ║ Size ║
╠══════════╬══════════╬══════════════╬══════╣
║ IBM      ║ Employee ║ EmployeeId   ║    5 ║
║ IBM      ║ Employee ║ EmployeeName ║   49 ║
║ IBM      ║ Project  ║ ProjectID    ║    4 ║
║ IBM      ║ Project  ║ ProjectName  ║  110 ║
║ TCS      ║ Employee ║ EmployeeId   ║    6 ║
║ TCS      ║ Employee ║ EmployeeName ║   51 ║
║ TCS      ║ Project  ║ ProjectID    ║    5 ║
║ TCS      ║ Project  ║ ProjectName  ║  226 ║
╚══════════╩══════════╩══════════════╩══════╝

I have an script that I used to use.
this is to compare 2 databases, in the example I am comparing bocss2 with tablebackups.

Now I only use the visual studio schema comparison tool as suggested in the comments.

It is not even properly formatted, it is not a complete, ready, free from bugs solution, but it would at least give you some ideas.

I still would like to suggest you use the visual studio community edition

--sp_CompareDb @SourceDB='Bocss2',@TargetDb='Tablebackups'

----DROP PROCEDURE sp_CompareDb

--USE MASTER
--GO

--CREATE PROC sp_CompareDb(
--                            @SourceDB SYSNAME,
--                            @TargetDb SYSNAME
--                            )
--AS

/*
DECLARE @SourceDB SYSNAME='DB1',
                  @TargetDb SYSNAME='DB2'
*/

DECLARE @SourceDB SYSNAME='Bocss2',
        @TargetDb SYSNAME='Tablebackups'

  SET nocount ON
  SET ansi_warnings ON
  SET ansi_nulls ON

  DECLARE @sqlStr VARCHAR(8000)
  SET @SourceDB= Rtrim(Ltrim(@SourceDB))
  IF DB_ID(@SourceDB) IS NULL
  BEGIN
        PRINT 'Error: Unable to find the database '+ @SourceDB +'!!!'
        RETURN
  END

  SET @TargetDb= Rtrim(Ltrim(@TargetDb))
  IF DB_ID(@TargetDb) IS NULL
  BEGIN
        PRINT 'Error: Unable to find the database '+ @TargetDb +'!!!'
        RETURN
    END

  PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25)
  PRINT 'Comparing databases ' + @SourceDB + ' and ' + @TargetDb
  PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25)
  -----------------------------------------------------------------------------------------
    -- Create temp tables needed to hold the db structure
    -----------------------------------------------------------------------------------------

    IF OBJECT_ID('TEMPDB..#TABLIST_SOURCE')IS NOT NULL
        DROP TABLE #TABLIST_SOURCE
    IF OBJECT_ID('TEMPDB..#TABLIST_TARGET')IS NOT NULL
        DROP TABLE #TABLIST_TARGET
    IF OBJECT_ID('TEMPDB..#IDXLIST_SOURCE')IS NOT NULL
        DROP TABLE #IDXLIST_SOURCE
    IF OBJECT_ID('TEMPDB..#IDXLIST_TARGET')IS NOT NULL
        DROP TABLE #IDXLIST_TARGET
    IF OBJECT_ID('TEMPDB..#FKLIST_SOURCE')IS NOT NULL
        DROP TABLE #FKLIST_SOURCE
    IF OBJECT_ID('TEMPDB..#FKLIST_TARGET')IS NOT NULL
        DROP TABLE #FKLIST_TARGET
    IF OBJECT_ID('TEMPDB..#TAB_RESULTS')IS NOT NULL
        DROP TABLE #TAB_RESULTS
    IF OBJECT_ID('TEMPDB..#IDX_RESULTS')IS NOT NULL
        DROP TABLE #IDX_RESULTS
    IF OBJECT_ID('TEMPDB..#FK_RESULTS')IS NOT NULL
        DROP TABLE #FK_RESULTS

  CREATE TABLE #TABLIST_SOURCE
  (
        ID INT IDENTITY(1,1),
        DATABASENAME sysname,
        TABLENAME SYSNAME ,
        COLUMNNAME SYSNAME,
        DATATYPE SYSNAME,
        NULLABLE VARCHAR(15)
  )

  CREATE TABLE #TABLIST_TARGET
  (
        ID INT IDENTITY(1,1),
        DATABASENAME sysname,
        TABLENAME SYSNAME ,
        COLUMNNAME SYSNAME,
        DATATYPE SYSNAME,
        NULLABLE VARCHAR(15)
  )

  CREATE TABLE #IDXLIST_SOURCE
  (
        ID INT IDENTITY(1,1),
        DATABASENAME sysname,
        TABLE_NAME SYSNAME,
        IDX_NAME    SYSNAME ,
        IDX_TYPE VARCHAR(20),
        IS_PRIMARY_KEY VARCHAR(10),
        IS_UNIQUE VARCHAR(10),
        IDX_COLUMNS VARCHAR(1000),
        IDX_INCLUDED_COLUMNS VARCHAR(1000)
  );

  CREATE TABLE #IDXLIST_TARGET
  (
        ID INT IDENTITY(1,1),
        DATABASENAME sysname,
        TABLE_NAME SYSNAME,
        IDX_NAME    SYSNAME ,
        IDX_TYPE VARCHAR(20),
        IS_PRIMARY_KEY VARCHAR(10),
        IS_UNIQUE VARCHAR(10),
        IDX_COLUMNS VARCHAR(1000),
        IDX_INCLUDED_COLUMNS VARCHAR(1000)
  );

  CREATE TABLE #FKLIST_SOURCE
  (
        ID INT IDENTITY(1,1),
        DATABASENAME sysname,
        FK_NAME SYSNAME,
        FK_TABLE sysname,
        FK_COLUMNS varchar(1000),
        PK_TABLE sysname,
        PK_COLUMNS varchar(1000)
  );

  CREATE TABLE #FKLIST_TARGET
  (
        ID INT IDENTITY(1,1),
        DATABASENAME sysname,
        FK_NAME SYSNAME,
        FK_TABLE sysname,
        FK_COLUMNS varchar(1000),
        PK_TABLE sysname,
        PK_COLUMNS varchar(1000)
  );

  CREATE TABLE #TAB_RESULTS
  (
        ID INT IDENTITY(1,1),
        DATABASENAME sysname,
        TABLENAME SYSNAME ,
        COLUMNNAME SYSNAME,
        DATATYPE SYSNAME,
        NULLABLE VARCHAR(15),
        REASON VArchar(150)
  );

  CREATE TABLE #IDX_RESULTS
  (
        ID INT IDENTITY(1,1),
        DATABASENAME sysname,
        TABLE_NAME SYSNAME,
        IDX_NAME    SYSNAME ,
        IDX_TYPE VARCHAR(20),
        IS_PRIMARY_KEY VARCHAR(10),
        IS_UNIQUE VARCHAR(10),
        IDX_COLUMNS VARCHAR(1000),
        IDX_INCLUDED_COLUMNS VARCHAR(1000),
        REASON Varchar(150)
  );

  CREATE TABLE #FK_RESULTS
  (
        ID INT IDENTITY(1,1),
        DATABASENAME sysname,
        FK_NAME SYSNAME,
        FK_TABLE sysname,
        FK_COLUMNS varchar(1000),
        PK_TABLE sysname,
        PK_COLUMNS varchar(1000),
        REASON VArchar(150)
  );

  PRINT 'Getting table and column list!';
  PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25);

  BEGIN
        INSERT INTO #TABLIST_SOURCE(DATABASENAME, TABLENAME,COLUMNNAME,DATATYPE,NULLABLE)
        EXEC('SELECT '''+@SourceDB +''', T.TABLE_NAME TABLENAME,
             C.COLUMN_NAME COLUMNNAME,
             TY.name + case when TY.name IN (''char'',''varchar'',''nvarchar'') THEN
                ''(''+CASE WHEN C.CHARACTER_MAXIMUM_LENGTH>0 THEN CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) ELSE ''max''END+'')''
                ELSE
                    ''''
                END
                DATATYPE,
                CASE WHEN C.is_nullable=''NO'' THEN
                    ''NOT NULL''
                    ELSE
                    ''NULL''
                END NULLABLE
                    FROM '+@SourceDB+'.INFORMATION_SCHEMA.TABLES T
                        INNER JOIN  '+@SourceDB+'.INFORMATION_SCHEMA.COLUMNS C
                            ON T.TABLE_NAME=C.TABLE_NAME
                            and T.TABLE_CATALOG=C.TABLE_CATALOG
                            and T.TABLE_SCHEMA=C.TABLE_SCHEMA
                         INNER JOIN '+@SourceDB+'.sys.types TY
                        ON C.DATA_TYPE =TY.name
                        ORDER BY TABLENAME, COLUMNNAME,C.ORDINAL_POSITION');

        INSERT INTO #TABLIST_TARGET(DATABASENAME, TABLENAME,COLUMNNAME,DATATYPE,NULLABLE)
        EXEC('SELECT '''+@TargetDB +''', T.TABLE_NAME TABLENAME,
             C.COLUMN_NAME COLUMNNAME,
             TY.name + case when TY.name IN (''char'',''varchar'',''nvarchar'') THEN
                ''(''+CASE WHEN C.CHARACTER_MAXIMUM_LENGTH>0 THEN CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) ELSE ''max''END+'')''
                ELSE
                    ''''
                END
                DATATYPE,
                CASE WHEN C.is_nullable=''NO'' THEN
                    ''NOT NULL''
                    ELSE
                    ''NULL''
                END NULLABLE
                    FROM '+@TargetDB+'.INFORMATION_SCHEMA.TABLES T
                        INNER JOIN  '+@TargetDB+'.INFORMATION_SCHEMA.COLUMNS C
                            ON T.TABLE_NAME=C.TABLE_NAME
                            and T.TABLE_CATALOG=C.TABLE_CATALOG
                            and T.TABLE_SCHEMA=C.TABLE_SCHEMA
                         INNER JOIN '+@TargetDB+'.sys.types TY
                        ON C.DATA_TYPE =TY.name
                        ORDER BY TABLENAME, COLUMNNAME,C.ORDINAL_POSITION');

        PRINT 'Getting index list!';
        PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25);

    INSERT INTO #IDXLIST_SOURCE(DATABASENAME, TABLE_NAME,IDX_NAME,IDX_TYPE,IS_PRIMARY_KEY,IS_UNIQUE,IDX_COLUMNS,IDX_INCLUDED_COLUMNS)
        EXEC ('WITH CTE AS (
           SELECT      ic.index_id + ic.object_id AS IndexId,t.name AS TableName
                       ,i.name AS IndexName
                                             ,case when ic.is_included_column =0 then
                                                    c.name end AS ColumnName
                                            ,case when ic.is_included_column =1 then
                                                    c.name end AS IncludedColumn
                                                    ,i.type_desc
                       ,i.is_primary_key,i.is_unique
           FROM  '+@SourceDB+'.sys.indexes i
           INNER JOIN '+@SourceDB+'.sys.index_columns ic
                   ON  i.index_id    =   ic.index_id
                   AND i.object_id   =   ic.object_id
           INNER JOIN '+@SourceDB+'.sys.columns c
                   ON  ic.column_id  =   c.column_id
                   AND i.object_id   =   c.object_id
           INNER JOIN '+@SourceDB+'.sys.tables t
                   ON  i.object_id = t.object_id
)
SELECT '''+@SourceDB+''',c.TableName TABLE_NAME,c.IndexName INDEX_NAME,c.type_desc INDEX_TYPE ,c.is_primary_key IS_PRIMARY_KEY,c.is_unique IS_UNIQUE
       ,STUFF( ( SELECT '',''+ a.ColumnName FROM CTE a WHERE c.IndexId = a.IndexId ORDER BY a.ColumnName  FOR XML PATH('''')),1 ,1, '''') AS COLUMNS
       ,STUFF( ( SELECT '',''+ a.IncludedColumn FROM CTE a WHERE c.IndexId = a.IndexId  ORDER BY a.ColumnName  FOR XML PATH('''')),1 ,1, '''') AS INCLUDED_COLUMNS
FROM   CTE c
GROUP  BY c.IndexId,c.TableName,c.IndexName,c.type_desc,c.is_primary_key,c.is_unique
ORDER  BY c.TableName ASC,c.is_primary_key DESC, C.IndexName ' );

    INSERT INTO #IDXLIST_TARGET(DATABASENAME,TABLE_NAME,IDX_NAME,IDX_TYPE,IS_PRIMARY_KEY,IS_UNIQUE,IDX_COLUMNS,IDX_INCLUDED_COLUMNS)
        EXEC ('WITH CTE AS (
           SELECT      ic.index_id + ic.object_id AS IndexId,t.name AS TableName
                       ,i.name AS IndexName
                                             ,case when ic.is_included_column =0 then
                                                    c.name end AS ColumnName
                                            ,case when ic.is_included_column =1 then
                                                    c.name end AS IncludedColumn
                                                    ,i.type_desc
                       ,i.is_primary_key,i.is_unique
           FROM  '+@TargetDB+'.sys.indexes i
           INNER JOIN '+@TargetDB+'.sys.index_columns ic
                   ON  i.index_id    =   ic.index_id
                   AND i.object_id   =   ic.object_id
           INNER JOIN '+@TargetDB+'.sys.columns c
                   ON  ic.column_id  =   c.column_id
                   AND i.object_id   =   c.object_id
           INNER JOIN '+@TargetDB+'.sys.tables t
                   ON  i.object_id = t.object_id
)
SELECT '''+@TargetDB+''',c.TableName,c.IndexName,c.type_desc,c.is_primary_key,c.is_unique
       ,STUFF( ( SELECT '',''+ a.ColumnName FROM CTE a WHERE c.IndexId = a.IndexId  ORDER BY a.ColumnName  FOR XML PATH('''')),1 ,1, '''') AS Columns
       ,STUFF( ( SELECT '',''+ a.IncludedColumn FROM CTE a WHERE c.IndexId = a.IndexId  ORDER BY a.ColumnName FOR XML PATH('''')),1 ,1, '''') AS IncludedColumns
FROM   CTE c
GROUP  BY c.IndexId,c.TableName,c.IndexName,c.type_desc,c.is_primary_key,c.is_unique
ORDER  BY c.TableName ASC,c.is_primary_key DESC, C.IndexName ');

PRINT 'Getting foreign key list!';
PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25);

INSERT INTO #FKLIST_SOURCE(DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS)
    EXEC ('With CTE
                AS
            (select OBJECT_NAME(FK.parent_object_id,db_id('''+@TargetDB+''')) PK_TABLE,
                        C1.name PK_COLUMN,
            object_name(FK.referenced_object_id,db_id('''+@TargetDB+'''))FK_TABLE,
            C2.name FK_COLUMN,
            FK.name     FK_NAME
from
        '+@SourceDB+'.sys.foreign_keys FK
            inner join
        '+@SourceDB+'.sys.foreign_key_columns FKC
            on FK.object_id=FKC.constraint_object_id
            inner join
        '+@SourceDB+'.sys.columns C1
            on FKC.parent_column_id=C1.column_id
            and FKC.parent_object_id=C1.object_id
            inner join
        '+@SourceDB+'.sys.columns C2
            on FKC.referenced_column_id=C2.column_id
            and FKC.referenced_object_id=C2.object_id
    )
SELECT '''+@SourceDB+''',C.FK_NAME,
             C.FK_TABLE,             STUFF( ( SELECT '',''+ A.FK_COLUMN FROM CTE a WHERE c.FK_NAME = a.FK_NAME and C.FK_TABLE=a.FK_TABLE FOR XML PATH('''')),1 ,1, '''') AS FK_COLUMNS,
             C.PK_TABLE,
             STUFF( ( SELECT '',''+ A.PK_Column FROM CTE a WHERE c.FK_NAME = a.FK_NAME and C.PK_TABLE=a.PK_TABLE FOR XML PATH('''')),1 ,1, '''') AS PK_COLUMNS
FROM CTE C
group by C.FK_NAME,
             C.FK_TABLE,
             C.PK_TABLE

order by C.FK_NAME,
             C.FK_TABLE,
             C.PK_TABLE

             ')

INSERT INTO #FKLIST_TARGET(DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS)
EXEC('
        With CTE
AS
(select OBJECT_NAME(FK.parent_object_id,db_id('''+@TargetDB+''')) PK_TABLE,
            C1.name PK_COLUMN,
            object_name(FK.referenced_object_id,db_id('''+@TargetDB+'''))FK_TABLE,
            C2.name FK_COLUMN,
            FK.name     FK_NAME
from
        '+@TargetDB+'.sys.foreign_keys FK
            inner join
        '+@TargetDB+'.sys.foreign_key_columns FKC
            on FK.object_id=FKC.constraint_object_id
            inner join
        '+@TargetDB+'.sys.columns C1
            on FKC.parent_column_id=C1.column_id
            and FKC.parent_object_id=C1.object_id
            inner join
        '+@TargetDB+'.sys.columns C2
            on FKC.referenced_column_id=C2.column_id
            and FKC.referenced_object_id=C2.object_id
    )
SELECT '''+@TargetDB+''',C.FK_NAME,
             C.FK_TABLE,             STUFF( ( SELECT '',''+ A.FK_COLUMN FROM CTE a WHERE c.FK_NAME = a.FK_NAME and C.FK_TABLE=a.FK_TABLE FOR XML PATH('''')),1 ,1, '''') AS FK_COLUMNS,
             C.PK_TABLE,
             STUFF( ( SELECT '',''+ A.PK_Column FROM CTE a WHERE c.FK_NAME = a.FK_NAME and C.PK_TABLE=a.PK_TABLE FOR XML PATH('''')),1 ,1, '''') AS PK_COLUMNS
FROM CTE C
group by C.FK_NAME,
             C.FK_TABLE,
             C.PK_TABLE

order by C.FK_NAME,
             C.FK_TABLE,
             C.PK_TABLE

             ')
END;

PRINT 'Print column mismatches!';
PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25);

INSERT INTO #TAB_RESULTS(DATABASENAME, TABLENAME,COLUMNNAME,DATATYPE,NULLABLE,REASON)
SELECT @SourceDB AS DATABASENAME,TABLENAME,COLUMNNAME,DATATYPE,NULLABLE,REASON FROM
(SELECT TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_SOURCE
EXCEPT
SELECT TS.TABLENAME,TS.COLUMNNAME,TS.DATATYPE,TS.NULLABLE FROM #TABLIST_SOURCE TS
INNER JOIN
#TABLIST_TARGET TT
ON TS.TABLENAME=TT.TABLENAME AND TS.COLUMNNAME=TT.COLUMNNAME) TAB_NONMATCH
CROSS JOIN (SELECT 'Missing Column' As Reason)Tab2
UNION ALL
SELECT @TargetDb as DATABASENAME,TABLENAME,COLUMNNAME,DATATYPE,NULLABLE,REASON FROM
(SELECT TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_TARGET
EXCEPT
SELECT TT.TABLENAME,TT.COLUMNNAME,TT.DATATYPE,TT.NULLABLE FROM #TABLIST_TARGET TT
INNER JOIN
#TABLIST_SOURCE TS
ON TS.TABLENAME=TT.TABLENAME AND TS.COLUMNNAME=TT.COLUMNNAME) TAB_MATCH
CROSS JOIN (SELECT 'Missing column ' As Reason)Tab2

--NON MATCHING COLUMNS
INSERT INTO #TAB_RESULTS(DATABASENAME,TABLENAME,COLUMNNAME,DATATYPE,NULLABLE,REASON)
    SELECT
        @SourceDB as DATABASENAME,
        TABLENAME,
        COLUMNNAME,
        DATATYPE,
        NULLABLE,
        REASON
    FROM
    (SELECT * FROM
    (SELECT TS.TABLENAME,TS.COLUMNNAME,TS.DATATYPE,TS.NULLABLE FROM #TABLIST_SOURCE TS
        INNER JOIN
         #TABLIST_TARGET TT ON TS.TABLENAME=TT.TABLENAME AND TS.COLUMNNAME=TT.COLUMNNAME)T
         EXCEPT
         (SELECT  TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_SOURCE
    INTERSECT
    SELECT  TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_TARGET))TT1
    CROSS JOIN (SELECT 'Definition not matching'AS REASON) t

    UNION ALL

    SELECT @TargetDb as DATABASENAME,
        TABLENAME,
        COLUMNNAME,
        DATATYPE,
        NULLABLE,
        REASON
    FROM(
    SELECT * FROM
    (SELECT TT.TABLENAME,TT.COLUMNNAME,TT.DATATYPE,TT.NULLABLE FROM #TABLIST_TARGET TT
        INNER JOIN
         #TABLIST_SOURCE TS ON TS.TABLENAME=TT.TABLENAME AND TS.COLUMNNAME=TT.COLUMNNAME)T
         EXCEPT
         (SELECT  TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_TARGET
    INTERSECT
    SELECT TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_SOURCE))TAB_NONMATCH
    CROSS JOIN (SELECT 'Definition not matching' AS REASON)T;

PRINT 'Print index mismatches!';
PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25);

INSERT INTO #IDX_RESULTS(DATABASENAME, TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE,REASON)
SELECT @SourceDB AS DATABASENAME, TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE,REASON FROM
(SELECT  TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_SOURCE
EXCEPT
SELECT TS.TABLE_NAME,TS.IDX_NAME, TS.IDX_COLUMNS,TS.IDX_INCLUDED_COLUMNS,TS.IS_PRIMARY_KEY,TS.IS_UNIQUE FROM #IDXLIST_SOURCE TS
INNER JOIN
#IDXLIST_TARGET TT
ON TS.TABLE_NAME=TT.TABLE_NAME AND TS.IDX_NAME=TT.IDX_NAME) TAB_NONMATCH
CROSS JOIN (SELECT 'Missing Index n' As Reason)Tab2
UNION ALL
SELECT @TargetDb as DATABASENAME, TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE,REASON FROM
(SELECT TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_TARGET
EXCEPT
SELECT TT.TABLE_NAME,TT.IDX_NAME,TT.IDX_COLUMNS,TT.IDX_INCLUDED_COLUMNS,TT.IS_PRIMARY_KEY,TT.IS_UNIQUE FROM #IDXLIST_TARGET TT
INNER JOIN
#IDXLIST_SOURCE TS
ON TS.TABLE_NAME=TT.TABLE_NAME AND TS.IDX_NAME=TT.IDX_NAME) TAB_MATCH
CROSS JOIN (SELECT 'Missing index ' As Reason)Tab2

--NON MATCHING INDEX
INSERT INTO #IDX_RESULTS(DATABASENAME,TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE,REASON)
    SELECT
        @SourceDB as DATABASENAME,
        TABLE_NAME,
        IDX_NAME,
        IDX_COLUMNS,
        IDX_INCLUDED_COLUMNS,
        IS_PRIMARY_KEY,
        IS_UNIQUE,
        REASON
    FROM
    (SELECT * FROM
    (SELECT TS.TABLE_NAME,
        TS.IDX_NAME,
        TS.IDX_COLUMNS,
        TS.IDX_INCLUDED_COLUMNS,
        TS.IS_PRIMARY_KEY,
        TS.IS_UNIQUE
        FROM #IDXLIST_SOURCE TS
        INNER JOIN
         #IDXLIST_TARGET TT ON TS.TABLE_NAME=TT.TABLE_NAME AND TS.IDX_NAME=TT.IDX_NAME)T
         EXCEPT
         (SELECT  TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_SOURCE
    INTERSECT
    SELECT  TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_TARGET))TT1
    CROSS JOIN (SELECT 'Definition not matching' AS REASON) t

    UNION ALL

    SELECT @TargetDb as DATABASENAME,
        TABLE_NAME,
        IDX_NAME,
        IDX_COLUMNS,
        IDX_INCLUDED_COLUMNS,
        IS_PRIMARY_KEY,
        IS_UNIQUE,
        REASON
    FROM(
    SELECT * FROM
    (SELECT TT.TABLE_NAME,
        TT.IDX_NAME,
        TT.IDX_COLUMNS,
        TT.IDX_INCLUDED_COLUMNS,
        TT.IS_PRIMARY_KEY,
        TT.IS_UNIQUE  FROM #IDXLIST_TARGET TT
        INNER JOIN
         #IDXLIST_SOURCE TS ON TS.TABLE_NAME=TT.TABLE_NAME AND TS.IDX_NAME=TT.IDX_NAME)T
         EXCEPT
         (SELECT  TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_TARGET
    INTERSECT
    SELECT TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_SOURCE))TAB_NONMATCH
    CROSS JOIN (SELECT 'Definition not matching' AS REASON)T;

PRINT 'Print key mismatches!';
PRINT Replicate('-', Len(@SourceDB) + Len(@TargetDb) + 25);

INSERT INTO #FK_RESULTS(DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS,REASON)
SELECT @SourceDB AS DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS,REASON
 FROM
(SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_SOURCE
EXCEPT
SELECT TS.FK_NAME,TS.FK_TABLE,TS.FK_COLUMNS,TS.PK_TABLE,TS.PK_COLUMNS FROM #FKLIST_SOURCE TS
INNER JOIN
#FKLIST_TARGET TT
ON TS.FK_NAME=TT.FK_NAME) TAB_NONMATCH
CROSS JOIN (SELECT 'Missing Index n' As Reason)Tab2

UNION ALL

SELECT @TargetDb as DATABASENAME,FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS,REASON FROM
(SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_TARGET
EXCEPT
SELECT TT.FK_NAME,TT.FK_TABLE,TT.FK_COLUMNS,TT.PK_TABLE,TT.PK_COLUMNS FROM #FKLIST_TARGET TT
INNER JOIN
#FKLIST_SOURCE TS
ON TS.FK_NAME=TT.FK_NAME) TAB_MATCH
CROSS JOIN (SELECT 'Missing key' As Reason)Tab2

--NON MATCHING Keys
INSERT INTO #FK_RESULTS(DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS,REASON)
    SELECT
        @SourceDB as DATABASENAME,
        FK_NAME,
        FK_TABLE,
        FK_COLUMNS,
        PK_TABLE,
        PK_COLUMNS,
        REASON
    FROM
    (SELECT * FROM
    (SELECT TS.FK_NAME,
        TS.FK_TABLE,
        TS.FK_COLUMNS,
        TS.PK_TABLE,
        TS.PK_COLUMNS
        FROM #FKLIST_SOURCE TS
        INNER JOIN
         #FKLIST_TARGET TT ON TS.FK_NAME=TT.FK_NAME)T
         EXCEPT
         (SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_SOURCE
    INTERSECT
    SELECT  FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_TARGET))TT1
    CROSS JOIN (SELECT 'Definition not matching' AS REASON) t

    UNION ALL

    SELECT @TargetDb as DATABASENAME,
        FK_NAME,
        FK_TABLE,
        FK_COLUMNS,
        PK_TABLE,
        PK_COLUMNS,
        REASON
    FROM(
    SELECT * FROM
    (SELECT TT.FK_NAME,
        TT.FK_TABLE,
        TT.FK_COLUMNS,
        TT.PK_TABLE,
        TT.PK_COLUMNS  FROM #FKLIST_TARGET TT
        INNER JOIN
         #FKLIST_SOURCE TS ON TS.FK_NAME=TT.FK_NAME)T
         EXCEPT
         (SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_TARGET
    INTERSECT
    SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_SOURCE))TAB_NONMATCH
    CROSS JOIN (SELECT 'Definition not matching' AS REASON)T;

--Print Final Results

--SELECT * FROM #TAB_RESULTS
--where tablename= 'tblBOrder'

SELECT * FROM #IDX_RESULTS
where TABLE_NAME = 'tblBorder'

--SELECT * FROM #FK_RESULTS

--select * from #FKLIST_TARGET

select * FROM #IDXLIST_SOURCE
go
select * FROM #IDXLIST_target
go


Do I lose data if I change from INT to TINYINT I have a database (Engine InnoDB) that was setup by someone else. I noticed that one column contains data between 1 and 107 at this moment, and it is highly unlikely to further increase much.

As it is currently set up as INT my idea was to change type to TINYINT unsigned.

Can I just do that without losing data? What would be the most efficient way to go about it?

I am still rather new to databases, getting my hands dirty at the moment and learning all about data types and performance, etc. Looks like my precursor hasn't done such a good job... :(

A tiny int will save you space. Here are their storage:

tinyint: 1 byte
smallint: 2 bytes
int: 4 bytes

From MySQL official website: http://dev.mysql.com/doc/refman/5.1/en/integer-types.html

Keep in mind a tinyint has a range up to 255. If there is a chance you may go over, I would use smallint or int

You can alter the column in sql and not lose data:

    alter table MyTable alter column MyColumn tinyint --(null|not null)


What would be better long term: Creating many documents in MongoDB or fewer documents with nested arrays I have a scenario I'm debating for my app.

Let's say I have a Quiz application with a MongoDB structure that looks like this:

{
_id: xxxxxx,
userId: xxxx,
quizData: [
{
name: xxxx,
quizId: xxxx,
questions: [...]
},
{
name: xxxx,
quizId: xxxx,
questions: [...]
}
...]}

I'm torn between what's better long-term for my application: let each user have just one document and for each quiz he/she creates, it will be added to the "quizData" array of objects...OR...let each user's quiz have its own document in the MongoDB, where I create the above structure for each quiz (obviously, the "quizData" will only contain one object instead of an array of many).

Theoretically, the user can create as many Quizzes as they want, but realistically, that probably won't be the case. I fear that if I let each user have it's own document for each quiz in the MongoDB, it will populate the collection fast and over time (with many users doing the same), it will slow down the querying process.

Anyone have any thoughts on the best way to go about this?

I'm torn between what's better long-term for my application: let each user have just one document and for each quiz he/she creates, it will be added to the "quizData" array of objects...OR...let each user's quiz have its own document in the MongoDB, where I create the above structure for each quiz (obviously, the "quizData" will only contain one object instead of an array of many).

Arrays which potentially grow without bound are an antipattern for performance and usability.

I would recommend having each quiz in a separate document for reasons including:

Better granularity for server memory management and document updates. When you retrieve or update a single quiz, the MongoDB server does not have to fetch/update a large document with all the quizzes for that user.
Easier to manipulate documents in your application code. Arrays of arrays are awkward to work with and have extra overhead if if you want to pull out a subset of elements via server-side manipulation. Similarly, if you wanted to provide a list of active/available quizzes in your application it would be easier to have quiz as a top-level object.
Avoid worrying about hitting the document size limit (16MB as at MongoDB 3.2). If users post many (or large) quizzes, a single document per user makes the document limit a much more likely issue to have to deal with in your application code.

For more information, see: Why shouldn't I embed large arrays in my documents?.


Order by one column inside other column I've a MySQL table that looks like the following:

http://sqlfiddle.com/#!2/ca67ac/1

I use it to build a grid where i put my content. The resultant is this:

I've to render every row separatelly but, inside every row, render it's content by column.
So, for this task i need to get the rows in this order: 1, 2, 3, 4, 6, 5, 7.

column number 6 must come before 5 because it's first in the column order inside that row from left to right.

Is there any sorting method that i can use to achieve that? Order by row but order by column inside that order.

I've solved it by doing the following:

SELECT nWidth, nHeight, nColumn,
(SELECT MAX(t.nRow + (t.nHeigth - 1)) FROM test t WHERE t.nRow = tp.nRow
GROUP BY t.nRow LIMIT 1) AS nRow
FROM test tp ORDER BY nRow, tp.nColumn

This will get the max from the row number plus it's heigth minus 1.
So, if we have a row that has a cell with height equal or higher than 2, all its cells are going to be in the same row. So, inside that row, we order by Column.

Hope this helps someone.


Do IOTs generate significantly more undo per block for deletes? IOTs are an index-like structure. When deleting large contiguous chunks of data, how much undo is generated as compared to a similar contiguous delete from a heap?

No, this type of delete generates roughly the same quantity of undo regardless of whether it is an IOT or heap.

In contrast, inserting into the IOT generates far more undo.

heap:

drop table foo;
create table foo( id integer not null,
                  val char(100) default 'A' not null);
--
insert into foo(id) select level from dual connect by level<100000;
--
select used_ublk
from v$transaction join v$session on(addr=taddr)
where sid=sys_context('USERENV','SID');
/*
USED_UBLK
----------------------
149
*/
--
analyze table foo compute statistics;
--
select blocks from all_tab_statistics where table_name='FOO';
/*
BLOCKS
----------------------
3274
*/
--
delete from foo;
--
select used_ublk
from v$transaction join v$session on(addr=taddr)
where sid=sys_context('USERENV','SID');
/*
USED_UBLK
----------------------
5264
*/

IOT:

drop table foo;
create table foo( id integer not null primary key,
                  val char(100) default 'A' not null) organization index;
--
insert into foo(id) select level from dual connect by level<100000;
--
select used_ublk
from v$transaction join v$session on(addr=taddr)
where sid=sys_context('USERENV','SID');
/*
USED_UBLK
----------------------
2030
*/
--
analyze table foo compute statistics;
select leaf_blocks from all_ind_statistics where table_name='FOO';
/*
LEAF_BLOCKS
----------------------
2942
*/
--
delete from foo;
select used_ublk
from v$transaction join v$session on(addr=taddr)
where sid=sys_context('USERENV','SID');
/*
USED_UBLK
----------------------
4741
*/


Restroring MYSQL databse from .frm and .ibd files (database does not exist error) please help ...

I want to restore MYSQL database using .frm and .ibd files (that's all I am having). here is additional information..

I reinstalled same version of WAMP without taking backup of mysql data. Now i am remain with "BIN" directory which contains some .frm and .ibd files. When i opend phpmyadmin i can find  My old database and table names in browser but when I click on it it says "'xyz'table doesn't exist". But exceptionally one of table is working and displaying it's data. all my tables were of  innoDB.

Get all .ibd and .frm files into a zip or tar.gz and upload it on https://recovery.twindb.com/ it will recover tables from .ibd files.

tar zcf database.tar.gz /path/to/dir_with_ibd_and_frm


SQL Server DB Restore Clustered to Single Node? Can you restore a DB from a SQL Server 2005 Clustered Environment (Prod) to a single server (single node) (SQL Server 2005) in Dev/Sys?

Cheers,
Conor

Yes. The clustered production SQL Server instance should be transparent to the database


What is the cost of having many columns as included columns in a nonclustred index? What is the cost of having many columns as included columns in a nonclustred index in SQL Server?

The more columns you include to index the larger this index becomes. It affects all operations with this index (inserts, updates, selects). It takes more space in buffer pool and when you use index more data needs to be processed (including maintenance tasks like integrity checks and backups). Also when you update column included to this index SQL Server needs to update this index too (it means more reads, writes and even more locks).


SQL Server 2012 Resource Governor Enhancement In SQL Server 2012 what specific enhacement was made regarding controlling memory usage by using Resource Governor. Up until SQL Server 2008 R2 resource governor was only able to control memory related to query memory grant.

I looked in SQL Server 2012 BOL however it does not specifically states anything clearly.

This is a good question, because there is a lot of misleading information out there.

The changes to what memory Resource Governor can manage in SQL Server 2012 roughly coincide with the changes to what memory the Max Server Memory setting can manage. Prior to SQL Server 2012, this was just buffer pool / single-page allocations. In SQL Server 2012 this is extended to multi-page allocations. I lifted this diagram from Memory Manager surface area changes in SQL Server 2012:

And while the statement is a little over-promising IMHO, the SQL OS team also stated in New SQLOS features in SQL Server 2012:

  Resource Governor governs all SQL memory consumption (other than special cases like buffer pool)

When I first started doing research on the changes to this feature, the indication I had was that it could manage all memory allocations except buffer pool and column store cache. Prior to SQL Server 2012, Resource Governor only had control over query grant memory.

There are of course other important enhancements to Resource Governor in SQL Server 2012, that I thought I'd mention for other readers who get here because of your question title. You can now:

have 64 resource pools instead of the previous limit of 20
use real CPU capping (even with no contention) - the previous model relied on concurrency before implementing a cap
implement scheduler / NUMA node affinity (e.g. tie a resource pool to a specific NUMA node)

Also note there are some differences with how memory manager changes have been implemented in 32-bit vs. 64-bit. Some relevant info here.


Ignore duplicates while using join I'm trying to ignore duplicates between two providers in my tables while using join.

I'm using this query for now:

SELECT *
FROM A AS a1, A AS a2
WHERE a1.provider <> a2.provider AND a1.etype = a2.etype

Unfortunately, that query gives me duplicates (two rows of the same providers, in opposite sides).

I'm trying to ignore rows that appear twice in the opposite sides, for example:

etype    provider    etype    provider
hello    aaa         hello    bbb
hello    bbb         hello    aaa

This dbfiddle illustrates the desired output as well.

What Kondybas explained about the appropriate operator, but with legal syntax:

SELECT etype, a1.provider AS provider1, a2.provider AS provider2
FROM   A a1
JOIN   A a2 USING (etype)
WHERE  a1.provider < a2.provider;

[INNER] JOIN requires a join condition, with either ON or USING.

To keep events with only a single provider in the result, use a LEFT [OUTER] JOIN:

SELECT a1.etype, a1.provider AS provider1, a2.provider AS provider2
FROM   A a1
LEFT   JOIN A a2 ON a1.etype = a2.etype
                AND a1.provider < a2.provider;

The second condition has to move to the join condition as well in this case. See:

Sort by number of related rows in referencing table


How do you set the working directory to a mapped network drive for R language in SSMS? So I'm messing around with the new "Integration with R" feature in SQL 2016.  However, having trouble setting the working directory to a mapped network drive (Z:) within SSMS.

Here's my code:

EXEC sp_execute_external_script
@language = N'R',
@script = N'setwd("Z:/")
          mytextvariable <- getwd();
          OutputDataSet <- as.data.frame(mytextvariable);',
@input_data_1 = N'SELECT 1 as Temp1'
WITH RESULT SETS (([col] char(20) NOT NULL));

Getting the error message:

  "Error in setwd("Z:/"): cannot change working directory
  Calls: source -> withVisible -> eval -> eval -> setwd"

I've tried changing the drive letter to local, physical drives on the server and it works fine, just doesn't work for the mapped network drive.

Each user session on Windows has its own set of network drives.  Just because your user session has the drive mapped doesn't mean the session being run by the SQL Server service has that drive mapped.

You can check if the mapped drive is visible to SQL Server by running this:

EXEC sys.xp_cmdshell 'net use';

This will output something similar to:

output
New connections will be remembered.
NULL
NULL
Status       Local     Remote                    Network
-------------------------------------------------------------------------------
OK           Z:        \\machine\share           Microsoft Windows Network
The command completed successfully.
NULL
NULL

In order to successfully run the xp_cmdshell extended stored procedure, you may need to enable it, using this code:

EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE
GO
EXEC sys.sp_configure 'xp_cmdshell', 1;
RECONFIGURE
GO

You can determine what account SQL Server is using by looking at the output from this command:

EXEC sys.xp_cmdshell 'whoami';

The account referenced in the above output must have access to the share, as well as the file system referenced by the share.

I don't have access to R here, however you might try mapping the drive, prior to running the R script, like so:

EXEC sys.xp_cmdshell 'net use Z: \\server\share /user:[DOMAIN]\Username <password>';
EXEC sys.sp_execute_external_script @language = N'R',
    @script = N'setwd("Z:/")
          mytextvariable <- getwd();
          OutputDataSet <- as.data.frame(mytextvariable);',
    @input_data_1 = N'SELECT 1 as Temp1'
WITH RESULT SETS (([col] char(20) NOT NULL));

The above code will cause the drive to be mapped for SQL Server's session until SQL Server is restarted.  Be advised, it's not a great idea to save the password in a script unless the script's location is guaranteed safe, otherwise an attacker might see the password.


What is DEALLOCATE pdo_stmt_00000001 in pg_stats_activity "query"? In my return from pg_stat_activity, there are many processes in the "query" column that have the value "DEALLOCATE pdo_stmt_00000001".

What does it mean?

The state of these connections is probably (and hopefully) idle.

That means that the last thing that happened on these connections was to deallocate a prepared statement.

That is kind of funny, because one of the key advantages of prepared statements is to exist for a longer time and be reused. Maybe the application uses a connection pool and wants to “clean” the connection before returning it to the pool.

Anyway, there is nothing to worry about.


Mysql_upgrade fails, table is corrupted I have a mariaDB with a table that is corrupted. It's useless data to me, so I've tried to drop the schema but it won't drop. Instead, it gives me this error when trying to drop it:

"Error Code: 1805. Column count of mysql.proc is wrong. Expected 20, found 21. The table is probably corrupted."

I've read on here that I should try to run a mysql_upgrade to fix this issue. But this also has failed when I point to the MariaDB bin directory and run this command line:

C:\Program Files\MariaDB 10.2\bin>mysql_upgrade --force -h localhost -uroot -p -
P3307

This is the results I get from it:

    Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.gtid_executed                                OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry                         OK
mysql.user                                         OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1408 (HY000) at line 545: Event Scheduler: An error occurred when initiali
zing system tables. Disabling the Event Scheduler.
ERROR 1805 (HY000) at line 572: Column count of mysql.proc is wrong. Expected 20
, found 21. The table is probably corrupted
ERROR 1805 (HY000) at line 574: Column count of mysql.proc is wrong. Expected 20
, found 21. The table is probably corrupted
ERROR 1805 (HY000) at line 582: Column count of mysql.proc is wrong. Expected 20
, found 21. The table is probably corrupted
ERROR 1805 (HY000) at line 584: Column count of mysql.proc is wrong. Expected 20
, found 21. The table is probably corrupted
FATAL ERROR: Upgrade failed

Any advice on how to resolve this?

10.2.6 is a really old version from 2017, and the first table reelase..

Consider upgrading to latest 10.2 version (e.g. 10.2.25).

You table probably isn't corrupt, just has the wrong definition. In 10.2.6 it should look like this. Look to see what you need to alter to make this your table definition.


SQL Server - Grid Export, choose delimiter I'm trying to find the fastest way to actually get SQL Server query results to Excel, with the least possible trouble.

Frankly the query > text is a bit annoying, there's a "9393 results found" at the bottom. There's also a character limit of 8,000.

The query > grid looks nice, but it either exports (copy>paste) with no delimiter whatsoever, or defaults to commas with (save as) - which doesn't work for data containing natural commas.

Is there any way to do grid export > with semicolons or custom deliminator?

Not sure why SQL Server likes to make this as annoying as possible, there are many MySQL clients that make this simple.

The "9393 results found" message can be removed by putting SET NOCOUNT ON before your query.

I'm using SSMS 2016 and I see the option to use a custom delimiter for Results to Text by going to Tools > Options, then looking under Query Results > SQL Server > Results to Text

The "Output format" dropdown has a custom delimiter option.

If you are committed to using Results to Grid, then you might have to edit your SELECT columns, perhaps using QUOTENAME() or another method.


What does it mean that a "clustered index does not store pointers to the data"? I am a beginner in SQL and Database Administration and I am trying to undestand the difference between clustered and non-clustered indexes. More or less I understood what the different tasks are but I have some issues when it comes to the question of "pointers". I don´t come from CS so I have some issue to imagine what happens at the "leaf level" with the pointers and in which way they connect the index to the data. So, what does it mean that a clustered index does not store pointers to the data, while, the non-clustered one, does it?
Sorry in advance if these questions sound stupid but at this point I am pretty confused!

No, that's not a bad question.
In MS SQL Server (and others that work similarly), a clustered index doesn't store pointers to the data because it doesn't need to, a clustered index actually contains the table data itself.
A clustered index is the table, just ordered in a particular way.
If you create and populate a brand new table with no clustered index (called a "heap"), then add a non-clustered index, you're creating a new (ordered) object that still has to point to the rows in the actual table.
But if you take that heap and add a clustered index, you are literally re-ordering the table itself.


MongoDB Upgrade to 3.0: HostAndPort Exception I want to upgrade my small MongoDB-Cluster (1 mongos, 3 config-servers, 2 shards a 3 nodes) running on OpenSuSe 13.1 (64bit) from 2.6.7 to 3.0.5. But it fails at the second step of the upgrade guide: upgrade a mongos instance

What I'm done:

downloaded a legacy-linux-64bit-version of MongoDB 3.0.5
deactivated load balancer
tried to start mongos with

mongos --configdb data/configdb --config config/router.conf --upgrade

This is the output of mongos:

2015-08-07T15:41:43.281+0200 W SHARDING running with 1 config server should be done only for testing purposes and is not recommended for production
2015-08-07T15:41:43.290+0200 I SHARDING [mongosMain] MongoS version 3.0.5 starting: pid=22726 port=27027 64-bit host=myhostname (--help for usage)
2015-08-07T15:41:43.290+0200 I CONTROL  [mongosMain] db version v3.0.5
2015-08-07T15:41:43.290+0200 I CONTROL  [mongosMain] git version: 8bc4ae20708dbb493cb09338d9e7be6698e4a3a3
2015-08-07T15:41:43.290+0200 I CONTROL  [mongosMain] build info: Linux build2.ny.cbi.10gen.cc 2.6.32-431.3.1.el6.x86_64 #1 SMP Fri Jan 3 21:39:27 UTC 2014 x86_64 BOOST_LIB_VERSION=1_49
2015-08-07T15:41:43.290+0200 I CONTROL  [mongosMain] allocator: tcmalloc
2015-08-07T15:41:43.290+0200 I CONTROL  [mongosMain] options: { config: "conf/router.conf", net: { bindIp: "myhostname", port: 27027 }, security: { keyFile: "res/mongodb.key" }, sharding: { configDB: "data/configdb/" }, upgrade: true }
uncaught DBException in mongos main:
9 Empty host component parsing HostAndPort from ""

I'm using the same settings as the already running instance, so the network settings should be correct.

Anyone a clue what I'm doing wrong here? Why is there a warning about one config server only, while I'm using three? And what does the exception mean?

The error/exception is because you point to a local database with --configdb. That should be a serverip:port:

##shards/replicas:
mongod --shardsvr --replSet ReplName --dbpath /data/mongodb (--port 27018) --logpath /data/log/log.log ...
##configservers:
mongod --configsvr --dbpath /data/cfg (--port 27019) --logpath /data/log/log.cfg
##mongos routers:
mongos --configdb cfg1IP:cfg1PORT,cfg2IP:cfg2PORT,cfg3IP:cfg3PORT (--port 27017) --logpath /data/log/log.mongos

Or you can use a configuration file.

But here you seem to mix the configuration for configservers with mongos-routers.


Is this way of MySQL shutdown via bat file sufficient to minimise corruption? I had memory issues with using 32bit Xampp so I have installed 64bit components myself as a new server.

Although, I want to make sure I am stopping the MySQL database server properly.

Is this a sufficient way to shut it down so that frequently stopping MySQL in the hectic development stage isn't corrupting the database?

The following is contained in a bat file I wrote.

@echo off
"C:\host\MariaDB\bin\mysqladmin.exe"  -u root -prootpass flush-tables
echo Flushing DB, Hopefully.
TIMEOUT 30
echo Flushed DB, I think!

"C:\host\MariaDB\bin\mysqladmin.exe"  -u root -prootpass shutdown
echo Stopped MySQL service.
pause

Again, it works but I want to make sure I am minimising the chance of corruption from stopping the database server.

Thanks in advance.

If you want to shutdown MySQL effectively, you need to run several things
@echo off
rem
rem Flush Everything and its Grandmother
rem
echo "Stopping MySQL Service"
"C:\host\MariaDB\bin\mysql.exe" -u root -prootpass -ANe"SET GLOBAL innodb_fast_shutdown=0; STOP SLAVE; FLUSH BINARY LOGS; FLUSH TABLES"
"C:\host\MariaDB\bin\mysqladmin.exe" -u root -prootpass shutdown
echo "Stopped MySQL Service"
pause

The first line does the following

STOP SLAVE : If this MySQL Service is a Slave, it will close the IO and SQL Threads and close the file handles opened against the relay logs. If replication is not enabled, it will be just a warning.
FLUSH BINARY LOGS : If binary logging is enabled, it will close the current binary log (which flushes it), and open a new one (file size of the new binary logs will be less than 200 bytes). If binary logging is disabled, it will be just a warning.
FLUSH TABLES : This closes any open file handles against all open tables, alerting the OS to perform the disk flush of all table changes in memory.
SET GLOBAL innodb_fast_shutdown=0 : The default value for innodb_fast_shutdown is 1. This allows InnoDB to suspend its changes in the redo logs and the double write buffer. Thus, changes will be applied the next time MySQL Service is started. This is also known as crash recovery upon startup. By setting innodb_fast_shutdown to 0, this causes InnoDB to apply changes during the shutdown. That will result in the MySQL Service starting faster because it does not need to do crash recovery.

I have written about using innodb_fast_shutdown many times

Apr 15, 2012 : Mysql Innodb: InnoDB: ERROR: the age of the last checkpoint is InnoDB: which exceeds the log group capacity
Mar 06, 2013 : How to properly kill MySQL?
Apr 23, 2013 : Start/Stop MySQL
May 05, 2013 : Issue after moving the ib_logfile1 and ib_logfile0 files
Dec 09, 2013 : Purge old records from mysql ibdata1 file
Jan 07, 2015 : Flush/Clean MySql ib_logfile0 ib_logfile1

GIVE IT A TRY !!!
NOTE : The reason for all the other commands is to close as many different file handles as possible before the actual shutdown process. Each closed file handle will induce a flush of the table if there are any disk changes. This gives the shutdown process less to work on, focusing its attention mainly on rolling forward all changes in the InnoDB Plumbing.
Here is a Pictorial Representation of InnoDB

This picture was made by Percona CTO Vadim Tkachenko
This picture should let you see what moving parts are in motion during the shutdown.
UPDATE 2016-10-13 07:45 EDT
@a_horse_with_no_name commented

Are you saying that a "regular" shutdown does not in fact cleanly shut down the database engine? (especially that it will not flush everything to disk?)

When it comes InnoDB, shutting down MySQL is subject to interpretation. This is why innodb_fast_shutdown exists. The documentation says:

The InnoDB shutdown mode. If the value is 0, InnoDB does a slow shutdown, a full purge and a change buffer merge before shutting down. If the value is 1 (the default), InnoDB skips these operations at shutdown, a process known as a fast shutdown. If the value is 2, InnoDB flushes its logs and shuts down cold, as if MySQL had crashed; no committed transactions are lost, but the crash recovery operation makes the next startup take longer.
The slow shutdown can take minutes, or even hours in extreme cases where substantial amounts of data are still buffered. Use the slow shutdown technique before upgrading or downgrading between MySQL major releases, so that all data files are fully prepared in case the upgrade process updates the file format.
Use innodb_fast_shutdown=2 in emergency or troubleshooting situations, to get the absolute fastest shutdown if data is at risk of corruption.

The result of a shutdown will leave InnoDB in one of these three states, two of them requiring some work during crash recovery at startup.
The reason for the answer I posted is the OS. In my opinion, the Linux OS and the right hardware provide a better environment for disk writing and flushing. I don't have that same confidence with Windows. My suggestions are just to give mysqld additional advantages in Windows.
InnoDB can holistically shutdown. My personal preference is to use innodb_fast_shutdown=0 so I don't worry about the redo logs during maintenenaces or worry about the OS.


I have to delete over 90 million records, do I remove or maintain the transaction's SQLWARNING? SQL Server: mysql  Ver 15.1 Distrib 5.5.65-MariaDB, for Linux (x86_64) using readline 5.1

OS: Slackware 14.1

That's 90 million old radius bank records I want to remove, leaving only those this month. The script would be this:

mysql --defaults-extra-file=/etc/my.cnf.d/.mylogin.cnf ${DB} <<EOFMYSQL
DELIMITER $$
USE ${DB} $$
DROP PROCEDURE IF EXISTS sp_monthly_table_backup $$
# tm: time column of table, tb: table
CREATE PROCEDURE sp_monthly_table_cleanup(IN tb varchar(30), IN tm varchar(30), OUT msg varchar(70))
        BEGIN

                ######## rollback block in case of errors ##########
                DECLARE EXIT HANDLER FOR 1062
                BEGIN
                        SET msg = CONCAT('FAILED: Routine failed for table ',tb,' | Duplicate keys error encountered ');
                        ROLLBACK;
                END;

                DECLARE EXIT HANDLER FOR SQLEXCEPTION
                BEGIN
                        # GET DIAGNOSTICS NOT SUPPORT IN MYSQL 5.5, ONLY >= MYSQL 5.6
                        # GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
                        # SET msg=CONCAT('FAILED: Routine failed for table ',tb,' | SQL State: ',@sqlstate,' | Errno: ',@errno,' | Msg: ',@text);
                        SET msg = CONCAT('FAILED: Routine failed for table ',tb,' | SQLException encountered ');
                        ROLLBACK;
                END;

                DECLARE EXIT HANDLER FOR SQLWARNING
                        BEGIN
                            SET msg=CONCAT('FAILED: Routine failed for table ',tb,'. SQLWarning encountered');
                            ROLLBACK;
                        END;
                  START TRANSACTION;
                      SET @str4=CONCAT('DELETE FROM ',tb,' WHERE ',tm,' < date_sub(NOW(), INTERVAL 1 MONTH)');
                      PREPARE stmt4 FROM @str4;
                      EXECUTE stmt4;
                      DEALLOCATE PREPARE stmt4;
                      SET msg=CONCAT('SUCCESS: routine successfully executed for table ',tb,' on ',NOW());
                      COMMIT;

       END $$
DELIMITER ;
EOFMYSQL

for t in "${!TABLES[@]}";
do
        echo ${t};
        echo ${TABLES[${t}]};
        mysql --defaults-extra-file=/etc/my.cnf.d/.mylogin.cnf ${DB} <<EOFMYSQL2 >> ${CLEARLOG}
        CALL sp_monthly_table_backup('${t}','${TABLES[${t}]}',@Msg);
        Select @Msg;
EOFMYSQL2

done

END=$(date +%s);
RUNTIME=$((END-START));

echo -e "Script finished on $(date -d @${END}) \n Runtime: ${RUNTIME} seconds" >> ${CLEARLOG};

STARTOPTRADACCT=$(date +%s);

echo -e "Starting optimization of table radacct on $(date -d @${STARTOPTRADACCT})" >> ${CLEARLOG};

mysql --defaults-extra-file=/etc/my.cnf.d/.mylogin.cnf ${DB} --execute "OPTIMIZE TABLE radacct";
ENDOPTRADACCT=$(date +%s);
RUNTIMEOPTRADACCT=$((END-START));

echo -e "Optimization of table radacct finished on $(date -d @${ENDOPTRADACCT}) \n Runtime: ${RUNTIMEOPTRADACCT} seconds" >> ${CLEARLOG};

While checking the result file, I noticed that delete was terminated because of a SQLWarning:

Starting script on Wed Jan  8 15:06:17 -03 2020
 Server timestamp: 1578506777
@Msg
FAILED: Routine failed for table radacct. SQLWarning encountered
Script finished on Wed Jan  8 22:50:03 -03 2020
 Runtime: 27826 seconds
Starting optimization of table radacct on Wed Jan  8 22:50:03 -03 2020
Optimization of table radacct finished on Thu Jan  9 01:22:13 -03 2020
 Runtime: 27826 seconds

Is it important that I keep this rollback to SQLWarning, or can I pull it out and still maintain data integrity?

UPDATE:  I just saw a warning in the database log, shown just as the script started. But did mysql go to all the trouble of deleting it first and then seeing this warning shown at the beginning of it and rolling it back?

200108 15:06:17 [ERROR] mysqld: Table './mysql/proc' is marked as crashed and should be repaired
200108 15:06:17 [Warning] Checking table:   './mysql/proc'

If you are deleting much of a table it is often better (faster, etc) to copy the table over without the 'old' rows:

CREATE TABLE new LIKE real;
INSERT INTO new
    SELECT * FROM real
        WHERE dt > NOW() - INTERVAL 1 MONTH;
RENAME TABLE real TO old,
             new TO real;
DROP TABLE old;   -- or dump it for archive purposes

Better yet, make that new table PARTITIONed by day so that you can daily do the very fast DROP PARTITION to get rid of the month-old data and REORGANIZE PARTITION to get a new partition for tomorrow.  Details in http://mysql.rjweb.org/doc.php/partitionmaint

If you need to "dump" or "archive" the month-old partition, see the discussion on "transportable tablespaces".  (Note: MySQL 5.6 has one method and newer versions have another.)

Details on other deletion techniques:  http://mysql.rjweb.org/doc.php/deletebig


Installation of SQL Server 2012 does not contain Virtual Interface Adapter? I have installed the entire Enterprise edition of SQL Server 2012 and have noticed that I do not have the Virtual Interface Adapter.

What is the cause of this?

The Virtual Interface Adapter has been deprecated and removed in SQL Server 2012.
Choosing a Network Protocol - SQL Server 2008 R2

The VIA protocol is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Configure Client Protocols - SQL Server 2012

Microsoft SQL Server supports client communication with the TCP/IP network protocol and the named pipes protocol. The shared memory protocol is also available if the client is connecting to an instance of the Database Engine on the same computer. There are three common methods of selecting the protocol.
•Configure all client applications to use the same network protocol by setting the protocol order in SQL Server Configuration Manager.
•Configure a single client application to use a different network protocol by creating an alias. For more information, see Create or Delete a Server Alias for Use by a Client (SQL Server Configuration Manager).
•Some client applications, such as sqlcmd.exe, can specify the protocol as part of the connection string. For more information, see Connect to the Database Engine With sqlcmd.