Registered Server window does not show Database Engine or Local Server Groups

In the folder %APPDATA%\Microsoft\Microsoft SQL Server\110\Tools\Shell you should find a file named RegSrvr.xml. That's the file where Management Studio stores local registered servers. If the file is missing or corrupt, SSMS won't display anything.

In this case, you can create a new file and paste this:

<?xml version="1.0"?>
<model xmlns="http://schemas.serviceml.org/smlif/2007/02">
  <identity>
    <name>urn:uuid:96fe1236-abf6-4a57-b54d-e9baab394fd1</name>
    <baseURI>http://documentcollection/</baseURI>
  </identity>
  <xs:bufferSchema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <definitions xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08">
      <document>
        <docinfo>
          <aliases>
            <alias>/system/schema/RegisteredServers</alias>
          </aliases>
          <sfc:version DomainVersion="1" />
        </docinfo>
        <data>
          <xs:schema targetNamespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
            <xs:element name="RegisteredServersStore">
              <xs:complexType>
                <xs:sequence>
                  <xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
            <xs:element name="ServerGroup">
              <xs:complexType>
                <xs:sequence>
                  <xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
            <RegisteredServers:bufferData xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08">
              <instances xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08">
                <document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:RegisteredServersStore xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:ServerGroups>
                        <sfc:Collection>
                          <sfc:Reference sml:ref="true">
                            <sml:Uri>/RegisteredServersStore/ServerGroup/AnalysisServicesServerGroup</sml:Uri>
                          </sfc:Reference>
                          <sfc:Reference sml:ref="true">
                            <sml:Uri>/RegisteredServersStore/ServerGroup/CentralManagementServerGroup</sml:Uri>
                          </sfc:Reference>
                          <sfc:Reference sml:ref="true">
                            <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</sml:Uri>
                          </sfc:Reference>
                          <sfc:Reference sml:ref="true">
                            <sml:Uri>/RegisteredServersStore/ServerGroup/IntegrationServicesServerGroup</sml:Uri>
                          </sfc:Reference>
                          <sfc:Reference sml:ref="true">
                            <sml:Uri>/RegisteredServersStore/ServerGroup/ReportingServicesServerGroup</sml:Uri>
                          </sfc:Reference>
                          <sfc:Reference sml:ref="true">
                            <sml:Uri>/RegisteredServersStore/ServerGroup/SqlServerCompactEditionServerGroup</sml:Uri>
                          </sfc:Reference>
                        </sfc:Collection>
                      </RegisteredServers:ServerGroups>
                    </RegisteredServers:RegisteredServersStore>
                  </data>
                </document>
                <document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore/ServerGroup/AnalysisServicesServerGroup</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:Parent>
                        <sfc:Reference sml:ref="true">
                          <sml:Uri>/RegisteredServersStore</sml:Uri>
                        </sfc:Reference>
                      </RegisteredServers:Parent>
                      <RegisteredServers:Name type="string">AnalysisServicesServerGroup</RegisteredServers:Name>
                      <RegisteredServers:ServerType type="ServerType">AnalysisServices</RegisteredServers:ServerType>
                    </RegisteredServers:ServerGroup>
                  </data>
                </document>
                <document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore/ServerGroup/CentralManagementServerGroup</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:Parent>
                        <sfc:Reference sml:ref="true">
                          <sml:Uri>/RegisteredServersStore</sml:Uri>
                        </sfc:Reference>
                      </RegisteredServers:Parent>
                      <RegisteredServers:Name type="string">CentralManagementServerGroup</RegisteredServers:Name>
                      <RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
                    </RegisteredServers:ServerGroup>
                  </data>
                </document>
                <document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:Parent>
                        <sfc:Reference sml:ref="true">
                          <sml:Uri>/RegisteredServersStore</sml:Uri>
                        </sfc:Reference>
                      </RegisteredServers:Parent>
                      <RegisteredServers:Name type="string">DatabaseEngineServerGroup</RegisteredServers:Name>
                      <RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
                    </RegisteredServers:ServerGroup>
                  </data>
                </document>
                <document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore/ServerGroup/IntegrationServicesServerGroup</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:Parent>
                        <sfc:Reference sml:ref="true">
                          <sml:Uri>/RegisteredServersStore</sml:Uri>
                        </sfc:Reference>
                      </RegisteredServers:Parent>
                      <RegisteredServers:Name type="string">IntegrationServicesServerGroup</RegisteredServers:Name>
                      <RegisteredServers:ServerType type="ServerType">IntegrationServices</RegisteredServers:ServerType>
                    </RegisteredServers:ServerGroup>
                  </data>
                </document>
                <document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore/ServerGroup/ReportingServicesServerGroup</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:Parent>
                        <sfc:Reference sml:ref="true">
                          <sml:Uri>/RegisteredServersStore</sml:Uri>
                        </sfc:Reference>
                      </RegisteredServers:Parent>
                      <RegisteredServers:Name type="string">ReportingServicesServerGroup</RegisteredServers:Name>
                      <RegisteredServers:ServerType type="ServerType">ReportingServices</RegisteredServers:ServerType>
                    </RegisteredServers:ServerGroup>
                  </data>
                </document>
                <document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore/ServerGroup/SqlServerCompactEditionServerGroup</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:Parent>
                        <sfc:Reference sml:ref="true">
                          <sml:Uri>/RegisteredServersStore</sml:Uri>
                        </sfc:Reference>
                      </RegisteredServers:Parent>
                      <RegisteredServers:Name type="string">SqlServerCompactEditionServerGroup</RegisteredServers:Name>
                      <RegisteredServers:ServerType type="ServerType">SqlServerCompactEdition</RegisteredServers:ServerType>
                    </RegisteredServers:ServerGroup>
                  </data>
                </document>
              </instances>
            </RegisteredServers:bufferData>
          </xs:schema>
        </data>
      </document>
    </definitions>
  </xs:bufferSchema>
</model>

This is what an empty registered servers file looks like in SSMS 2012. It should bring your SSMS back to normal.


Performance and correctness of selecting only most recent entries across joined tables

Something like this:

SELECT purchases.id AS purchase_id, thing_id, purchase_date, name, predicted_replace_days, product_image, barcode
FROM (
    SELECT thing_id, MAX(purchase_date) pd
    FROM purchases
    GROUP BY thing_id
) AS sub
JOIN purchases ON purchases.id = sub.thing_id and sub.pd=purchase_date
INNER JOIN things on thing_id = things.id
WHERE user_id = 1


What useful information can I get from ASH report which I cannot get from AWR report?

AWR(Automatic Workload Repository Reports) takes snapshots of the database in specified intervals(one hour is default) and stores that data for later analysis. For example continuous improvement of the database work.

ASH(Active Session History) has information about current active sessions, and it gather data every second. If you are experiencing sudden database issues, you should use ASH.

More on this subject
http://docs.oracle.com/cd/E11882_01/server.112/e16638/autostat.htm#PFGRF94186


Foreign Key constraint on fixed value field - Ever appropriate?

The proper thing to do now would probably to make one table for each subgroup ...

Probably.

  It is though very unlikely that I ever further need to refer to the subgroups...

You probably said something very similar when you made that first questionable design decision.  There's a reason that normalization never asks the question, "How often will I need to refer to whatever?"

If you don't enjoy fixing this kind of problem over and over, do it right today and get on with more useful and fun work.


None of the system tables (that is, nothing in the INFORMATION_SCHEMA database) exist that has that kind of information recorded anywhere. In other words, there is no native mechanism to put any timestamps on column changes. Any time that

one or more columns change in any row
a new row is added
an old is deleted
an ALTER TABLE of any kind

the UPDATE_TIME column in INFORMATION_SCHEMA.TABLES is updated.

SUGGESTION

This may be a bitter pill to swallow, but you must do the following:

Create a custom table that records

primary key
column name
the old value of that column
the new value of that column
timestamp of the change

Create BEFORE UPDATE trigger to compare old and new columns and record it if they do not match

EPILOGUE

This may not be so bad if you are customizing for a few tables and a few columns.


Installing SQL Server 2000 32-bit on Windows Server 2008 x64?

I'm not sure why you're installing SQL Server 2000 anywhere in the year 2012, but no, there shouldn't be any technical issue other than the ones you've identified and the fact that it is nowhere near supported.

In any case, there is no 64-bit version of SQL Server 2000 - there is only 32-bit for x86 and 64-bit for IA. I assume your Windows Server 2008 box isn't Itanium.


Trying to create a collation on Postgres 9.6; a nonsensical error message

I found a workaround: the bug happens only if an earlier attempt to create a collation has failed. After a failure, the bug seems to keep happening until the server process is restarted. So to create the collation, you have to get it right from the start, or restart the process to try again. To restart a Docker container, use

docker restart $DB_CONTAINER_NAME_HERE


Oracle AWR shows missing stats for SQL ID as top elapsed statement

I could think of eviction from shared pool, like your guess, or the statement running with statistics_level=basic. The statistics_level can be dynamically set on a per-session basis.

  Setting the STATISTICS_LEVEL parameter
  to BASIC disables the collection of
  many of the important statistics
  required by Oracle Database features
  and functionality, including:

*

  Automatic Workload Repository (AWR) Snapshots


What settings to use to export mysql table rows that I'll be importing to another table with data using phpmyadmin

You have two possibilities.

use the export at the botton of the table, after selecting you data with the correct data

see

use SELECT INTO OUTfile

LIKE

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table WHERE n= 1;

Please check the limitations in the link for secure_file_priv

You can after import it via phpmyadmin or use LOAD DATA Statement


Extended properties are not copied using SMO

yes extended properties are a bit "special" to handle, fortunately Phil Factor has written a long article with helper functions which you can find here :- PhilFactorsArticle


What all files are required for restoration process of SQL Server in Full + Diff + Transactional backup policy

or did i interpret it wrong and I would need last full backup file + differential 2 + log file 2.1 + log file 2.2....

I have not read the blog but above one seems correct approach. Differential backup is mainly there to reduce Recovery Time Object(RTO) it reduces the time taken to bring database up and running after disaster strikes.

Example:

If you have

Daily full backup
Every 4 hr differential backup
Every 15 mins log backup

Suppose your database crashes today at 5 PM.

The restore sequence would be

First restore Today's full backup with no recovery then 4 PM differential backup with no recovery and the  all log backup with no recovery, just the last log backup with recovery to bring database online.

If in above scenario you dont take diff backup then restore sequence would be

Todays full backup and then all log backups taken after this full backup witn no recovery just the last backup with recovery to bring DB online.

  What is contained in differential backup, is it just mdf + ndf backup or ldf + mdf + ndf backup since last full backup was taken?

You added above part of question after my answer so I will include this as well.Differential backup includes are all the data extents that have changed since the last full backup. Yes they also include (if required) few amount of transaction log to bring database into consistent state after restore is done. For more details read this


Access to the path 'c:\some\path' is denied for MSSQL CLR

I have permissions to the database, I'm dbo. I'm an admin on the server. I have permissions in the file system.

None of that matters, typically. Unless you (or whoever coded the SQLCLR methods) implemented Impersonation, then the security context used for external operations is that of the service account running SQL Server (similar to xp_cmdshell behavior). It is that account that needs permission to the path(s) that you are trying to access.

For the sake of completeness regarding file access permissions:

For local (on the box) access, it is as simple as either

the service account (default behavior) for the Database Engine (i.e. MSSQLSERVER or MSSQL$InstanceName) service needing permission, or
if Impersonation has been implemented in the code

and a the login executing the code is a Windows Login, not a SQL Server login, then it is that Windows account that needs permission
but a SQL Server login is being used, the external access is still done as the Database Engine service account

For remote access (shared drive), constrained delegation might need to be set up (via Active Directory; including SPNs). Good 'ol Kerberos double-hop issue. In this case, you would see a difference between logging into SQL Server from another computer, other than the server it is running on vs logging directly onto the server running SQL Server and then connecting to the local SQL Server instance.

Keep in mind that "DENY"s take precedence over "GRANT"s (just like with SQL Server permissions).

In order to determine if the account used for external access actually has the necessary permission to the folder(s) and/or file(s):

Go to the "Properties" of the path in question (the specific file or folder reporting the error)
Go to the "Security" tab
Click the "Advanced" button
Go to the "Effective Access" tab
Click the "select a user" link
Enter in the fully account name (e.g. NT Service\MSSQLSERVER)
Click the "OK" button
Click the "View effective access" button
Does that account have access to that resource?

Are there any DENY permissions anywhere in the path that you are trying to access?

ALSO If all the code is doing is file system stuff, then most likely you don't need to have the assembly marked as UNSAFE and it should instead be EXTERNAL_ACCESS. Not too many file system operations should require UNSAFE. One of them is getting a list of fixed drives, but not sure of what else.


Very slow update with subquery (different tables)

The subquery will be executed for each row from the outer query, and no index will be used. In addition, 0.6 sec is "relatively" long. For example, if you have 1000 rows in domain table, then expect more them 600 sec (10 minutes).

In the second query, MySQL is able to use the index on 'domain'. This is a feature of using "IN" instead of subquery.


This is not possible: the display settings can not be specified per column.

If you set the font to one which definitely supports relevant Unicode code points, you can use these characters instead of relying on a legacy symbols font, while still having normal characters available.

For example "Arial Unicode MS" supports CHECK MARK (U+2713) and HEAVY BALLOT X (U+2718).


What is the difference between the user `dbo` and the owner of the database stored in sys.databases

I had always thought the user dbo was the actual owner of the database.

That is (or at least should be) correct. The name "dbo" of that User never changes, yet the underlying SID does depending on who created the database, or who it was set to be via sp_changedbowner (up though, and including, SQL Server 2005) or ALTER AUTHORIZATION (starting with SQL Server 2008).

In all three of those cases, the record in sys.databases is also changed so that they are kept in sync. However, when restoring a Database, either from another system or from the same Instance but from a DB that was backed-up / detached prior to one of those 2 SQL commands being executed to change the owner, then upon RESTORE or attach, there will be a mismatch between the owner_sid column in sys.databases and the "dbo" sid in sys.database_principals in that DB.

As far as I am aware of, the record in sys.database_principals in each DB is the real owner, and the owner_sid column in sys.databases is a matter of record-keeping / convenience (similar to denormalization; without sys.databases the system would need to make separate queries across all DBs to get that info, each time requested!) and security. One thing it is used for is to identify a potentially harmful / invalid restored / attached DB is those records do not match. Attempting to access SQLCLR Assemblies marked as either EXTERNAL_ACCESS or UNSAFE won't load if one has chosen to go the less secure route of enabling TRUSTWORTHY as that relies upon the "dbo" SID as it needs to match a Login that has either the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission. And when there is a mismatch in the SID between those two system catalog views, it can't be determined which one to use, and used as a red-flag for there being a potential security issue. In fact, I test for this condition in the installation script for SQL# to alert someone to make the appropriate change, just so that they don't have to waste time hunting it down in case SQL Server complains about it at some point.


Consider SQLite.  It is lightweight, reliable, and well proven in production.  In fact, the creators claim it is the most widely deployed database in the world.

It works well as a data interchange format as well as a database, using tools like sftp to sync files between servers.  In addition, developers can easily work with SQLite files in just about any stack.


Oracle Data Pump Import imports no records

I suspect this may have something to do with you starting impdp as sysdba. Please see the note in http://docs.oracle.com/database/121/SUTIL/dp_export.htm#SUTIL824:

"Do not start Export as SYSDBA, except at the request of Oracle technical support. SYSDBA is used internally and has specialized functions; its behavior is not the same as for general users."

Try your command as system.


Why does MySQL (InnoDB) table get faster after OPTIMIZE TABLE, but then don't work?

OPTIMIZE rebuilds the table.  This (for InnoDB) squeezes out some of the fragmentation and wasted space.  This is unlikely to make a noticeable difference in any query.

Also, OPTIMIZE does an ANALYZE.  This has a chance of changing the statistics, thereby leading to a different (better or worse) EXPLAIN plan.

Since ANALYZE is much faster (on InnoDB) than OPTIMIZE, just do the ANALYZE.

Various non-ANALYZE actions cause an ANALYZE to be done.

ANALYZE randomly probes the BTrees, gathering stats.  Sometimes the resulting stats are poor.  There is effectively no way to prevent this from happening.  Several partial hacks have been created over the years; 5.6.7 gets close to eliminating this problem with ANALYZE. Here's one of them: http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_stats_persistent_sample_pages


Availability groups multiple instance, multiple listeners

When I connect to AVG02 using the name AvgList02 it connects me to AVG01 although I'm not using its listener name. Where is the issue, and how to fix it?

Consider port configuration play key role in general. In your case most probably following might happened.

Both availability groups's primary replica is same node (DB01 or DB02)
There must be SQL Browser service running in each SQL Server
Default SQL ports (1433) must have been assigned for listeners

When client tries to connect on listener name the IP would be bounded based on DNS response (in multi sub-net first successful IP bound) which lead client connection to pointing to primary replica.

Connection from AvgList02 to AG01 (AVG01):
When the request hitting primary replica (after IP bound from listener name), next part is port (consider wrong port as per point 3) the connection would be established to the service which is running on particular port number (1433).

Connection from AvgList02\FBG to AG02 (AVG02):
Similar to above case (after IP bound from listener name), since the instance name (FBG) mentioned in connection SQL Browser would take action to bound the connection with respective SQL Service that match with instance name mentioned in connection.

Summary:
I personally recommend to use custom port for SQL Server especially when multiple instances running in same server, and disable SQL Browser service, let every connection hard-code custom port number in connection string to connect respective instance without depending on SQL Browser service.

For availability group implementation, consider custom ports and avoid port conflict in following sections:

SQL ports (Default: 1433)
Endpoint ports (Default: 5022)
Listener ports (no default)


Incorrect result when you execute a query that uses WITH RECOMPILE option in SQL Server 2012

I believe your question is, "Is the hotfix in https://support.microsoft.com/kb/2965069 included in Service Pack 2?" The answer as of July 28, 2014 is no. Watch that KB article to see when it changes - I use http://www.WatchThatPage.com for stuff like this.


How can I count different types in a column in sql?

You can use the PIVOT operator, like this:

SELECT
   QID, ISNULL([1],0) AS [Type(1)], ISNULL([2],0) AS [Type(2)], ISNULL([3],0) AS [Type(3)]
FROM
   (SELECT QID, Type, COUNT(*) AS row_count
   FROM MyTable
   GROUP BY QID, Type) AS t
PIVOT
   (SUM([row_count]) FOR Type IN ([1],[2],[3])) AS PivotTable;

BOL article about using PIVOT and UNPIVOT.

SQL Fiddle with demo


Your MDF is actually corrupt, based on that error message. This means you can't attach it.

You could create a database with the same name, substitute MDF (and rename LDF), and hope it comes online or at least suspect to start recovering it.

Otherwise, you'll need to find a 3rd party tool that understands MDF files to get data back.

Or just restore from your latest backup...?


Determining the Actual Server Create Date

determine the actual create date for SQL Server

This can be found using

SELECT  createdate as SQL_SERVER_INSTALLATION_DATE
FROM    sys.syslogins
where   sid = 0x010100000000000512000000

Note : sys.syslogins is deprecated, so using sys.server_principals will give you the same results.

So you can use :

--Using  sys.server_principals
SELECT SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + '
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
    RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
 SERVERPROPERTY ('Collation') AS Collation
      ,create_date as 'SQL Server Installation Date'
FROM sys.server_principals
WHERE name='NT AUTHORITY\SYSTEM'

--Using sys.server_principals
SELECT create_date as 'SQL Server Installation Date'
FROM sys.server_principals
WHERE name='NT AUTHORITY\SYSTEM'

  Actual creation of database

This can be found by reading the boot page of the database.

Note: DBCC PAGE is undocumented.  I have not seen any side effects of using it, but since it is undocumented, use it with caution.

DBCC TRACEON(3604)
GO
 -- Actual creation time of database is stored in the boot page of the database
 -- This is even retained after restore or detach/attach of the database.
 --- Look for dbi_crdate.
DBCC PAGE('database_name_Goes_here', 1, 9, 3)
GO

DBCC TRACEOFF(3604)
GO

Even better using DBCC PAGE  WITH TABLERESULTS

CREATE TABLE PageData
(
      ParentObject VARCHAR(1000)NULL,
      Object VARCHAR(4000)NULL,
      Field VARCHAR(1000)NULL,
      ObjectValue VARCHAR(MAX)NULL,
)
GO

INSERT INTO PageData (ParentObject, Object, Field, ObjectValue)
EXEC ('DBCC PAGE (''AdventureWorks2008R2_copy_August_2014'',1, 9, 3) WITH TABLERESULTS')
GO

select DatabaseName = (select ObjectValue from PageData where field ='dbi_dbname')
, DatabaseCreateDate = ( select ObjectValue from PageData where field = 'dbi_crdate')


SQL Server MAXDOP on VMWare Virtual Servers

Your colleague is wrong. SQL Server will use parallelism whether the cores are virtual or not.

In fact the VMWare SQL Server best practices guide you link to explicitly states:

  There is a fair amount of misconception and incorrect advice on the
  Internet regarding  the values of these configurations in a virtual
  environment. When low performance is  observed on their database, and
  CXPACKET is high, many DBAs decide to disable  parallelism altogether
  by setting MAXDOP value to one (1). This is not recommended  because
  there might be large jobs that will benefit from processing on
  multiple CPUs.  The recommendation instead is to increase the CTFP
  value from five seconds to  approximately 50 seconds to make sure only
  large queries run in parallel. Set the  MAXDOP according to
  Microsoft’s recommendation for the number of cores in the  VM’s NUMA
  node (no more than eight).

I will however note that the writer of the VMWare best practices guide is confused. The value is not in seconds but in "cost" (also known as query bucks)


See who has access to databases via AD group membership

just seeing which groups the user is in that also have a login on the instance would be nice

You can accomplish this using following code:

exec xp_logininfo 'dom\acc', 'all';

Or you can impersonate that login and use sys.login_token like this:

exec as login = 'dom\acc';
select distinct name
from sys.login_token
where principal_id > 0
      and type = 'WINDOWS GROUP';
revert;


I can't comment under the question but i can tell you what i did. I had a similar issue like that due to a break in the restore chain. I tried manually editing the monitor tables to "hack" the replaying of restore logs so that the Alert Job won't throw those alerts but i ended up digging a bigger hole for myself.
Note that even though you are seeing the LS_Restore job giving success messages that doesn't mean it is actually doing a Restore. Click on the View History and look for something similar to this: Number of log backup files restored: 0. (Note that this could mean there's no data to restore or there's data to restore but the log file next in line to restore is missing). I was getting a false positive (wrong successful LS_Restore) but the log files restored were always 0 even though it couldn't find the log file needed (you would see a message saying: Can't find previous log shipping file to be restored).

As a test ensure that there's committed activity in the production database then go to the DR and check the view history for LS_Restore. You want to see something like: Number of log backup files copied: 1. If for 6 restores you don't see a change from "0" files restored then i suggest:

Restore your Secondary database using a full + differential back up of the production
OR
Re-initialize the log shipping process (delete current log shipping setup and start over the process)


Oracle Jobs: Can I create a job that will not overlap with itself?

Using dbms_job, you're never going to have two instances of a job running at the same time.  If I do something like this with an interval that is 1 minute in the future and a procedure that takes 3 minutes to run

declare
  l_jobno pls_integer;
begin
  dbms_job.submit( l_jobno,
                   'begin proc_that_takes_3_minutes(); end;',
                   sysdate,
                   'sysdate + interval ''1'' minute' );
end;
/

the interval expression is only evaluated after the prior run finishes.  There won't be 3 instances of the job running simultaneously.  The job will just run once every 4 minutes.

This is why you frequently hear people about jobs "drifting" over time-- a job is scheduled to run at 4 AM with an interval of "sysdate + 1" so every day it runs at a slightly later time.  If the job takes 1 minute to run, after 2 months, it's now running just after 5 AM.  And that's why you'd generally want your interval to be something like "trunc(sysdate+1) + interval '4' hour" if you want to run every day at 4 AM.

If you want to provide additional protection in case, say, someone tries to run the procedure in a different session at the same time that the job is running, you can put a dbms_lock.request call in the procedure that tries to acquire a user-defined lock before doing the actual work.

create or replace procedure what_the_job_does
as
  l_status integer;
begin
  l_status := dbms_lock.request( 'MY_LOCK_NAME' );
  if( l_status in (1,4) )
  then
    <<do actual work>>
  end if;
end;


Why cannot connect remote report server instance?

If you're using a different version of Reporting Service Configuration Manager, then it wont pick it up.

SSRS Config Manager 2016 doesn't see my local SSRS 2012 instance and vice-versa.
Hope that helps.


Restarting Galera MySQL after proper Shutdown

I am not quite certain if I understand your question completely, but let's consider two scenarios:
After an orderly, manual shutdown of the cluster, you will know which node you shut down last. Just begin the startup sequence with that node, and bring them up manually one after the other.
But suppose your shutdown was accidental, due to a power outage or similar. You will not know which node considers itself "the last to go down". However, you can check the file grastate.dat on the individual cluster members and search for the flag safe_to_bootstrap: 1. If there is exactly one node that has this flag set to 1, boot that one first.
Otherwise, as described in https://galeracluster.com/2016/11/introducing-the-safe-to-bootstrap-feature-in-galera-cluster/, start mysqld with the --wsrep-recover switch, compare the UUIDs in the "Recovered position" line in the output and set the safe_to_bootstrap flag to 1 by hand on the node with the highest UUID. Then, proceed as above.
Those were the procedures for manually starting the nodes. I'm asking about a way to automate this in a more robust way in a different question.


CROSS JOIN with later PIVOT going rather slowly

I didn't find any way to speed up the PIVOT operation - it's just a big operation that takes time with a lot of data.  The one thing I did that seemed to speed things up a small amount (perhaps the addition concatenation operator implemented more efficiently in SQL Server) was to change CONCAT('Downloads_', usr.[OrganizationName]) to 'Downloads_' + usr.[OrganizationName].  The rest seems to be pretty much optimal.


Save Transaction: Is the name local to the stored procedure or not?


MySQL Host 'host_name' is blocked - Which Database?

I am not sure that you can get the database, but you can identify the user with one of the following methods:

The general log
The error log (log_warnings = 2)
An extra patch called user statistics (included by default in Percona and MariaDB)
An audit plugin like McAfee's

The first three are mentioned on this article on MySQL performance blog.

Sadly, visilibity on the host cache was only added until 5.6 on performance_schema.host_cache.

As a temporary workaround, you would be required to set max_connect_errors to a large value instead of 10, and set MAX_CONNECTIONS_PER_HOUR and MAX_USER_CONNECTIONS per user to an appropriate value (you should be using different users if you are not, and/or different DNS names).


Circular dependency between 2 table with one to one and one to many relationship

In SQL Server, I might accomplish this using a cross reference table between "companies" and "users" where only a single row can be an owner for a given company, via the presence of a unique non-clustered index.

For example:

USE tempdb; --create this test in tempdb

IF OBJECT_ID(N'dbo.CompaniesUsers', N'U') IS NOT NULL
DROP TABLE dbo.CompaniesUsers;

IF OBJECT_ID(N'dbo.Users', N'U') IS NOT NULL
DROP TABLE dbo.Users;

IF OBJECT_ID(N'dbo.Companies', N'U') IS NOT NULL
DROP TABLE dbo.Companies;
GO

CREATE TABLE dbo.Users
(
    UserID int NOT NULL
        CONSTRAINT PK_Users
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , UserName varchar(100) NOT NULL
        CONSTRAINT UQ_Users_UserName
        UNIQUE
);

CREATE TABLE dbo.Companies
(
    CompanyID int NOT NULL
        CONSTRAINT PK_Companies
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , CompanyName varchar(100) NOT NULL
        CONSTRAINT UQ_Companies_CompanyName
        UNIQUE
);

CREATE TABLE dbo.CompaniesUsers
(
    CompanyID int NOT NULL
        CONSTRAINT FK_CompanyUsers_CompanyID
        FOREIGN KEY
        REFERENCES dbo.Companies(CompanyID)
    , UserID int NOT NULL
        CONSTRAINT FK_CompanyUsers_UserID
        FOREIGN KEY
        REFERENCES dbo.Users(UserID)
    , CompanyOwner bit NOT NULL
        CONSTRAINT DF_CompanyUsers_Owner
        DEFAULT ((0))
);

CREATE UNIQUE NONCLUSTERED INDEX IX_CompanyUsers
ON dbo.CompaniesUsers(CompanyID, CompanyOwner)
INCLUDE (UserID)
WHERE CompanyOwner = 1;

Here I'll insert some test data to show how this works.  First, two "users":

INSERT INTO dbo.Users (UserName)
VALUES ('owner 1')
    , ('owner 2');

And, now, a "Company":

INSERT INTO dbo.Companies (CompanyName)
VALUES ('Company 1');

If I attempt to make both users an owner of the company, the statement fails:

INSERT INTO dbo.CompaniesUsers (CompanyID, UserID, CompanyOwner)
VALUES (1, 1, 1)
    , (1, 2, 1);

  Msg 2601, Level 14, State 1, Line 62
  Cannot insert duplicate key row in object 'dbo.CompaniesUsers' with unique index 'IX_CompanyUsers'. The duplicate key value is (1, 1).

However, if I only allow one of the users to be the "owner", then the insert works:

INSERT INTO dbo.CompaniesUsers (CompanyID, UserID, CompanyOwner)
VALUES (1, 1, 1)
    , (1, 2, 0);

SELECT *
FROM dbo.CompaniesUsers

╔═══════════╦════════╦══════════════╗
║ CompanyID ║ UserID ║ CompanyOwner ║
╠═══════════╬════════╬══════════════╣
║         1 ║      1 ║            1 ║
║         1 ║      2 ║            0 ║
╚═══════════╩════════╩══════════════╝

If you need to ensure a "user" can own at most a single "company", then this unique, non-clustered index, should do the trick:

CREATE UNIQUE NONCLUSTERED INDEX IX_CompanyUsers_1
ON dbo.CompaniesUsers(UserID, CompanyOwner)
INCLUDE (CompanyID)
WHERE CompanyOwner = 1;

I originally missed the requirement that a "user" can only be a member of a single "company".  With that in mind, you'd need to add this index (the "IX_CompanyUsers_1" index would be unnecessary):

CREATE UNIQUE NONCLUSTERED INDEX IX_CompanyUsers_2
ON dbo.CompaniesUsers(UserID)


Determine least number of columns required for unique (clustered) index

I don't know how useful this will prove to be as I've not tested it out thoroughly, but you should be able to utilize Statistics Density Vector values to see what columns (and column combinations) will provide a higher likelihood of unique values.  The script listed below basically pulls all the Density Vector values for all stats on a given table.  This is not complex as it looks for the lowest Density Vector with the lowest Average Record Length.  Whatever records appear first in this result set should be your best candidates for unique columns.

OF NOTE: This only evaluates statistics that exist, so you may need to create multi-column statistics to fill in any gaps as auto-generated stats are limited to single columns.

Why is this better than the count approach?

You can specify sample rates with statistics which should reduce analysis times on larger tables, and the table scan (and locking that will come with it) when running the various COUNT aggregations also won't occur when creating the stats against a busy system.

Here's the script:

SET NOCOUNT ON

-- provide table name you wish to analyze
DECLARE @tableName NVARCHAR(256) = 'dbo.TABLE__NAME'
      , @statsName NVARCHAR(128), @dSQL NVARCHAR(1024)

DECLARE @stats_densityVector TABLE
(
    StatName    NVARCHAR(1024),
    AllDensity  REAL,
    AvgLength   BIGINT,
    Columns     NVARCHAR(1024)
)

DECLARE statsHeader CURSOR
FOR
    SELECT  name AS stats_name
    FROM    sys.stats s
    WHERE   s.object_id = OBJECT_ID(@tableName)

OPEN statsHeader
FETCH NEXT FROM statsHeader
INTO    @statsName

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @dSQL = 'DBCC SHOW_STATISTICS (''' + @tableName + ''', ' + @statsName + ') WITH DENSITY_VECTOR, NO_INFOMSGS'
    INSERT INTO @stats_densityVector (AllDensity, AvgLength, Columns)
    EXEC (@dSQL)

    UPDATE @stats_densityVector
    SET StatName = @statsName
    WHERE StatName IS NULL

    FETCH NEXT FROM statsHeader
    INTO    @statsName
END

CLOSE statsHeader
DEALLOCATE statsHeader

SET NOCOUNT OFF

SELECT  OBJECT_SCHEMA_NAME(s.object_id) + '.' + OBJECT_NAME(s.object_id) AS table_name
        , dv.*
        , sp.rows_sampled
        , sp.rows
        , (1.0 * sp.rows_sampled) / (1.0 * sp.rows) AS sample_pct
FROM sys.stats s
        CROSS APPLY sys.dm_db_stats_properties (s.object_id, s.stats_id) sp
        INNER JOIN @stats_densityVector dv
            ON dv.StatName = s.name
WHERE s.object_id = OBJECT_ID(@tableName)
ORDER BY dv.AllDensity, dv.AvgLength

Here's a dbfiddle.uk example of how this would work on a contrived example.


mysql - query records to fetch by time stamp

Since the seconds are from 0 (1970-01-01 00:00:00 UTC), you should look for every multiple of 60

SELECT * FROM mytable WHERE MOD(TimeStamp,60)=0;

or if TimeStamp is indexed, you can do

SELECT T.* FROM
(SELECT TimeStamp FROM mytable WHERE MOD(TimeStamp,60)=0) M
INNER JOIN mytable T USING (TimeStamp);

Give it a Try !!!

SUGGESTION #1

You should store the timestamp of the minute and index it

ALTER TABLE mytable ADD COLUMN MinuteTimeStamp AFTER TimeStamp;
UPDATE mytable SET MinuteTimeStamp = TimeStamp - MOD(TimeStamp,60);
ALTER TABLE mytable ADD INDEX MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey);

Then, you can do MIN aggregation on MinuteTimeStamp.

SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp;

and use it get those records

SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);

It was tactfully pointed out that triggers would degrade performance

Perhaps doing INSERTs like this may help

INSERT INTO mytable (UniqueKey,TimeStamp,MinuteTimeStamp) VALUES
(
    uniquevalue,
    UNIX_TIMESTAMP(NOW()),
    UNIX_TIMESTAMP(NOW() - INTERVAL SECOND(NOW()) SECOND)
);

SUGGESTION #2

Since you have over 1000 columns (Ugh), perhaps a table of those minute timestamps would be better.

CREATE TABLE MinuteKeys
(
    MinuteTimeStamp INT UNSIGNED NOT NULL,
    UniqueKey INT UNSIGNED NOT NULL,
    PRIMARY KEY (UniqueKey)
    KEY MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey)
) ENGINE=MyISAM;
ALTER TABLE MinuteKeys DISABLE KEYS;
INSERT INTO MinuteKeys SELECT TimeStamp - MOD(TimeStamp,60),UniqueKey FROM mytable;
ALTER TABLE MinuteKeys ENABLE KEYS;

Then, you could use that table for the aggregation

SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM MinuteKeys GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);

EPILOGUE

Other suggestions are possible but you should really consider normalization of the table

See my post Too many columns in MySQL as to why


Escaping a single quote in SQL Server 2005

I think you just meant:

select supplierid from dbo.suppliers
where companyname ='Grandma Kelly''s Homestead';

(In other words, there was only one apostrophe to replace.)


If you do not specify an order by for the result set of your first query then your data is not guaranteed to be ordered.

It does appear that you want to order by the minimal id field in your table based on PRIMARY KEY CLUSTERED ( [id] ASC).

One way would be by using a CTE and MIN(id)

WITH CTE
AS
(
SELECT route_id,fixcode,fixdescription, MIN(id) as minid
FROM route_fixcodes
WHERE route_id = 995063
GROUP BY route_id,fixcode,fixdescription
)
SELECT route_id,fixcode,fixdescription
FROM CTE
Order by minid;

Test data

CREATE TABLE #route_fixcodes( [id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL ,route_id int,fixcode int,fixdescription nvarchar(255));

INSERT INTO #route_fixcodes(route_id,fixcode,fixdescription)
VALUES(995063,100,'Issue_Observed'),(995063,100,'Issue_Observed'),(995063,137,'Swap Altice One Pack')
,(995063,137,'Swap Altice One Pack'),(995063,247,'Defective CPE Equip.'),(995063,247,'Defective CPE Equip.')
,(995063,112,'outside coax repair'),(995063,112,'outside coax repair')

Result

route_id    fixcode fixdescription
995063  100 Issue_Observed
995063  137 Swap Altice One Pack
995063  247 Defective CPE Equip.
995063  112 outside coax repair


MySQL / MariaDB Get last 7 days summarized and with avarages calculated

First, a very nice first post. Both ddl and sample data makes it easy to recreate the setup. Second, all you have to do with your query is to add the date dimension:

SELECT plant_id, date(created_at) as day, avg(temperature)
FROM plant_data
WHERE created_at >= '2020-02-15 00:00:00'
  AND created_at <= '2020-02-17 00:00:00'
GROUP BY plant_id, date(created_at);

You might also be interested in the BETWEEN predicate:

SELECT plant_id, date(created_at) as day, avg(temperature)
FROM plant_data
WHERE created_at BETWEEN '2020-02-15 00:00:00'
                     AND '2020-02-17 00:00:00'
GROUP BY plant_id, date(created_at);

I haven't checked, but there is a risk that day is a reserved word. In addition I prefer to add more meaning to my identifiers, so I would possibly choose:

SELECT plant_id, date(created_at) as created_date, avg(temperature)
FROM plant_data
WHERE created_at BETWEEN '2020-02-15 00:00:00'
                     AND '2020-02-17 00:00:00'
GROUP BY plant_id, date(created_at);

As a complement to posting ddl and insert data you can also use - for example - Fiddle. There are more sites like this, and I here choosed this one for no particular reason.


I want to get first,second and n th occurent of value in sql database?

You can use ROW_NUMBER to count it. Here is more about this

CREATE TABLE Table1
    ([id] int, [attribute] varchar(11))
;

INSERT INTO Table1
    ([id], [attribute])
VALUES
    (1, 'ICT'),
    (2, 'ENGINEERING'),
    (3, 'ICT'),
    (4, 'ENGINEERING'),
    (5, 'ENGINEERING')
;

SELECT
  id,attribute
  ,ROW_NUMBER()OVER(PARTITION BY Attribute ORDER BY id) as Occurence
FROM
  Table1
ORDER BY id;

output:

id  attribute   Occurence
1   ICT         1
2   ENGINEERING 1
3   ICT         2
4   ENGINEERING 2
5   ENGINEERING 3

dbfiddle here


PostgreSQL isn't using indices in FULL OUTER JOIN

When combining two tables with FULL OUTER JOIN all  rows are included in the result. Typically, it doesn't make sense to use indexes for the joining columns. That's why attempts with indexes on (ts_minute, user_id, campaign_id, host_id) will be fruitless. It just so happens that the first column also fits the relevant predicate on ts_minute. See below.

The query also returns substantially fewer rows with an [INNER] JOIN, which explains some of the performance difference - but certainly not factor 10.000. Your updated info confirms that fewer rows are returned (220 vs. 330) but that's still not the whole story. The difference could be much bigger before aggregation.

Your predicate on ts_minute is the better candidate for index support. There are two indices that would seem applicable for the predicate ts_minute > now() - interval '5 min' (pending information on actual data types). But the appearance is deceitful: The resulting column ts_minute after the FULL [OUTER] JOIN is a combination of the columns in both underlying tables. It's like an expression that's not sargable and cannot use indices on ts_minute on the underlying tables.

You have to apply the predicate before the FULL [OUTER] JOIN to allow index support. That's what you already stumbled upon in your added test with two separate sub queries. You'll find index (only) scans or probably bitmap index scans if you inspect the EXPLAIN output.

Since you GROUP BY user_id, campaign_id, it is irrelevant whether you add the columns ts_minute and host_id to the join condition in this query. ts_minute is only necessary for the WHERE condition.

SELECT user_id, campaign_id
     , i.sum_cost, b.sum_bids_internal, b.sum_eligible_requests
FROM  (
   SELECT user_id, campaign_id
        , sum(cost) AS sum_cost
   FROM   impr_summary_test
   WHERE  ts_minute > now() - interval '5 min'
   GROUP  BY 1, 2
   ) i
FULL JOIN (
   SELECT user_id, campaign_id
        , sum(bids_internal) AS sum_bids_internal
        , sum(eligible_requests) AS sum_eligible_requests
   FROM   bid_summary_test
   WHERE  ts_minute > now() - interval '5 min'
   GROUP  BY 1, 2
   ) b USING (user_id, campaign_id)
ORDER  BY 1,2;

Assuming that only a very small percentage of the rows in each table lie within the "last 5 minutes", an index on ts_minute in both tables should make this very fast.

If your write patterns and autovacuum allow index-only scans, these indices would give you best performance:

CREATE INDEX ON impr_summary_test (ts_minute, user_id, campaign_id, cost);
CREATE INDEX ON bid_summary_test  (ts_minute, user_id, campaign_id, bids_internal, eligible_requests);

Weigh cost and benefit for highly specialized indexes.


Why would you distribute replica shards across nodes and not mirror replicas?

In your example, there are two nodes.
The data is represented at 4 data set shards: A, B, C, D.
Both nodes hold all 4 data sets, with each node as primary writable shard for 2 data sets, and secondary (read only) replica shards for 2 data sets.
This allows you to spread workload across both nodes, with each node serving half of the workload. In the event if a node failure, the secondary replica data sets can be made primary on the other node, with a single node serving 100% of the workload. There is no data loss because A, B, C, & D exist on both nodes.


Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios

In general it is better to know the specific requirements and not make design decisions based on what works best in most situations.  Either could be preferable.  Here are some specifics to gather:

How fast do deletes need to be?
How fast do un-deletes need to be?
How often will deleted data be queried and will it be queried with data that has not been deleted?
How fast do queries of deleted data need to be?
Do you need to preserve only deleted items or changes as well?
Do you need to keep the table/indexes on the primary table small?
What partitioning and/or change tracking technologies are available on the database platform?
How much disk space is available?
Will the deleting occur on the fly or in batch operations?


In error log it's saying: FCB open failed: could not open file
  "\sharepath" for file number 1 OS error: 32 the process cannot access
  the file as it's being used by another process

System account has issues with accessing database files on network share

Can you, perhaps, try this?:

grant full access of [domain\serverName$] to
\\sharepath\sharefolder where database files resides
trace if there any open handles to those file, that prevents database to be started


mysql copy and insert rows in same table with new primary key and sequence number

Suppose we have the following table, containing test data as specified in your question.  Notice that the id column is "AUTO_INCREMENTed". (MySQL version 5.7)

create table batches (
  id int auto_increment primary key
, first_name varchar(32)
, last_name varchar(32)
, address varchar(32)
, batch_no integer
);

insert into batches (first_name, last_name, address, batch_no) values
 ('william','Faulkner','UK',1), ('suzy','nicholus','US',1)
,('ganesh','bora','india',1), ('mahadev','patil','india',1)
,('prasad','k','UK',2), ('sandeep','g','US',2)
,('prajakta','J','india',2), ('Nimish','G','india',2);

select * from batches;
+----+------------+-----------+---------+----------+
| id | first_name | last_name | address | batch_no |
+----+------------+-----------+---------+----------+
|  1 | william    | Faulkner  | UK      |        1 |
|  2 | suzy       | nicholus  | US      |        1 |
|  3 | ganesh     | bora      | india   |        1 |
|  4 | mahadev    | patil     | india   |        1 |
|  5 | prasad     | k         | UK      |        2 |
|  6 | sandeep    | g         | US      |        2 |
|  7 | prajakta   | J         | india   |        2 |
|  8 | Nimish     | G         | india   |        2 |
+----+------------+-----------+---------+----------+

The following procedure may help you to find a solution that fits your purpose.  Your initial idea - assembling a combination of an INSERT and a SELECT, and executing it - is still in there, just the method of finding all necessary columns is different.

delimiter //

create procedure duplicate_rows (
  schema_ text
, table_ text
, oldbatch_ int
, newbatch_ int)
begin

  declare colINSERT_ text default '' ; -- columns needed for INSERTing
  declare colSELECT_ text default '' ; -- columns needed for SELECTing
-- Find all columns for INSERT and SELECT, respectively. () contain "insert" cols.
-- Exclude the 'id' and 'batch_no' columns from the column list(s).
  select
      concat( '(', group_concat(column_name), ',batch_no)')
    , concat( group_concat(column_name), ',', newbatch_ )
    into colINSERT_, colSELECT_
    from information_schema.columns
   where table_schema = schema_
     and table_name = table_
     and column_name not in ('id', 'batch_no');

  set @sql = concat(
    'insert into '       , table_, colINSERT_
  , ' select '           , colSELECT_
  , ' from '             , table_
  , ' where batch_no = ' , oldbatch_
  );

  prepare stmt_ from @sql;
  execute stmt_;
  set @sql = '';

end//

delimiter ;

Test

mysql> call duplicate_rows('test', 'batches', 1, 20);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from batches;
+----+------------+-----------+---------+----------+
| id | first_name | last_name | address | batch_no |
+----+------------+-----------+---------+----------+
|  1 | william    | Faulkner  | UK      |        1 |
|  2 | suzy       | nicholus  | US      |        1 |
|  3 | ganesh     | bora      | india   |        1 |
|  4 | mahadev    | patil     | india   |        1 |
|  5 | prasad     | k         | UK      |        2 |
|  6 | sandeep    | g         | US      |        2 |
|  7 | prajakta   | J         | india   |        2 |
|  8 | Nimish     | G         | india   |        2 |
|  9 | william    | Faulkner  | UK      |       20 |
| 10 | suzy       | nicholus  | US      |       20 |
| 11 | ganesh     | bora      | india   |       20 |
| 12 | mahadev    | patil     | india   |       20 |
+----+------------+-----------+---------+----------+
12 rows in set (0.00 sec)

Notice: when using this procedure, you may encounter "gaps" in auto-incremented ID columns.  More information about this:

https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

https://stackoverflow.com/questions/29315012/what-if-auto-increment-gaps-caused-by-mysql-insert-on-duplicate-key-update

https://stackoverflow.com/questions/1841104/how-to-fill-in-the-holes-in-auto-incremenet-fields


don't the users SYS and SYSTEM share the table dba_users?

Local users created in a pluggable database are not visible in other containers through DBA_USERS. You can query all users from all open pluggable databases in the root container through CDB_USERS.

Overview of Commonality in a CDB

SQL> show con_name pdbs

CON_NAME
------------------------------
CDB$ROOT

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SAMPLE                         READ WRITE NO
         5 BENCHMARK                      READ WRITE NO

SQL>  conn sys/Oracle123@o7ca1-vip/SAMPLE as sysdba
Connected.
SQL> create user user_created_by_sys identified by 1;

User created.

SQL> conn system/Oracle123@o7ca1-vip/BENCHMARK
Connected.
SQL> create user user_created_by_system identified by 1;

User created.

SQL> select username from dba_users where username like 'USER_CREATED%';

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
USER_CREATED_BY_SYSTEM

SQL> conn sys/Oracle123@o7ca1-vip/SAMPLE as sysdba
Connected.
SQL> select username from dba_users where username like 'USER_CREATED%';

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
USER_CREATED_BY_SYS

SQL> conn / as sysdba
Connected.
SQL> select username from dba_users where username like 'USER_CREATED%';

no rows selected

SQL> select con_id, username from cdb_users where username like 'USER_CREATED%';

    CON_ID USERNAME
---------- --------------------------------------------------------------------------------------------------------------------------------
         3 USER_CREATED_BY_SYS
         5 USER_CREATED_BY_SYSTEM


As a SQL Server DBA, what do I need to know about the meltdown/spectre vulnerabilities?

Here is Microsoft's Security Advisory on the vulnerabilities, which have been assigned three "CVE" numbers:

CVE-2017-5715 - Branch target injection ("Spectre")
CVE-2017-5753 - Bounds check bypass ("Spectre")
CVE-2017-5754 - Rogue data cache load ("Meltdown")

The Microsoft KB for how these vulnerabilities impact SQL server is being actively updated as new information becomes available:
KB 4073225: SQL Server Guidance to protect against speculative execution side-channel vulnerabilities.
Microsoft's exact recommendation will depend on your configuration and business scenario, please refer to the KB for details. If you are hosting on Azure, for example, no action is required (the environment is already patched). If, however, you are hosting applications in shared virtual or physical environments with potentially untrusted code, other mitigations may be required.
SQL patches are currently available for the following impacted SQL versions:

SQL Server 2008: 2008 SP4 GDR
SQL Server 2008R2: 2008R2 SP3 GDR
SQL Server 2012: 2012 SP3 CU, 2012 SP3 GDR, 2012 SP4 GDR
SQL Server 2014: 2014 SP2 GDR, 2014 SP2 CU
SQL Server 2016: 2016 CU7 SP1, 2016 GRD SP1, 2016 CU, 2016 GDR, CU7 for 2016 SP1
SQL Server 2017: 2017 GRD, 2017 CU3 RTM, CU3 for 2017

These SQL server patches protect against CVE 2017-5753 (Spectre: Bounds check bypass).
To protect against CVE 2017-5754 (Meltdown: Rogue data cache load), you can enable Kernel Virtual Address Shadowing (KVAS) on Windows (via registry change) or Linux Kernel Page Table Isolation (KPTI) on Linux (via a patch from your Linux distributor).
To protect against CVE 2017-5715 (Spectre: Branch target injection), you can enable Branch Target Injection mitigation hardware support (IBC) via registry change plus a firmware update from your hardware manufacturer.
Note that KVAS, KPTI and IBC may not be required for your environment, and these are the changes with the most significant performance impact (emphasis mine):

Microsoft advises all customers to install updated versions of SQL Server and Windows. This should have negligible to minimal performance impact to existing applications based on Microsoft testing of SQL workloads, however, we recommend that you validate before deploying to a production environment.
Microsoft has measured the impact of Kernel Virtual Address Shadowing (KVAS), Kernel Page Table Indirection (KPTI) and Branch Target Injection Mitigation (IBC) on various SQL workloads in various environments and found some workloads with significant degradation. We recommend that you validate the performance impact of enabling these features before deploying into a production environment. If the performance impact of enabling these features is too high for an existing application, customers can consider whether isolating SQL Server from untrusted code running on the same machine is a better mitigation for their application.

Microsoft System Center Configuration Manager (SCCM) specific guidance:
Additional guidance to mitigate speculative execution side channel vulnerabilities as of Jan 08 2018.

Related blog posts:

Brent Ozar: SQL Server Patches for Meltdown and Spectre Attacks
SQLHA: The No Good, Terrible Processor Flaw and SQL Server Deployments – Nearly Everything You Need To Know
Thomas LaRock: SQL Server Guidance to Protect Against Meltdown and Spectre Attacks
Glenn Berry: Microsoft SQL Server Updates for Meltdown and Spectre Exploits
Glenn Berry: Checking your Meltdown and Spectre Mitigation Status in Windows
Aaron Bertrand: Latest Builds of SQL Server 2017 (collected info and links for CU3)
Joey D'Antoni: Spectre and Meltdown: How Do They Impact SQL Server?


Count events on "user based" sliding window

Here is another option.  It still doesn't use your CONNECT BY (probably a good thing), but does have fewer table scans.  For the SQL Fiddle I used Phil's modified data.

SELECT usr, usrmax - (slice-1)*7 WindowEnd, count(*) FROM
(
  SELECT usr, max(dt) OVER (PARTITION BY Usr) usrmax
     , TRUNC((max(dt) OVER (PARTITION BY Usr) - dt)/7) + 1 Slice
  FROM Sales s
) a
GROUP BY usr, usrmax, slice
ORDER BY usr, WindowEnd;

+1 For an excellent and interesting question.


How to use a variable for object's owner (schema)?

In modern versions of SQL Server, this is actually the schema, not the owner. You can't parameterize this and must concatenate a command:

DECLARE @schema sysname = N'owner';

DECLARE @sql nvarchar(max);
SET @sql = N'CREATE TABLE ' + QUOTENAME(@schema) + N'.my_table(cols...);';
EXEC sys.sp_executesql @sql;

If this is being specified by an end user, you may want to validate that it is a proper schema first, e.g.:

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = @schema)
BEGIN
  RAISERROR(N'Invalid schema %s!', 11, 1, @schema);
  RETURN;
END

A really lazy way is to set the callers default schema to the target, and then don't specify the schema name. This is a bad idea but I'm mentioning it for completeness.


Lock Escalation happening while inserting few million records into a table in production

You can check for any Foreign Key,constraint,trigger etc define on table and give detail of it.

You can use TabLock hint].

Also if there is no chance of Getting READ UNCOMMITTED data then you can use NoLock hint in Select .

INSERT INTO TAB1 WITH (TABLOCK)
SELECT
COL1,COL2,COL3
FROM TAB2

Identity column as primary key is not an issue.
Non cluster Index can be and issue.

So as second option,you can disable index then rebuild later.It is also time consuming for million of records.

ALTER INDEX IX_IndexName ON TAB1 DISABLE;
GO

INSERT INTO TAB1 WITH (TABLOCK)
    SELECT
    COL1,COL2,COL3
    FROM TAB2

ALTER INDEX IX_IndexName ON Tab1 REBUILD;
GO


Determining primary key columns in PGAdmin, if the constraint name is generated by system

I'm not sure I understood what you're asking, but if your question is how to determine what columns constitute a table's primary key you can query the catalog like so:
select c.conname constraint_name, o.relname table_name, a.attname column_name
from pg_constraint c
inner join pg_class o
   on c.conrelid = o.oid
inner join pg_attribute a
  on c.conrelid = a.attrelid
-- pg_constraint.conkey is an array of pg_attribute.attnum values
-- "with ordinality" clause helps keep proper column ordering
inner join lateral unnest (c.conkey) with ordinality as cols (attnum, pos)
  on a.attnum = cols.attnum
where c.contype = 'p'
order by conname, relname, pos


Excellent points by @joishi-bodio.

Here's a simple diagram explaining the different entities, attributes, and their relationships in your model.

The first step in modeling any database is to identify the real-world entities; the attributes that define each entity; and then their relationships and cross references.

Here are different observations and conclusions that you may draw from the example -

Entities and Attributes -

The most obvious real-world entity looking at your example is PERSON.
The entity Person can be defined with attributes like Name, Age, Gender, DOB, Blood group type
A PERSON can be either a 'Blood Donor' or 'Blood Receiver' or both. 'Donor' and 'Receiver' sound like the different roles that a Person takes, but are not different entities by themselves.
Blood Group can be another entity and can be defined by the name of that blood group among other things.
In a hospital, blood is stored in blood bags, which could be another entity, defined by who the donor for that blood bag is.
Transactions is another table which is the store for all blood transactions. A transaction can be defined by a blood bag being assigned to a person (receiver) on a date.

Relationships:

Every person has exactly 1 blood group. You can have 0 or many people with that group.
Each bloodbag has blood from 1 person. A person may or may not have blood in many blood bags.
You can assign 0 or many blood bags to a receiver. A person may or may not be a donor to one or many blood bags.

We get something similar to -


how to find who used SA for login in SQL Server?

community used one SA account for a database.

This is a big security risk, since your "SA" password is a known password in your organization. "SA" is the most elevated privilege account that if misused will cause a disaster and/or data breach by internal people.

I agree with @AaronBertrand, that you should not give "SA" password out to people you do not trust. IMHO, "SA" should be disabled (or atleast change its password on frequent basis), since there are other ways wherein you can give people access following principle of least privilege to get their work done. SQL Server does not have LAPS (like windows server now has), so you have to design and implement a process that changes "SA" password on frequent basis. Below are some things that you can do to limit sa use (see reference cited below for more information article by - K. Brian Kelley):

Set a hard to guess password.
Rename sa.
Disable sa.
Ensure that no other accounts exist named sa.

  So, Is there a way in SQL server to find that person who used SA password and logged in as a user ?

Given above points, SQL Server does not have an easy way of detecting what users/applications are using "SA" to connect. I have implemented a light weight server side trace that can be used to find out applications/users/host name that uses "SA" to connect. Script can be found here and below is the sample output :

For complete automation, you can use TRACE_FILE_CLOSE WMI event to trigger import of trace file into sql server database table.

Alternatively, you can gradually move away from SQL Server authentication to Windows Authentication.

Refer to :

SQL Server 2012 Security Best Practices --> (direct Word doc download)
Best Practices to Secure the SQL Server sa Account - by K. Brian Kelley
How to Get SQL Server Security Horribly Wrong


Using `transactions` without `select ... for update`?

You need a transaction and it needs to grab the gap with FOR UPDATE.  The problem is that you don't have an index to use to help with the task.

I suggest that having both a start and end in a row makes the task difficult.  If, instead, there were only a start (or, equivalently, end), you could have an index and use SELECT...FOR UPDATE successfully.

Since you have gaps in the table, you would need a way to represent "real range" versus "unused range".  That would involve another column (which you might already have).

Building such a structure, plus code to grab part of a gap, is found here , where I discuss (as an example) associating ranges of IP-addresses with their 'owner'.  I use a special owner_id to indicate that a range is 'unassigned'.

The code was designed with speed in mind, since searching on start..end performs miserably at scale.


Why SQL Server doesn't support foreign key constraints on views?

There's no fundamental problem with doing this, since tables and views are both relation variables in the relational model.  Foreign key constraints are just a particular subclass of general constraints, which standard SQL implements using CREATE ASSERTION so there's no particular SQL language difficulty either.

According to this Connect item (no longer available, and not archived) it's simply a matter of prioritization.  If you would find this feature useful, vote for the item and particularly add details of your use case in the comments.


Grouping rows by looking at two columns without considering the order

Something like:

SELECT COUNT(*) AS C, V1, V2
FROM   (SELECT CASE WHEN Value1<Value2 THEN Value1 ELSE Value2 END AS V1
             , CASE WHEN Value1<Value2 THEN Value2 ELSE Value1 END AS V2
        FROM   input_table
       ) AS tbl
GROUP BY V1, V2

should do the trick, but may not be terribly efficient. Any filtering clauses should be added to the inner select, not the outer.

SELECT COUNT(*) AS C
     , CASE WHEN Value1<Value2 THEN Value1 ELSE Value2 END AS V1
     , CASE WHEN Value1<Value2 THEN Value2 ELSE Value1 END AS V2
FROM   input_table
GROUP BY CASE WHEN Value1<Value2 THEN Value1 ELSE Value2 END, CASE WHEN Value1<Value2 THEN Value2 ELSE Value1 END

may also work (and may be more efficient) but the code repetition between the GROUP and SELECT clauses may become a maintenance problem.

Of course if you can ensure that the data is always the "right way around" (and this doesn't break your model in other ways - we can't tell if it might as your questions gives no detail on which to base a supposition either way) then

SELECT COUNT(*) AS C, Value1, Value2
FROM   input_table
GROUP BY Value1, Value2

is sufficient. You could enforce the two values being the right way around using INSTEAD OF triggers or in your business logic layer (updating existing data should be easy).


SQL Insert multiple values with UNIQUE constraint

SQL constraint violation affects the entire statement, you cannot ignore individual rows.

What you can do is insert only the rows that don't violate the constraint. There are several ways to do that, for example

INSERT INTO dzp.contractid(vnr)
SELECT DISTINCT vnr FROM dzp.accounts_stage s
WHERE NOT EXISTS (SELECT 1 FROM dzp.contractid WHERE vnr = s.vnr)

You could also use MERGE:

MERGE dzp.contractid AS t
USING dzp.accounts_stage AS s
ON (t.vnr = s.vnr)
WHEN NOT MATCHED BY TARGET
THEN INSERT(vnr) VALUES(s.vnr)


According to the SQL92 spec, can I store the value 1 in a field where the precision = scale?

The precision is the total number of decimal digits that can be stored on both sides of the decimal place. The scale is the total number of decimal digits to the right of the decimal point which can be less than or equal to the precision. Therefore the maximum value that can be stored in a DECIMAL(3,3) or a NUMERIC(3,3) is 0.999. The easiest way to find this out is to actually try it out.

Using mysql 5.5.20 the below returns 0.999:

SET @val = (SELECT CAST(1 AS DECIMAL(3,3)));
SELECT @val;

Whereas the following returns 1:

SET @val = (SELECT CAST(1 AS DECIMAL(3,0)));
SELECT @val;

I hope this helps.

EDIT: I forgot to mention that on most platforms attempting to perform the above will result in some type of Arithmetic Overflow Exception. For instance the below statement running on Microsoft SQL Server 2008 will throw an error:

DECLARE @val DECIMAL(3,3);
SET @val = 1;
SELECT @val;


Any tools to simulate innodb crash for innodb_force_recovery levels 1-6?

I use bvi to corrupt a tablespace. However you need to know InnoDB files format and how InnoDB works in order to make it start with particular recovery level. For example, for level 1 you need to change a checksum. For 3 - corrupt UNDO and so on.


Slower queries after upgrade from mysql-5.5 to mysql-5.6

The problem was that our new RDS instance was being throttled on writeIOPS, we could see queued disk operations (DiskQueueDepth of 10 or greater) and we could see writeIOPS averaging 300, whereas Amazon only provide 100 reliable iOPS (source).

The solution was to fork out approx $200 per month for "provisioned iOPS". 1000 is the smallest you could buy (which easily covers us). After this change which took 5 hours to complete on 350G of storage (but only 30s of downtime with a multi-AZ instance) everything was fine and our MySQL 5.6 instance was running better than the original MySQL 5.5 instance like all the literature promises.

The problem was that the new RDS instance that we were given since migrating to 5.6 was just not as good as our original, which was just bad luck. We asked Amazon support "Q: Why would there be a difference in mysql5.5 than mysql5.6?"
From Amazon:

  Your previous underlying host may have been benefiting from unusually light use on the underlying volumes. You may have grown accustomed to very light use which allowed you to consume additional IOPs without accumulating as much DiskQueueDepth. However, when you moved to a new underlying host by upgrading you may now be experiencing more typical use of the underlying volumes. With typical use of the underlying volumes it's not recommended to consistently exceed the amount of reliable IOPs while expecting to maintain the lower DiskQueueDepth that you've grown accustomed to. However, there is no way to confirm or deny that this was the cause.


Deleting an Oracle database without connecting to it?

From René Nyffenegger's blog:

  Since 10g, It is possible to drop a database in Oracle 10g with the new SQL statement drop database…

  The command gets rid of datafiles online redo log files, controlfiles and spfile…

shutdown abort;
startup mount exclusive restrict;
drop database;
exit

  Requirements:

  . database must be closed
    . exclusively mounted
    . restricted

If you do not have a password, you should still be able to connect from the console, eg:

sqlplus / as sysdba


Accessing file share from ETL package in SQL Always-On configuration

It sounds to me like you are suggesting that you require a highly available file share (see https://blogs.technet.microsoft.com/clausjor/2012/06/07/smb-transparent-failover-making-file-shares-continuously-available/). In order to do this you will obviously need Clustered storage (an ip and network name). It would be a little unusual to do this using your Availability Group's Windows Cluster since really that is only in place to provide Quorum and meta-data synchronisation/ mediation of AG Cluster HA objects -and I would personally advice against complicating that deployment -Clusters should be kept as simple and easy to troubleshoot as possible. Instead I would advise to use a remote fileshare - which you can either make highly available via another windows cluster or just use a standalone server (depending upon requirements).


How to divide data per client from a single database?

I think you could definitely use SSIS to do this task.

I would start by selecting your distinct ClientID's into an object variable and then using a ForEachLoop with a Foreach ADO Enumerator and then specify that variable and load each row one at a time into a string variable. You could then used an Execute SQL Task that would check to see if a database exists for that ClientID and, if not, create the database and subsequent tables.

Then you could dynamically build your destination connection based on that ClientID as the database name. You also would want to dynamically build your SQL for your extract from your source and then do a simple extract and load into your new or existing database.


As Thomas mentioned that it can be done using LOGON Trigger. Below is the script that will help you out

/*

http://www.sqlservercentral.com/scripts/Security/69558/
Credit: Gregory A. Ferdinandsen
--greg@ferdinandsen.com
--Revision 1.0, 8 Feb 10
--Requires SQL 2005 SP2 or higher
*/
if not exists (select 1 from master..sysdatabases where name = 'SQL_Audit')
 begin
 create database SQL_Audit
  end
USE [SQL_Audit]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BlackList](
[SRV_Rule] [int] IDENTITY(1,1) NOT NULL,
[HostName] [varchar](64) NULL,
[IP_Address] [varchar](15) NULL,
[LoginName] [varchar](128) NULL,
[AppName] [varchar](256) NULL,
[RestrictionEnabled] [bit] NULL,
[Description] [varchar](2048) NULL,
CONSTRAINT [PK_BlackList] PRIMARY KEY CLUSTERED
(
[SRV_Rule] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[BlackList] ADD CONSTRAINT [DF_BlackList_RestrictionEnabled] DEFAULT ((0)) FOR [RestrictionEnabled]
GO

---------------------------------------------------------------------
---------------------------------------------------------------------
USE [SQL_Audit]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Violations](
[ViolationNum] [int] IDENTITY(1,1) NOT NULL,
[PostDate] [datetime] NOT NULL,
[LoginName] [varchar](128) NULL,
[IPAddress] [varchar](15) NULL,
[HostName] [nvarchar](64) NULL,
[ServerName] [varchar](96) NULL,
[AppName] [nvarchar](256) NULL,
[ViolationType] [varchar](512) NULL,
CONSTRAINT [PK_Violations] PRIMARY KEY CLUSTERED
(
[ViolationNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Violations] ADD CONSTRAINT [DF_Violations_PostDate] DEFAULT (getdate()) FOR [PostDate]
GO
---------------------------------------------------------------------
---------------------------------------------------------------------
--(c) Gregory A. Ferdinandsen
--greg@ferdinandsen.com
--Revision 1.0, 8 Feb 10
--Requires SQL 2005 SP2 or higher

--
--Change with <<Execute as 'Domain\SQL'>> for a valid service account that has sa rights
--
--Information on Logon Triggers: http://msdn.microsoft.com/en-us/library/bb326598.aspx
--
USE Master
go

CREATE Trigger [trg_LoginBlackList]
 on all Server

 as
begin

 declare @data XML
declare @User as varchar(128)
declare @HostName as varchar(64)
declare @IPAddress as varchar(15)
declare @AppName as nvarchar(256)
declare @SPID as int
declare @SrvName as nvarchar(96)
declare @PostTime as datetime
declare @LogMsg as varchar(1024)

set @data = EVENTDATA()
set @User = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)')
set @IPAddress = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(15)')
set @SPID = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')
set @SrvName = @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(96)')
set @PostTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
set @HostName = Cast(Host_Name() as nvarchar(64))
set @AppName = Cast(App_Name() as nvarchar(256))

--Check to see if the blacklist table exists, if the table does not exist, exit the Trigger, as otherwise all user would be locked out.

if Not Exists (select * from SQL_Audit.INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'BlackList')
begin
return;
end

--#1
--If a user connects from a given work station and with a given UserName, they will be dissconected
--This user need to be set up in SQL_Audit..Blacklist with a user name and a host name, no IP Address is necesary
--This is the prefered method of blacklisting, as DHCP could reak havoc on any IP restrictions
If(Exists(Select * from SQL_Audit.dbo.BlackList where LoginName = @User and HostName = @HostName and RestrictionEnabled = 1))
begin
--Any data modifications made up to the point of ROLLBACK TRANSACTION are rolled back
--The current trigger continues to execute any remaining statements that appear after the ROLLBACK statement.
--If any of these statements modify data, the modifications are not rolled back.
--http://technet.microsoft.com/en-us/library/bb153915.aspx
rollback

insert into SQL_Audit..Violations
(PostDate, LoginName, IPAddress, HostName, ServerName, AppName, ViolationType)
values (@PostTime, @User, @IPAddress, @HostName, @SrvName, @AppName, 'LoginName, HostName')

--Exit trigger without evaluating any further conditions
return;
end

--#2
--If a user connects from a given IP Address and with a given UserName, they will be dissconected
--This user need to be set up in SQL_Audit..Blacklist with a user name and a IP Address, no HostName is necesary
If(Exists(Select * from SQL_Audit.dbo.BlackList where LoginName = @User and IP_Address = @IPAddress and RestrictionEnabled = 1))
begin
--Any data modifications made up to the point of ROLLBACK TRANSACTION are rolled back
--The current trigger continues to execute any remaining statements that appear after the ROLLBACK statement.
--If any of these statements modify data, the modifications are not rolled back.
--http://technet.microsoft.com/en-us/library/bb153915.aspx
rollback

insert into SQL_Audit..Violations
(PostDate, LoginName, IPAddress, HostName, ServerName, AppName, ViolationType)
values (@PostTime, @User, @IPAddress, @HostName, @SrvName, @AppName, 'LoginName, IP Address')

--Exit trigger without evaluating any further conditions
return;
end

--#3
--If a user connects from a given Blacklisted IP Address, regardless of the host name or SQL Server User
--This IPAddress need to be set up in SQL_Audit..Blacklist with only an IP Address, no other information is needed
--This will block all connections from the designated IP Address
If(Exists(Select * from SQL_Audit.dbo.BlackList where IP_Address = @IPAddress and LoginName is NULL and HostName is NULL and RestrictionEnabled = 1))
begin
--Any data modifications made up to the point of ROLLBACK TRANSACTION are rolled back
--The current trigger continues to execute any remaining statements that appear after the ROLLBACK statement.
--If any of these statements modify data, the modifications are not rolled back.
--http://technet.microsoft.com/en-us/library/bb153915.aspx
rollback

insert into SQL_Audit..Violations
(PostDate, LoginName, IPAddress, HostName, ServerName, AppName, ViolationType)
values (@PostTime, @User, @IPAddress, @HostName, @SrvName, @AppName, 'IP Address')

--Exit trigger without evaluating any further conditions
return;
end

--#4
--If a user connects from a given Blacklisted Workstation, regardless of the IP Address or SQL Server User
--This Client need to be set up in SQL_Audit..Blacklist with only a value for HostName, no other information is needed
--This will block all connections from the designated Host
If(Exists(Select * from SQL_Audit.dbo.BlackList where HostName = @HostName and LoginName is NULL and IP_Address is NULL and RestrictionEnabled = 1))
begin
--Any data modifications made up to the point of ROLLBACK TRANSACTION are rolled back
--The current trigger continues to execute any remaining statements that appear after the ROLLBACK statement.
--If any of these statements modify data, the modifications are not rolled back.
--http://technet.microsoft.com/en-us/library/bb153915.aspx
rollback

insert into SQL_Audit..Violations
(PostDate, LoginName, IPAddress, HostName, ServerName, AppName, ViolationType)
values (@PostTime, @User, @IPAddress, @HostName, @SrvName, @AppName, 'HostName')

--Exit trigger without evaluating any further conditions
return;
end

--#5
--If a particular application connects to SQL Server, regardless of IP Address, UserName, or HostName, the session is terminated
If(Exists(Select * from SQL_Audit.dbo.BlackList where AppName = @AppName and HostName is NULL and LoginName is NULL and IP_Address is NULL and RestrictionEnabled = 1))
begin
--Any data modifications made up to the point of ROLLBACK TRANSACTION are rolled back
--The current trigger continues to execute any remaining statements that appear after the ROLLBACK statement.
--If any of these statements modify data, the modifications are not rolled back.
--http://technet.microsoft.com/en-us/library/bb153915.aspx
rollback

insert into SQL_Audit..Violations
(PostDate, LoginName, IPAddress, HostName, ServerName, AppName, ViolationType)
values (@PostTime, @User, @IPAddress, @HostName, @SrvName, @AppName, 'ApplicationName')

--Exit trigger without evaluating any further conditions
return;
end

--#6
--If a particular application connects to SQL Server, with a given UserName (i.e. service account cannot connect with SSMS)
If(Exists(Select * from SQL_Audit.dbo.BlackList where AppName = @AppName and LoginName = @User and RestrictionEnabled = 1))
begin
--Any data modifications made up to the point of ROLLBACK TRANSACTION are rolled back
--The current trigger continues to execute any remaining statements that appear after the ROLLBACK statement.
--If any of these statements modify data, the modifications are not rolled back.
--http://technet.microsoft.com/en-us/library/bb153915.aspx
rollback

insert into SQL_Audit..Violations
(PostDate, LoginName, IPAddress, HostName, ServerName, AppName, ViolationType)
values (@PostTime, @User, @IPAddress, @HostName, @SrvName, @AppName, 'ApplicationName, UserName')

--Exit trigger without evaluating any further conditions
return;
end
end;

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [trg_LoginBlackList] ON ALL SERVER
GO


Oracle 11g increasing shared pool size and java pool size

You're have AMM enabled, so Oracle will manage it for you automatically. SGA_TARGET will be set to a non-zero value, which means:

  Specifies the total size of all SGA components. If SGA_TARGET is
  specified, then the buffer cache (DB_CACHE_SIZE), Java pool
  (JAVA_POOL_SIZE), large pool (LARGE_POOL_SIZE), and shared pool
  (SHARED_POOL_SIZE) memory pools are automatically sized.

You can set it to a non-zero value that is equal to or less than SGA_MAX_SIZE, so if you need to allocate more memory, just up SGA_MAX_SIZE and SGA_TARGET.

eg:

ALTER SYSTEM SET SGA_MAX_SIZE = 2048M;
ALTER SYSTEM SET SGA_TARGET = 2048M;

See this documentation section (search for "Enabling Automatic Shared Memory Management") - Read that whole documentation section to gain a better understanding.

As an aside, your SGA_MAX_SIZE does seem very small.


I think you want a lookup table that shows the requirements for a particular event. In this case, generally I would also have attr1-3 in a separate table called 'Attribute' and a user_attribute table to assign attributes to a particular user_detail record. Then, you could assign a particular event to require n attributes through the lookup table-

event_attribute (event_attribute_id [PK], event_id [FK], attribute_id [FK],...).


How to tell different types of Logins apart and how to see the users?

Yes the icon changes if it's a group vs a user. You can also query sys.server_principals.

To see the users within an AD group, you can use xp_logininfo. As long as the AD group is a login on the SQL Server, you can query it using xp_logininfo. Here's a really old blog post I wrote on it.

Syntax:

xp_logininfo [ [ @acctname = ] 'account_name' ]
     [ , [ @option = ] 'all' | 'members' ]
     [ , [ @privilege = ] variable_name OUTPUT]

Example:

EXEC master.dbo.xp_logininfo 'DomainName\GroupName', 'members'


Select Query slow for Non-clustered index

Because when your query using a non-clustered index (not covering every column), SQL Server finds your data with key lookup. This is more work than scan the entire clustered index.
SQL Server has a cost-based optimization. So, it compares two execution plans and chooses the less coster. But sometimes (depends on your statistics), it does wrong chooses.
Also, check out the tipping point good blog post from Brent Ozar.
If you change your query with a less SELECT col1,col2 than SELECT * and add columns on the non-clustered index as an include which are selecting, you can get rid of from key lookup operation. But, in your situation in which there is no condition, actually no need for the non clustered index.
I create table,index and write a query just like you. As you can see the cost of queries, a non-clustered index+key lookup more than the clustered index scan.

First Query:

SQL Server Execution Times:    CPU time = 32 ms,  elapsed time = 241
ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
time = 0 ms.

Second Query:

SQL Server Execution Times:    CPU time = 125 ms,  elapsed time = 245
ms. SQL Server parse and compile time:


What is recommended level of security for a production database?

Windows Authentication should be chosen over SQL Server Auth. Windows Auth is inherently more secure, as it's controlled by the OS. Granted, there are times when you won't be able to get around a SQL Server Auth principal because of a third party application, or whatever the surrounding circumstances are.
You should have the amount of respective logins for each logical principal that can connect or could be audited. In other words, if every application uses the same login, and your auditing captures login name, it'll be virtually useless.
You should disable the sa account as it can be a security threat. The reason behind this is because it is extremely well-known login name for SQL Server instances. It takes out a lot of the guessing game for hackers. Disable this account, but ensure there are other appropriate principals with sysadmin privileges.

As for other security measures, they are far and vast. Our own member, mrdenny, has written a book on this very subject. A few pointers are you'll want to keep a tight sand box on your principals. In other words, give only permissions that the principal will need in order to perform the functionality, and no more.


How to DRY record metadata in SQL Server

DDL (Data Definition Language: create/alter/drop/...) and DML (Data Manipulation Language: insert/update/delete/...) operations will need to be logged separately as the first affects the structure of the tables (and other objects) where the second affects the contents of those objects. I'll only deal with the DML side (when your application modifies table contents on behalf of a user) here.

Either way keeping the user ID and user name separately is not normal form - unless one isn't direclty modified (i.e. if the name is maintained as a computed column) you could potentally end up with data inconsistency. Also if you are trying to maintain an audit of data modifications you should have some sort of timestamp against the action. A generic arrangement might look like:

                   ActionAudit
ThingTable         ---------------
------------       AuditRowID (PK)
ThingID (PK)  <--  ThingID    (FK)       Users
Property1          Action                -------------
Property2          UserID     (FK)  -->  UserID   (PK)
   ...             TimeStamp             USerName
PropertyN

(where @action@ in the audit table is an enumeration representing insert,update,delete)
Of course you now have an extra table to maintain as whenever rows are affected in ThingTable you need to add an entry in AuditTable too, but you do have a complete list of who changed to the data not just the most recent user to do so.

If you want to keep a copy of the user ID (and the name too if you like) that inserted or last updated a row in ThingTable, then you could use a computed column or maintain those columns by a trigger that fires when an entry is made in ThingTable.

If you want to audit multiple objects this way, you either need an action audiot table per other object, or you need to drop the FK relationship with the ThingID in the one table, or you need a general Things table to stay in normal form, like so:

                                                ActionAudit
ThingType1Table             Things              ---------------
---------------             ------------        AuditRowID (PK)
ThingID   (PK, FK)  ----->  ThingID (PK)  <---  ThingID    (FK)       Users
Property1             |                         Action                -------------
Property2             |                         UserID     (FK)  -->  UserID   (PK)
   ...                |                         TimeStamp             USerName
PropertyN             |
                      |
                      |
ThingType2Table       |
---------------       |
ThingID   (PK, FK)  --'
Property1
Property2
   ...
PropertyN

If you need to note what changed each time then you'll need to add some way of recording that, but as all you are recorindg right now id the user I assume you don't need this level of recording. If you did you could do something like:

                     ThingFullAudit
                     -------------------
                .--  ThingID    (FK)
                |    AuditRowID (PK, FK) --.
                |    Property1             |
                |    Property2             |
                |       ...                |
                |    PropertyN             |
                |                          |
                |                          |
                |    ActionAudit           |
ThingTable      |    ---------------       |
------------    |    AuditRowID (PK)  <----'
ThingID (PK)  <----  ThingID    (FK)            Users
Property1            Action                     -------------
Property2            UserID     (FK)  ------->  UserID   (PK)
   ...               TimeStamp                  USerName
PropertyN

(that is based on the audit table per other object version: I'll leave merging this concept with the single audit table for all objects idea as an exercise for the reader!)
You can maintain the ThingFullAudit table using a trigger on ActionAudit, assuming the row in ThingTable is added/modified before the row in ActionAudit is inserted, that copies the contents of ThingTable to ThingFullAudit. You can then see what changes each user made in each update.

Of course this may all be massively over-kill for your logging requirements - you'll need to describe what the existing columns are trying to acheive in order for us to give you a more specific and concise answer.


Are there any risks to granting users SQL Server SHOWPLAN permission?

Take a look at Showplan Security in Books Online, which says:

  Users who have SHOWPLAN, ALTER TRACE, or VIEW SERVER STATE permission
  can view queries that are captured in Showplan output. These queries
  may contain sensitive information such as passwords. Therefore, we
  recommend that you only grant these permissions to users who are
  authorized to view sensitive information, such as members of the
  db_owner fixed database role, or members of the sysadmin fixed server
  role. We also recommend that you only save Showplan files or trace
  files that contain Showplan-related events to a location that uses the
  NTFS file system, and that you restrict access to users who are
  authorized to view sensitive information.

  For example, consider the following query:

SELECT COUNT(*)  FROM table_1  WHERE column_1 < 10

  If a malicious user produces Showplan output for a set of queries like
  this example, and replaces the value "10" in the predicate with
  different constants each time, the user could infer an approximate
  data distribution of the column values for column_1 in table_1 by
  reading the estimated row counts.

This risk does seem negligible, especially given this is a development server, where all queries would be from devs and IT people, not users.


What is the maximum number of columns allowed on a table in MySQL MEMORY storage engine?

From This MySQL link

  There is a hard limit of 4096 columns per table, but the effective
  maximum may be less for a given table. The exact limit depends on
  several interacting factors.

  Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.

  ...

These are engine-independent MySQL limits. As you say the MEMORY engine specific manual pages do not seem to specify any additional restrictions for size or count.


Caching queries that last longer then then X seconds

Instead, cache only selected queries.  Go through your code and decide which queries are likely to be exactly repeatedly.  On those do SELECT SQL_CACHE ....  On the others do SELECT SQL_NO_CACHE ....
As for the long queries -- improve them.  See this for getting started.  When you have identified a couple of 'slow' queries, open a new Question to get help on making them faster.  Sometimes we can help you make them run 10 times as fast.


Print Column Names for Table in Another Database

You were almost there with your second answer - you just need to ensure you're selecting columns from the correct sys.columns table

declare @ColumnList varchar(max) =
(select STUFF((
SELECT ',
    '
+ QUOTENAME(c.name)
FROM DatabaseB.sys.columns c
where c.object_id = object_id('DatabaseB.dbo.Customer')
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,''))

print @ColumnList

The above works for me, adding the DB reference into sys.columns for DatabaseB.


return age in years and months from date of birth

You can do it with variables, like this:

SELECT
  user_id,
  year(@fecha:=from_days(datediff(now(), user_birthdate))) as years,
  month(@fecha) as months

But have caution with the execution order. If you assign the value in SELECT don't expect same value in WHERE part.