I am getting a failed login message in the SQL Server error logs however, nothing actually failed?

From Aaron Bertrand's blog on Troubleshooting Error 18456

  Error: 18456, Severity: 14, State: 58.
  Login failed for user ''.
  Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.

State 58 occurs when SQL Server is set to use Windows Authentication only, and a client attempts to log in using SQL Authentication.

You have to Change Server Authentication Mode and restart sql server, if you/your software requires SQL Server authentication and your sql server is configured for Windows Authentication.


How do I identify all the students who have passed?

The problem you describe is known as relational division. There are basically two ways to approach it:

Transform for all x : p(x) to  not exists x :  not p(x)

In other words, transform students who have passed all test to students where it does not exist a test that the student did not pass. Your query in the question is an example of this.

This transformation is often referred to as De Morgan's laws for quantifiers. See for example:

https://philosophy.stackexchange.com/questions/28324/proving-de-morgans-laws-for-quantifiers

Students who have passed the same amount of tests as there are tests. Something like:

 SELECT student_id
 FROM tests
 WHERE passed = 'y'
 GROUP BY student_id
 HAVING COUNT(1) = (SELECT COUNT(DISTINCT test_id)
                    FROM tests)

I'm answering from a mobile so I can't see the question while answering, so the details of my answer is likely wrong according to your question, but hopefully you get the idea.


You can set the database to SINGLE_USER so that no other session can come in while you are performing your changes. Also, sp_addrolemember has been deprecated; you should be using ALTER ROLE instead. Finally, please get in the habit of using statement terminators.

USE master;
GO

ALTER DATABASE SQL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE SQL SET READ_WRITE;
GO

USE SQL;
GO

ALTER ROLE db_datareader ADD MEMBER [USER];
GO

USE master;
GO

ALTER DATABASE SQL SET READ_ONLY;
ALTER DATABASE SQL SET MULTI_USER;
GO


Why this statement invalid: `VALUES ( CURRENT PACKAGESET )`?

I dont think CURRENT PACKAGESET is a special register in LUW 10.5. The special registers can be found at:

https://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0008404.html?cp=SSEPGG_10.5.0%2F2-12-2-5

SET CURRENT PACKAGESET is a statement that can only be embedded in an application program:

https://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001005.html?cp=SSEPGG_10.5.0%2F2-12-7-219&lang=en


Does SQLBackup2Azure delete the blobs as well?

I have experimented with the tool and confirmed that deleting the stub files does not delete the Azure blob.  I had to set up a PowerShell script to cleanup the file blobs.


SQL Server- Limit to Concurrent table queries?

How is it that N number of users can access the same physical data at the same time?

This is done using transactions (honors ACID properties) & concurrency models. SQL server supports Optimistic concurrency as well as Pessimistic concurrency (default concurrency model) which inturn uses different isolation levels (read committed is the default isolation level ) and locking mechanism to allow multiple users access the data at the same time.

Refer to : SQL Server Transaction Locking and Row Versioning Guide


I need to make a structure for a mysql database

Build another table with 3 columns

the primary key of users (perhaps email?).
what they are watching,
when.

Each user would have any number (0 or more) rows in this table.

Think this way:  When you need a "vector", make a new table.


Configuring MySQL (my.cnf) for MyISAM and InnoDB

You mentioned before you are running

MySQL 5.5.29
16 GB RAM

Here are some good settings to start with
innodb_buffer_pool_size=4G
innodb_buffer_pool_instance=2
innodb_read_io_threads=12
innodb_write_io_threads=12
innodb_io_capacity=300
innodb_log_file_size=128M

Don't do the conversion yet !!!
Put there settings in my.cnf, then do the following
service mysql stop
cd /var/lib/mysql
rm -f ibdata1 ib_logfile*
service mysql start

Now, you can do the conversion.
Give it a Try !!!
UPDATE 2013-05-10 10:44 EDT
Here are my past posts on tuning InnoDB for better CPU usage

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance

Here are my past posts on tuning InnoDB

Feb 16, 2011 : How to safely change MySQL innodb variable 'innodb_log_file_size'?
Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?


I'll create a small example, that you perhaps can use:

create table customers
( customer_no int not null primary key
, valid_licence char(3) default 'No' not null
, status varchar(10) default 'Unapproved' not null
);

insert into customers (customer_no) values (1),(2),(3);

So now we have 3 customers:

select * from customers;
+-------------+---------------+------------+
| customer_no | valid_licence | status     |
+-------------+---------------+------------+
|           1 | No            | Unapproved |
|           2 | No            | Unapproved |
|           3 | No            | Unapproved |
+-------------+---------------+------------+
3 rows in set (0.000 sec)

If I get it right your trigger should look like:

delimiter //
create trigger check_licence
BEFORE UPDATE ON customers
FOR EACH ROW BEGIN
    SET NEW.Status = CASE WHEN NEW.valid_licence = 'Yes'
                          THEN 'Approved'
                          ELSE 'Unapproved'
                     END;
END //
delimiter ;

Test:

update customers set valid_licence = 'Yes' where customer_no = 2;

select * from customers;
+-------------+---------------+------------+
| customer_no | valid_licence | status     |
+-------------+---------------+------------+
|           1 | No            | Unapproved |
|           2 | Yes           | Approved   |
|           3 | No            | Unapproved |
+-------------+---------------+------------+
3 rows in set (0.000 sec)

As for your question "Also, would you recommend doing a trigger for age separately?". First of all, don't use age, it will change as time passes by. Use birth_date and calculate the age. Not sure what you intend to do there though, can you clarify?


Is it possible to build an UNDO framework for postgres?

In theory you can, but in practice you'll find that undoing individual changes is kind of hard to get right because of foreign keys, etc.

Consider:

CREATE TABLE a (
    id integer primary key
);

CREATE TABLE b (
    id integer primary key,
    a_id integer not null references a(id) on delete cascade
);

INSERT INTO a(id) VALUES (1),(2),(3);
INSERT INTO b(id, a_id) VALUES (10,1), (20,2), (30,3);

Now, say you do a:

DELETE FROM a WHERE id = 2;

This will cause an automatic delete of (20,2) from b. When you want to undo this change, you need to know that the delete from a also caused a delete in b.

Similarly, say you:

DELETE FROM b WHERE id = 30;

then separately and later, someone else does a:

DELETE FROM a WHERE id = 3;

If you want to undo the first delete it'll fail unless you undo the second delete first.

Basically, you can only reliably undo an operation if you undo all subsequent operations in the order that they occurred first. Even then, it's hard to get right.

That's before you even get to thinking about schema changes. If you add a new NOT NULL column to a table, then you want to undo a DELETE that happened before the column was added, what do you do? Give the new row the default value in the column? What if there's no DEFAULT set?

I'd record an audit history but wouldn't generally attempt to support undo.

You might want to investigate PostgreSQL's point-in-time recovery features for a way to do database-wide rollbacks or restores to points in the past.


In SQL Server and using only client side encryption requests, can I use a different certificate for each client?

SQL Server SSL encryption that is initiated from the server does not require that the client have a copy of the certificate in advance. The certificate and private key are stored in the certificate store of the service account on the server. If the certificate is selected using SQL Server Configuration Manager and Force Encryption is set to true, then whenever a client requests a session, the server replies with the encryption requirement and sends a copy of the certificate, which contains the public key. To establish an SSL or TLS session, the client randomly selects a symmetric session key and transmits it back to the server encrypted by the public key. This random key is then used as the basis for secure communication. It is recommended to use a domain certificate or CA certificate and not a self signed certificate as a self signed certificate would leave you vulnerable to a man in the middle attack.

There is also a way to request from the SQL Server Management Studio client that a session be encrypted. I don't see any certificate configuration, so it may use a self signed certificate generated at the client to initiate a secure connection. Either way, you can see if any connections are encrypted using the sql statement:

    select encrypt_option, * from sys.dm_exec_connections


So SET ARITHABORT ON basically says "if a divide by zero error happens or an arithmetic overflow happens abort the query" This is usually desirable behavior and is the default instance wide setting. If this causes issues with your vendor's queries, I would say that they may have been suffering from some coding issues to begin with. I would ask them for more details on why they are concerned here.

Of all of the rules of indexed views, I would call this and many of the set options rules the least controversial.

This would have to be set in the connections that interact with the view. So you would want to work with the vendor and really try and understand their reasoning and try and get them to commit to what they are thinking on the big disagreement here.

That said - Indexed views are a bit of a big deal. They have other rules and they can impact the application and assumptions the vendor's developers had when building and performance testing. You should really have a conversation with them about the business problem you are trying to solve through indexed views and get them involved in the conversation about how to solve the problem.


Yes this is the default behavior. How ? The default value for max_connections is 151.

This is true for MySQL 5.7, MySQL 5.6, and MySQL 5.5

If /etc/my.cnf does not have max_connections, then 151 comes up as max_connections.

You can just run set global max_connections = 1000;.

Your hosting company must put that entry in /etc/my.cnf for you

[mysqld]
max_connections=1000

Otherwise, every restart of mysqld will reset max_connections to 151.

I have suggested this before

Jul 06, 2012 : How to resolve too many connections and fatal error in mysql running on vps


LOAD_FILE() works on SELECT, but returns NULL on insert

LOAD_FILE, only reads the contents from the file and returns the contents from itself as a string, you try to add an image into field and using this function isn't gonna be possible. Besides you should have FILE privilege to you could do this and allocating the file into /var/lib/mysql-files. Being this a bad practice, the best is add the route relative or absolute from this file.


Design for when a company can edit a client information, but client has a private profile

A staff has all of the Person table columns, and more.

How much more ? 4-5 column extra ?
I suggest try to keep both table same with more column as additional. along with ClientType Column int.

OR

Client Info table means ,those minimum info is need to become client.
Same thing is true for Staff too .Isn't it ? So Staff info will be maintain in 2 tables.One which is necessary to become client will be in client Table.Rest in Staff table.

Now as per rule Company can change same fields for both Client and Staff.
So may be Staff info is edited from 2 place,but both are 2 diff. thing.

So there will be Profile table for both Staff and Client.

Profile
 -Id
 -Name
 -Email
-- ClientType int not null (1=Client,2=Staff,3=Client who do not have profile)

Staff Table
It contain info of Staff table which cannot be edited by company.but can be edited by Admin

Person (basically a clone of the client's profile at first)
 -Id
 -Name
 -Email
 -Profile Id (not nullable)
-- ClientType (Denormalization)

CompanyClient
 -Company Id
 -Person Id
 --ClientType (Denormalization)

Rating Table
--RatingID
--PersonID
--Observatiion
--Rating

This is not my final design .It all depend upon MIS report and frequency of use.


Matching a smaller piece of text in a local variable to a larger piece of text that I have pulled in a query

You can use the CHARINDEX() function to do this:

SELECT vManagedEntity.Path
FROM vManagedEntity.Path
WHERE CHARINDEX(@ServerName,Path) > 0

Or, you can craft a LIKE query:

Assuming you want to do a Starts With match on the Path field:

SELECT vManagedEntity.Path
FROM vManagedEntity.Path
WHERE Path LIKE @ServerName + '%'

If you want to do an Ends With, change to:

SELECT vManagedEntity.Path
FROM vManagedEntity.Path
WHERE Path LIKE '%' + @ServerName

Or, a Contains:

SELECT vManagedEntity.Path
FROM vManagedEntity.Path
WHERE Path LIKE '%' + @ServerName + '%'


mariadb takes too much load though having sufficient RAM

With your limited information posted, suggestions to consider for your my.cnf [mysqld] section

max_connections=512  # from 2000 because max_used_connections only 213
join_buffer_size=256K  # from 20M to reduce RAM required per connection
read_buffer_size=256K  # from 2M to reduce RAM required per connection
read_rnd_buffer_size=128K  # from 8M to reduce RAM required per connection

These changes will significantly reduce your CPU busy and provide better performance.


MySQL - Grant user permission to create table as long is it has a prefix in the name

There is not a wildcard facility of that sort.

Don't prefix columns with the table name.    If you need qualification, do tablename.columnname.

Don't prefix tables with the table name.  If you need qualification, do dbname.tablename.

If necessary, move all the examples into a separate db and GRANT suitable permissions to suitable users for that database.


Force update statements to take page locks in the same order?

How can I force both statements to take page locks in the same order?

You can't.  Instead force them both to take a table lock with a TABLOCKX hint, or use an Application Lock to serialize the sessions.


Thanks to the comment by beccasue, I found the solution:

Using a Nested If with IsNumeric and ToNumber worked.

Example of nested Crystal Reports if statement copied from a SO question:
Nested if else in Crystal Reports

if {table1.id} <> 1 then
   (if {table1.name} <> "a" then
      var1 := "Hello"
   else
      var1 := "Hi";)
else
   var1 := "Bye";


How do I determine the correct index on MS-SQL-Server based on a select?

You are unlikely to ever see index use because of this in the query:
CONVERT(VARCHAR(6), v1.Tag, 112)

Because it has to calculate the values from v1.Tag, it can't simply use the histogram in the statistics to determine row counts. It must scan the entire table to find all the values that match, and it has to do the conversion each time.
If u1.Monat and v1.Tag are not the same data type, adding a column that is the same data type might be necessary (on either u1 or v1, whichever).
However, the bigger issue is, you're moving all the data. The query has no WHERE clause to filter anything. Without a filter, the most efficient way to move everything is through scans. Index seeks wouldn't make sense. You might see some index usage on the JOINs between tables (once you fix the CONVERT problem), but possibly not since all the rows are being moved.


And is it correct to assume that the n rows that are chosen from
  table_name2 are completely random records?

When an order isn't specified, it will usually use the sort fields and directions from the design of the primary key of table_name2, but is not guaranteed to.  As Aaron Bertrand said

  It doesn't really matter what the clustered index is. SQL Server may
  still return the order based on some other index based on a wide
  variety of factors. Creating a primary key on some column DOES NOT
  guarantee that selects with no order by will suddenly come back
  ordered by that column always. Might you observe that most of the
  time? Sure. But that is not the same as a guarantee. I've never seen a
  polar bear on my street but there is no polar bear forcefield that
  prevents it from happening. – Aaron Bertrand♦ Sep 14 '11 at 20:41

It certainly wouldn't hurt to add your order clause in, even if you want them to return in the index order of the PK.

From the tutorial example

INSERT INTO table_name1 (Col1, Col2)
  SELECT TOP n Col3, Col4
  FROM table_name2
Order By Col1;

Gaaaah.  That's horrible coding. That will order by Col1 ASC of table_name2, even though your SELECT clause isn't explicitly returning a Col1 from the second table. It is NOT ordered by Col3 of the 2nd table aka Col1 of the 1st table. The MS docs for INSERT INTO do not list an ORDER BY clause - the ORDER BY is part of the inner SELECT clause.

It's always better to explicitly specify which table you are inserting into and selecting from.  E.g. this would be a slightly better example.

INSERT INTO
  table_name1 (Col1, Col2)
  SELECT TOP 3
    t2.Col3
    ,t2.Col4
  FROM
    table_name2 AS t2
  ORDER BY
    t2.Col1 ASC, t2.Col3 ASC, t2.Col4 ASC

After this alteration there's no confusion about what Col1 means (nor the sort order of it). I'd still recommend throwing in a sort order of Col3 and Col4 if Col1 isn't unique for all entries in table_name2, as demonstrated above. You cannot alias the table that records are inserted into (e.g. table_name1 AS t1 (t1.Col1, t1.Col2) as that is not legal syntax for existing versions of SQL Server.

Opinion-based recommendation: avoid the tutorial website that you found.


MariaDB Galera setup with drastically different amounts of memory used on cluster nodes - why?

The InnoDB buffer pool caches queries; if you have less usage on one of the nodes then you're going to have less cache used.

If you're not properly load-balancing queries then the amount of cache in use on each node would be different. If you just turned on a node and added it to the cluster the amount of cache would be different than the others one that have been long running.

It doesn't matter if you use Galera in this case as this is a specific InnoDB question; sure you could be using Galera, but it has nothing to do with this caching question you've asked.


Why is a SELECT grant needed for an UPDATE statement on a table? (Error 1143)

Whilst I can't find anything official to back this up (I will keep trying) I guess a user with UPDATE but not SELECT permissions could brute force their way into knowing the values in the table without having SELECT permissions

lets say the user runs the following query

UPDATE MyTable SET MyCol = 'Something' WHERE Id = 1

and it returns that it successfully updated a row

and then the user runs

UPDATE MyTable SET MyCol = 'Something' WHERE Id = 2

and so on (they could write a loop using front end or database code to try a range of Ids quickly)

This tells the user which values for Id exist in the table without them having SELECT permissions (and this example could be applied to other columns in the table)


Recommendation for synchronizing SQL Server DBs

You can also use ApexSQL Data Diff. It has command line, so you can schedule the synchronization
I've used both RG and ApexSQL. ApexSQL has only 1 version (not basic and Pro, like RG), so the fetures that are available only in RG SQL Data Compare Pro version available in ApexSQL 'basic' version. And ApexSQL is cheaper


Use FOR XML PATH with subqueries to get the appropriate level of nesting, something like this:

DECLARE @yourTable TABLE
    (
    ResourceName        VARCHAR(20),
    ClassName           VARCHAR(20),

    SystemName          VARCHAR(20),
    ShortName           VARCHAR(20),
    ClassSortOrder      INT,

    TabNumber           INT,
    RowNumber           INT,
    DBName              VARCHAR(20)
    )

INSERT INTO @yourTable VALUES
    ( 'Property', 'Commercial', 'Type', 'Type', 1, 1, 1, 'Fields Type' ),
    ( 'Property', 'Residential', 'Address', 'Address', 2, 2, 2, 'Fields Address' ),
    ( 'Property', 'MultiFamily', 'Price', 'Price', 3, 3, 3, 'Fields Price' ),
    ( 'Property', 'LotsAndLandy', 'BuildYear', 'BuildYear', 4, 4, 4, 'Fields Type' )

SELECT * FROM @yourTable

SELECT
    0 AS "@ReplyCode",
    'Operation Successful' AS "@ReplyText",
    (
    SELECT
        ResourceName AS "ResourceName",
        (
        SELECT
            ClassName AS "ClassName",
            (
            SELECT
                SystemName AS "SystemName",
                DBName AS "DBName",
                ShortName AS "ShortName",
                ClassSortOrder AS "ClassSortOrder"
                FOR XML PATH(''), TYPE
            ) AS "Field"
        FROM @yourTable c
        WHERE r.ResourceName = c.ResourceName
        FOR XML PATH('Class'), TYPE
        ) AS "Classes"
    FROM ( SELECT DISTINCT ResourceName FROM @yourTable ) r
    FOR XML PATH(''), TYPE
    ) AS "*"
FOR XML PATH('Results'), TYPE

It's not 100% clear as your sample data is different from your sample XML but hopefully that gives you a starting point.


What options exist for copying data between servers on a regular basis?

I'd consider backup/restore of a mix of full and differential backups from prod to dev.
Then synch between this restored copy to your actual dev databases

SQL Server 2008 R2+ supports backup compression in Standard Edition (In SQL Server 2008 it was Enterprise Edition only) which makes this easier.

Reasons:

You test your backup integrity and restore capability
You can reset your dev database easily if some code monkey bollixes it
You can compare "before" and "after" DDL, performance etc
You can snapshot the restored database as needed to roll this back if you make changes

I've used this before and will use it again for these reasons

I suggested this in my answer to The smallest backup possible ... with SQL Server


SQL Batches and the GO command: which parts of my script failed?

After sufficient testing, I think I've answered all my questions:

  If an error refers to Line 6, is that from the top of the script? Or from the top of the batch? (after the prior GO)

Errors are reported from the top of the entire script, not from the top of the batch.

One caveat: errors point to the line that starts the statement containing the error, even if the error itself is on a later line. For example, if badTableName doesn't exist, the following will report an error on line 12, not line 13:

SELECT *                 --line 12
FROM badTableName        --line 13

  If an error is encountered, does it roll back only the statements in that batch? (between the GOs)?

Yes. If an error is encountered, that entire batch (between the GOs) is rolled back.

  Would the order of the statements within the batch have mattered, if one of them errored out?

No, the order of commands within that block would not have mattered.

  The order of the output leads me to believe that the first sp_rename succeeded (with the "caution" output), and the second failed with the error.

After some testing, this appears to be correct. The Caution is only output when sp_rename completes successfully.

  The failure of the prior batches doesn't prevent later batches from running, correct?

Correct. Other batches are treated independently, and will be run.


Monitor stored procedure statistics with extended events

Your options are a bit limited in SQL Server 2008 R2 and Extended Events.  You could try rpc_completed but remember it will only register for remote procedure calls.  Here's some sample code tested in SQL 2008 R2 which shows how to create a session then read it.  Work through it, make sure you understand it and try it out:
-- xe_rpc_completed
IF EXISTS ( SELECT * from sys.server_event_sessions WHERE name = 'xe_rpc_completed' )
DROP EVENT SESSION [xe_rpc_completed] ON SERVER
GO

-- rpc_completed
CREATE EVENT SESSION [xe_rpc_completed] ON SERVER
ADD EVENT sqlserver.rpc_completed
    (
    ACTION ( package0.callstack, sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_stack )
    )
ADD TARGET package0.asynchronous_file_target (
     SET filename = 'd:\temp\xe_rpc_completed.etl', metadatafile = 'd:\temp\xe_rpc_completed.mta' )
WITH ( MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY =OFF, STARTUP_STATE = OFF )
GO

-- Start the session, collect some data
ALTER EVENT SESSION [xe_rpc_completed]
ON SERVER
STATE = START;
GO

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL drop table #tmp

SELECT IDENTITY(INT,1,1) rowId, object_name, CAST( event_data AS XML ) event_data
INTO #tmp
FROM sys.fn_xe_file_target_read_file('d:\temp\xe_rpc_completed*etl', 'd:\temp\xe_rpc_completed*mta', NULL, NULL )
GO

-- Optionally add primary XML index;
--!!NB this will cause table to grow up to 5x!
ALTER TABLE #tmp ADD CONSTRAINT pk_tmp PRIMARY KEY (rowId)
GO
CREATE PRIMARY XML INDEX xmlidx_event_data ON #tmp ( event_data )
GO

SELECT
    e.c.value('@timestamp', 'DATETIME2') [timestamp],
    e.c.value('(data[@name = "cpu"]/value/text())[1]', 'INT') cpu,
    e.c.value('(data[@name = "duration"]/value/text())[1]', 'INT') duration_s,
    e.c.value('(data[@name = "reads"]/value/text())[1]', 'INT') reads,
    e.c.value('(data[@name = "writes"]/value/text())[1]', 'INT') writes,
    e.c.query('(action[@name = "sql_text"]/value/text())[1]') sql_text,
    e.c.query('.') event_data

FROM #tmp t
    CROSS APPLY t.event_data.nodes('event') e(c)
ORDER BY [timestamp]
GO

-- Collect some data
-- ...

-- When you're ready stop the session
ALTER EVENT SESSION [xe_rpc_completed]
ON SERVER
STATE = STOP;
GO

synchronous_event_counter probably won't help you as you just get an event and a count.  It's good for say, counting logins or deadlocks in a day, something like that.
ring_buffer probably won't help you because it's a circular area with only limited space allowed.  Basically it could run out of room and cycle over.
HTH


How can I return the full text of a document indexed in SQL Server Full-Text?

I am curious about what you will accomplish by getting the plain text.  You already have the documents in the FileTable and you can open the files when needed using the appropriate tools.

For example: If you are looking at a PDF, a Word document, an Excel spreadsheet, and so forth you probably have the tools to look at the data. Most tools will even allow you to save the 'plain text' manually. But I suppose it depends on how you define "plain text".  Save of Word document to a non-Unicode .TXT file?  Or something more complex?

Of course, I realize that you would likely want a more automated approach, which I comment on further down.

One thing to know is that not all files will necessarily copy to "plain text". (At least I had trouble with plain text from Chinese.)

Regarding the Full Text Indexes:

Although the SQL Server Full Text Index is aware of the relative positions of 'words' in the index so that it can search for phrases, it has no interface to reveal the serial sequence of words in the text. Thus, there is no way (currently) to build plain text from the Full Text Index.

Even if those details were available, the Full Text Index still does not have everything needed to fully represent a document.

If you have any Stop Words configured, then they will not appear in the Full Text Index.
Punctuation is generally not included except when tokenized within a word, such as "pre-apocalypse", "F.B.I." and so forth.
Formatting is lost.
The language being used for Full Text Indexing may affect the results.

The Full Text Indexes are solving a different problem from creating plain text. However, my experience is that large text bases usually benefit from Full Text Indexing anyway, since someone is always trying to find something

I understand that once you get the text you will redundantly store it in a SQL Server table so as to expedite your use of the plain text data.

Automating the Extract and Load

This will require some work.  But, since your documents are currently in a FileTable you should be able to access them from the file system using file tools. The "off-topic" answer to plain text extraction below includes several tools that are being used by others. Perhaps some of these tools would be useful to you.

http://stackoverflow.com/questions/5671988/how-to-extract-just-plain-text-from-doc-docx-files-unix

Since you are using SQL Server 2014, once you have plain text files you should be able to import them in a number of ways: BCP, SSIS, and other tools are available to load data.

Depending on your approach, you might choose to use a staging table to further prepare the results before moving the data into the destination table. And if you are also doing document versioning you will likely need to create some metadata that allows you to track different versions.


Restore a database with a different name on the same server

It's really simple to solve. You are trying to override the original files you need to you use the move option like this:
restore database <Your-Database> from disk ='<backup-file-path>'
with move 'mdf_file_name' to '<new-path>',
move 'log_file_name' to '<new-path>'

By the way to discover the file name from backup use this command:
restore filelistonly from disk ='<backup-file-path>'


Highest cost section in Actual Execution Plan doesn't show any logical reads?

The % cost is still just an estimation, even in an Actual Execution Plan. You need to look at the row source level statistics here to tell you how much time each step took.
Judging by the XML, 274ms was taken up to the point of the hash join at node 7, 72ms of that was just the cluster scan of Pk_TeamMembers (node 8) and 35ms was up to the the Left Semi Join (node 9). This means that a whopping 167ms was taken just to execute the Hash Join step (likely assigning memory for the 263,600 rows from Pk_TeamMembers).
It looks like you've already tried to target this join before:
LEFT JOIN TeamMembers tm on AF.CompletionUserID = tm.UserID AND AF.TeamID = tm.TeamID AND tm.TeamID = t.TeamID -- Overloaded to help SQL find indexes

You probably want to do an (Outer) nested loop to this index instead. The query planner has decided to use a hash join because it thinks that you will get 59000 rows from the other side of this join, when in fact you get 3.
You have choices

Hint so that a nested loop join is used
Figure out why the planner thinks it will have so many rows and give it some help (statistics, simplify the filtering)

The hint would look like
LEFT LOOP JOIN TeamMembers tm on AF.CompletionUserID = tm.UserID AND AF.TeamID = tm.TeamID AND tm.TeamID = t.TeamID

The second choice will be particularly hard because of the complexity of your driving filters. One way to do this would be to insert the rows that match your main driving filters, and then join to this temp table in your main query. Here, it looks like you need to join 3 of the 5 tables together first in order to utilize your @userId@ filters fairly:
FROM ActivityFeed AF
    JOIN ActivityType A ON AF.ActivityType = A.ActivityType
    LEFT JOIN Teams t on AF.TeamID = t.TeamID
WHERE
    AF.SearchIndexUpdateDate IS NULL -- Only get items that aren't in the search index
    AND
    AF.Active=1
    AND
    A.Active=1
    AND
    (A.AdminOnly = 0 OR AF.TeamID IN (SELECT TeamID FROM @tmpFindAdminFlagsForTeamsMembershipsForUser x WHERE x.IsAdmin=1))
    AND
    (
      (
        A.TeamOnly=1
        AND AF.TeamID IN (SELECT TeamID FROM @tmpFindAdminFlagsForTeamsMembershipsForUser x WHERE A.HideWhenOwnerless = 0 OR x.IsOwnerless = 0)
        AND (A.ShowSelf=1 OR AF.CompletionUserID <> @UserID)
        AND ISNULL(t.IsDeleted,0) = 0 AND t.active=1
        AND (A.IncludeOnsite = 1 OR EXISTS(SELECT 1 FROM @tmpFindAdminFlagsForTeamsMembershipsForUser x WHERE x.TeamID = AF.TeamID AND x.IsOnsiteOwner = 0))
       )
       OR
      (
        A.UserOnly=1
        AND AF.UserID=@UserID
      )
    )
    AND
    -- Don't included moderated activity (owner moderation actions are in Group activity view API_GetActivityFeedByTeam)
    AF.Moderated = 0
    AND
   -- don't show completions or plan completions to other team members if opted out (tm.ShowUserActivityInFeed)
    (ISNULL(A.ShowOnlyWhenUserActivityActive,0) = 0 OR (A.ShowOnlyWhenUserActivityActive = 1 AND tm.ShowUserActivityinFeed = 1))
    AND
    (AF.TeamID IS NULL OR AF.TeamID IN (SELECT TeamID FROM @tmpFindAdminFlagsForTeamsMembershipsForUser as x))


How does a database use the SYS schema even when we change the password?

The database doesn't bother authenticating itself to itself.

As the database does not use the password identification strategy when performing operations, changing the SYS password only impacts how humans can use the account.


Best way to handle column that doesn't conform to one particular type

The approach you are proposing is workable but informal.  A more formal approach can be found in my answer to this question.

The basic pattern is called a Property Bag.


PHP / MYSQL connection failures via socket under moderate load

Error 11 in unix is a segmentation fault error. Either the server has bad memory chips installed or you are making other configuration values too much for the server to handle.

I once experimented with setting the nofile value in linux to a value like yours and my whole server crashed. Now I make nofile set to maybe 40000 tops. Try that, then reboot the server to see if the problem goes away.


Run Mongodb Server as Service and mongod instance

The following command has solved my problem

sudo -u mongodb mongod --dbpath /var/lib/mongodb

The -u mongodb after sudo runs the mongod with mongodb user. So,I don't have to change owner of folder /var/lib/mongodb to mongodb.

Now, I am able to start Mongodb Server as Service and also by mongod.


SQL80001: Incorrect Syntax:" Create Procedure" must be the only statement in the batch

You just need to put a GO batch separator around your CREATE PROCEDURE. You have something else above it, and it needs to be in its own batch. Hence the GO that's needed.

GO
CREATE PROCEDURE [dbo].[USP_UserRoles_Select]
(
    @Rolename VARCHAR(30) = ''
)
AS
BEGIN
    Select ID,Name
    FROM
        AspNetRoles
    WHERE
        Name like  @Rolename +'%'
END
GO

A GO is also needed after the script if there is code following the END. The BEGIN and END statements are superfluous so all code in the batch will be part of the stored procedure.


If you don't have the maintenance window for it, updating statistics daily is probably a little overkill.  Especially if you have Auto Update Statistics turned on for the database.  In your original post, you said that users are seeing a performance degredation due to this maintenance plan.  Is there no other time to run this maintenance plan?  No other window?  I see that your plan encompasses index reorganization, when are you rebuilding indexes?  When that operation happens, statistics are automatically updated (provided that isn't turned off for the index).

Exactly how often you should be updating statistics depends greatly on how much data modification your indexes and data is receiving.  If there is very little modification (INSERT, UPDATE, DELETE) to the data, then you could have a more infrequent schedule for the update statistics job.

One way to find out if your statistics are stale is to look at the execution plans and if you estimated rows greatly differ from your actual rows returned then that is a good indication that the interval needs to be upped.  In your case, you're going the other way and a bit of trial may be in order for you.  Update statistics weekly, and if you're starting to see the tell-tale signs of stale stats then go from there.

If you are using Auto Update Statistics for your database, see this reference for the threshold of when statistics are updated.


Open Transaction that won't go away SPID Ns

I guess this is a known issue and Microsoft has released information about it in below links

Cause:

This issue occurs because the "tran_sp_MScreate_peer_tables" transaction was left open by a replication upgrade script during the upgrade. This open transaction prevents usual log truncation.

Transaction log for database is growing with system SPID as open transaction

There is also a documented support article which will help you in understanding the issue

Database transaction log continues to grow after upgrade


Currently (Jan 23, 2017)

PostgreSQL uses IANA (Olson) version 2016j
2016j is the latest version.
tz-updates are rolled out to supported major versions in point releases. 9.2 is still supported (current as of Jan 2017) with TZ upgrades.

You can see the change long for the tz database upstream here

  Where can I find any information about the timezone's DB version used?

check out the README in src/timezone/README which says "the code in this directory is currently synced with tzcode release 2016j."

  Is it possible updating the timezone's DB by replacing the newest official IANA version? Consider a legacy system with Postgres 8.4 and no chance of updating it.

It's already the latest version, but I imagine if it ever falls behind patches would be accepted. You can find the instructions to create the patch in the README file above.


I see one of three options here:

1) you can have a templated script to create databases that explicitly includes the recovery model.

2) you can set the model database to simple and not have to worry about this.

3) you can hope everybody remembers, which seems like what you are doing. (not recommended)

I would personally go with number two. That's what the model database is there for.


No syntax for Nonclustered index in MySQL?

InnoDB:  The PRIMARY KEY is UNIQUE and clustered -- always.
Secondary keys are non-clustered.  They have the PRIMARY KEY (not a rownum or rowid) in the leaf node of the secondary key index.

MyISAM:  Keys are non-clustered -- always.  The leaf nodes of indexes have a pointer (think: byte offset) into the data file to get from the index to the data.  ALTER TABLE tbl ORDER BY ...; will reorder the "data", thereby temporarily give you clustering on whatever you ORDERed BY.

MySQL decided those were "good enough".  I have only very rarely found a need for something else.


Sql servers are using Kerberros and NTLM Authentication

Kerberos authentication should be faster and it is definitely more secure, so if you are at all concerned about security it should be enabled.  The only issue that I can think of that might occur is if the Service Principal Names (SPNs) incorrect, then logins will fail.  Accordingly, if you are adding SPNs to get it set up, I would recommend having the SETSPN commands ready so you can quickly will back out the changes that were made.

Add new SPNs
Test client / application connections (restart applications that connect to force new logins)
Remove SPNs if tests fail and investigate

Ref: What Is Kerberos Authentication?


MySQL InnoDB Insertion Faster Than MyISAM

InnoDB has been faster than MyISAM at everything for at least a decade. This shouldn't be a surprise in 2020. There hasn't been a compelling case for using MyISAM in a very long time.

On a workload that involves any concurrency, the difference will be even bigger.

Also, note that Baron's post is from 2012 - and it was probably borderline in terms of accuracy even back then.


An OFFLINE database is indeed still occupying space on disk. You can verify this by finding the location of the database files with a quick peek at sys.master_files and then navigating in windows explorer to the directory location.

select *
from sys.master_files
where database_id = db_id('my_offline_db');

If it's not bothering you, then there's no need to drop the database. You might run into some maintenance processes that don't play nice with an offline database, but it's a matter of preference whether you modify these processes to gracefully handle and offline database or you remove the offline db from your instance.

If you choose to drop the database to free up disk space, you will need to remember to manually delete the files afterwards. If you want to quickly browse some reasons why you might keep the offline database around, this ServerFault question is a good read.


PostgreSQL update column in one table with value from another, based on key from first table

An update of that magnitude is inevitably going to take a substantial amount of time, no matter how good your configuration is or how powerful the hardware. It's possible there might be room for improvement, but there are not enough details to go on here. For a start, it would be interesting to know the output of:
EXPLAIN (VERBOSE, SETTINGS)
   UPDATE paper_author pa SET author_name =
   (SELECT author_name FROM authors a WHERE a.author_id = pa.author_id)

You might want to consider an alternative strategy along the lines of breaking up the update into smaller batches. That will give you a better idea of how long the process might last, and provide better control over your system resources.


Using the same sequence twice in a PL/SQL statement

This is the expected behaviour of nextval as documented:

  Within a single SQL statement containing a reference to NEXTVAL, Oracle increments the sequence once:

  For each row returned by the outer query block of a SELECT statement

  [...] If any of these locations contains more than one reference to NEXTVAL, then Oracle increments the sequence once and returns the same value for all occurrences of NEXTVAL.

This means that you won't be able to use plain SQL to overcome this limitation: you will need some PL/SQL. Either a trigger that populates both fields or a function that wraps the sequence call. Here's an example with such a function with 11g:

SQL> create sequence table_seq;

Sequence created.

SQL> create table test (sort_nr number, text_id number, unit_id number);

Table created.

SQL> create or replace function getid return number is begin return table_seq.nextval; end;
  2  /

Function created.

SQL> insert into test values (1, getid, getid);

1 row created.

SQL> select * from test;

   SORT_NR    TEXT_ID    UNIT_ID
---------- ---------- ----------
         1          1          2


Assuming col caps out at 255 characters, this doesn't have to perform any checks on length or complicated case expressions:

SELECT col = SUBSTRING(col, PATINDEX('%[a-z]%', col), 255)
  FROM dbo.table
  ORDER BY col;

Updating to show an example that works with varchar even if the value I hard-coded exceeds the size of the column:

DECLARE @t TABLE(col VARCHAR(32));

INSERT @t VALUES
('_one'),
('_two'),
('three_blah'),
('four_blah');

SELECT col = SUBSTRING(col, PATINDEX('%[A-Za-z]%', col), 255)
  FROM @t
  ORDER BY col;

Results:

col
----------
four_blah
one
three_blah
two

Another alternative (that doesn't require hard-coding the length of the string or determining the length of every value in the table) is to use STUFF. Note that this does change how strings without a single alphabetic character will be handled (NULL output instead of being left alone). Those rows will still sort first, they'll just be less meaningful. You can deal with those however you like by adding COALESCE.

SELECT col = STUFF(col, 1, PATINDEX('%[A-Za-z]%', col)-1, '')
  FROM @t
  ORDER BY col;

(This portion of the answer should be credited to @MikaelEriksson.)


Is it possible to run a trigger on Table Creation in MySQL

Unfortunately, no.

MySQL documentation clearly states applicable events:

    trigger_event: { INSERT | UPDATE | DELETE }


Manual failover of AAG in SQL Server 2012

Do not use Failover Cluster Manager for your failovers. It can cause problems.

  Do not use the Failover Cluster Manager to move availability groups to
  different nodes or to fail over availability groups. The Failover
  Cluster Manager is not aware of the synchronization status of the
  availability replicas, and doing so can lead to extended downtime. You
  must use Transact-SQL or SQL Server Management Studio.


Design a table of items with many features; how to group those features

Up front disclaimer: I am a SQL DBA, so exact solutions may differ.

Two approaches that I have seen to this. The first is to ask if a single person can be an admin for more than one store. Can Paul Smith have store 27 and 19? or just one? If that is the case, then I would add the needed fields to that persons' profile.

A second way of approaching it is to have a join table that would look like this:

Join_ID |  Admin_ID |  Store_ID | Admin_Type_ID

This way would be a many-to-many situation, where a single person can be assigned to multiple stores, while a single store can have multiple admins of the same or different types. You could also use this to have one person be a general manager at one store, but a network admin at another.

The overhead for this should not be too different than anything else going on in the database, and should only take a few extra minutes to plan out how the stored procedures would read and write to it.


Where is SQLCMD.EXE in SQL Server 2014 Express?

Found this on Microsoft Connect:

  According to this page on MSDN
  http://msdn.microsoft.com/en-us/library/ms143547(v=sql.120).aspx ,
  sqlcmd.exe and bcp.exe are now in < Install Directory >\Client
  SDK\ODBC\110\Tools\Binn

Indeed, sqlcmd.exe and bcp.exe are in that folder in my machine.

Make sure you installed Client SDK Tools.


Mapping a 1:1:N ternary relationship to a relational schema

A candidate key of a relation is a set of attributes such that every tuple (record) of the relation must have values differing from those of all the other tuples.

A primary key can be either chosen among the different candidate keys of a relation, or can be a surrogate key, that is an artificial identifier invented to uniquely distinguish all the records of the relation.

Surely, a primary key should not be a strict superkey, that is a key from which we can remove one or more attributes so that the remaining attributes still uniquely identify all the tuples of the relation.

Now to find the primary key we must first ask: which are the candidate keys of this relation? In this case there are only two candidate keys: (EQUIPMENT_ID, TIME_SLOT_ID) and (MEMBER_ID, TIME_SLOT_ID). In fact, each of them must be unique for all the tuples of the relation. And there are no other candidate keys (in fact, (EQUIPMENT_ID, MEMBER_ID) can have duplicate elements, as the single attributes, as you have already noted, while (MEMBER_ID, EQUIPMENT_ID, TIME_SLOT_ID) is a strict superkey).

So the the primary key in your case could be: a) the couple (EQUIPMENT_ID, TIME_SLOT_ID); b) the couple (MEMBER_ID, TIME_SLOT_ID); c) a surrogate key like RESERVES_ID. If there are no particular reason to introduce another attribute as surrogate key, you can use any of the two couples of attributes as primary key, and declare the other pair as unique.

Finally, note that applying the normalization theory, you have two non-trivial functional dependencies MEMBER_ID, TIME_SLOT_ID -> EQUIPMENT_ID and EQUIPMENT_ID, TIME_SLOT_ID -> MEMBER_ID from which can be derived that the relation is in Boyce Codd Normal form and that the two couples are the unique candidate keys.

EDITED

If your question really is: in case of a generic ternary relationship, with the cardinality as in the example, represented as a relation with three foreign keys, which are the candidate keys of such relation?

The answer can be given by considering the functional dependencies inferred by the cardinality. Let’s call the three attributes (foreign keys) A, B and C, and suppose that you can have at most a value of A for a couple of values BC, at most a value of B for a couple of values AC, and any number of values of C for a couple of values AB (this models what you have called 1:1:N relationship in your example, with A being the member, B the equiplement and C the time slot). This can be translated in terms of functional dependencies as:

BC -> A
AC -> B

(technically, this is called a cover of the set of dependencies holding on the relation, meaning that there are no other non-trivial functional dependencies holding in this case).

From this, we can easily derive that the relation has only two candidate keys, AC and BC. So, in terms of a relational database system, you can choose any one of them as primary key, and declare the unique constaint for the other.


How can I assign different random values to each row in a SELECT statement?

A subquery is evaluated once if possible. I can't recall what the "feature" is called (folding?) sorry.

The same applies to GETDATE and RAND functions. NEWID is evaluated row by row because it in intrinsically a random value and should never generate the same value twice.

The usual techniques are to use use NEWID as input to CHECKSUM or as a seed to RAND

For random values per row:

SELECT
   co1l, col2,
   ABS(CHECKSUM(NEWID())) AS Random1,
   RAND(CHECKSUM(NEWID())) AS Random2
FROM
   MyTable

If you want random order:

SELECT
   co1l, col2
FROM
   MyTable
ORDER BY
   NEWID()

If you want random order with a row order too. ActualOrder order here is preserved regardless of the order of the resultset

SELECT
   id, val,
   ROWNUMBER() OVER (ORDER BY id) AS id
FROM
   #t1
ORDER BY
   NEWID()

Edit:

In this case, we can state the requirement as:

  return any random value from the set for each row in the set
  the random value will be different from the actual value in any row

This is different to what I offered above which simply re-orders rows in various ways

So, I'd consider CROSS APPLY. The WHERE clause force row by row evaluation and avoids the "folding" issue and ensures that val and rnd are always different. CROSS APPLY can scale quite well too

SELECT
   id, val, R.rnd
FROM
   #t1 t1
   CROSS APPLY
   (SELECT TOP 1 val as rnd FROM #t1 t2 WHERE t1.val <> t2.val ORDER BY NEWID()) R
ORDER BY
   id


This may be a bit unorthodox, but you could have a table that allows you to store "friendly names", and comments etc, which you can update/query whenever required. Eg

create table myfriendlynames (
  id number generated always as identity primary key
, aclid varchar2( 256 ) unique
, friendlyname varchar2( 256 ) unique
--, comments varchar2( 256 )
) ;

-- pick up all aclids from dba_network_acls, and store them in the new table
merge into myfriendlynames M
using (
  select aclid from dba_network_acls
) ACLS on ( M.aclid = ACLS.aclid )
when not matched then
  insert ( M.aclid ) values ( ACLS.aclid )
;

Update, set a friendly name ...

SQL> select * from myfriendlynames ;
ID  ACLID             FRIENDLYNAME
1   0000000080002710  NULL

update myfriendlynames
set friendlyname = 'Fred'
where friendlyname is null ;  -- you could also use id here

-- aclid - friendlyname mapping
select ACLS.aclid, M.friendlyname
from dba_network_acls ACLS
  join myfriendlynames M on ACLS.aclid = M.aclid
;

ACLID             FRIENDLYNAME
0000000080002710  Fred

Query / view

-- add/remove columns here as required
select
  ACLS.host
, ACLS.lower_port
, ACLS.upper_port
, substr( ACLS.acl, 1, 16 ) || '...' || substr( ACLS.acl, -4, 4 ) aclshort
, ACLS.aclid
, M.friendlyname
from dba_network_acls ACLS
  left join myfriendlynames M on ACLS.aclid = M.aclid
;

HOST  LOWER_PORT  UPPER_PORT  ACLSHORT                 ACLID             FRIENDLYNAME
*     NULL        NULL        NETWORK_ACL_FD9A...FB6F  0000000080002710  Fred


Installing Postgresql parray_gin extension on mac, include not found

You need to manually download the sources and edit the Makefile

Add this:

PG_CPPFLAGS = "-I/usr/local/opt/icu4c/include"
SHLIB_LINK =  "-L/usr/local/opt/icu4c/lib"

and it works

https://github.com/theirix/parray_gin/issues/5


You can use PIVOT with dynamic sql to solve your problem. Below is a simple implementation to solve the issue. The query will support any number of months you may have. Make sure the length of @pivcols is large enough to accommodate the maximum anticipated string length for all the current and future months (povot columns) concatenated.

--Table structure--

Create table #T
(
     PN int
    ,Year int
    ,Month int
    ,Usage int
    ,UID int
);

Insert into #T values(813453,  2014,    12,      0,       24180)
Insert into #T values(813453,  2014,    11,      2,       24179)
Insert into #T values(813453,  2014,    10,      4,       24178)
Insert into #T values(813453,  2014,    9,      6,       24177)
Insert into #T values(813453,  2014,    8,      3,       24176);

--Dynamic pivot--

declare @pivcols as varchar(800);

select  @pivcols = stuff((select '],[' + cast((Year*12 + Month) as varchar(10))
from #T order by Year*12 + Month desc
for xml path('')), 1, 2, '') + ']';

declare @pivquery varchar(500);

set @pivquery = 'Select PN, '+
@pivcols +'
from
(select PN, Usage,
 cast((Year*12 + Month) as varchar(10)) rnk
 from #T
) as d
PIVOT
(
max(Usage)
for rnk in ( '+ @pivcols +' )
) AS pvt ';

EXECUTE(@pivquery);

--Results--

PN      24180   24179   24178   24177   24176
813453  0       2       4       6       3


In Oracle, will certain users only be able to see certain data in the privileges tables?

The table_privileges view is only preserved for compatibility with ancient versions of Oracle.

You should instead be using the user_tab_privs view, all_tab_privs view, or dba_tab_privs view (documentation links for each when you click).


This sample might help in order to script events. To capture the specific events, you need to create a Server audit specification first - The SQL Server Audit object collects a single instance of server or database-level actions and groups of actions to monitor:

USE master;
GO
CREATE SERVER AUDIT ServerAudit
TO FILE (FILEPATH = 'c:\audits\', MAXSIZE = 2 GB)
WITH (ON_FAILURE = CONTINUE);
GO
ALTER SERVER AUDIT ServerAudit
WITH (STATE = ON);

The next step is to create a Database audit specification at the database level. The audit group in this example is is SCHEMA_OBJECT_CHANGE_GROUP

USE [ACMEDB];
GO
CREATE DATABASE AUDIT SPECIFICATION schema_change
FOR SERVER AUDIT ServerAudit
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
GO

Finally, for example, query the previously created audit, by using the LIKE operator to narrow down captured entries to the ones related to enable/disable triggers:

SELECT
   event_time AS [Time],
   server_principal_name AS [User],
   object_name AS [Object name],
   Statement
  FROM sys.fn_get_audit_file('c:\audits\ServerAudit*', NULL, NULL)
WHERE
   database_name
   =
   'ACMEDB'
AND (
   Statement LIKE '%DISABLE%TRIGGER%'
OR Statement LIKE '%ENABLE%TRIGGER%')ORDER BY
                                      [Time] DESC;


How can I execute multiple statements at once in MySQL Workbench?

You can open a separate SQL editor for each LOAD command and run it there. While one command is running you can switch to the next editor and run the next command and so on.


MySQL - is there a way to implement a CHECK constraint after creating the table?

Assuming you are using MySQL >= 8.16 when MySQL finally implmemented CHECK constraints, you can do this following:

ALTER TABLE <table_name>
ADD CHECK (check criteria)

so in your case, it would be

ALTER TABLE jims_faculty
CHECK (fac_experience >= 2)

If you want to name the constraint, you can do this:

ALTER TABLE jims_faculty
ADD CONSTRAINT chk_fac_experience_gt_2 CHECK (fac_experience >= 2);

From the manual (incredibly) Prior to MySQL 8.0.16, CREATE TABLE permits only the following limited version of table CHECK constraint syntax, which is parsed and ignored:

The information in this post was largely gleaned from here.


MySQL looking up more rows than needed (indexing issue)

Your indexes are fine for the two types of queries you mentioned.
This query will be satisfied by traversing the clustered index on the primary key...
[...] WHERE participant_id = x AND question_id = y AND given_answer_id = z;

...and this one is satisfied by the index on 'question_id':
[...] WHERE question_id = x;

The output of EXPLAIN SELECT is not telling you what you think it is telling you, because the value shown in rows is an estimate of the number of rows the server will need to consider, not the actual rows it will examine.  For InnoDB these are based on index statistics.

rows
The rows column indicates the number of rows MySQL believes it must examine to execute the query.
For InnoDB tables, this number is an estimate, and may not always be exact.
— http://dev.mysql.com/doc/refman/5.5/en/explain-output.html#explain_rows

The optimizer gathers information about different possible query plans, and chooses the one with the lowest cost.  The information shown in EXPLAIN is the information the optimizer gathered about the plan it selected.
When type is ref and key is not NULL, this means that the name listed in the key column is the name of the index that the optimizer has chosen to use to find the desired rows, so your query plan looks exactly as it should.
Note, sometimes you will see Using index in the Extra column and a lot of people assume that this means an index is being used, or that no index is being used when that doesn't appear, but that's not correct, either.  Using index describes a special case called a "covering index" -- it does not indicate whether an index is being used to locate the rows of interest.
It's possible that running ANALYZE [LOCAL] TABLE would cause the numbers in rows shown by EXPLAIN to differ, but this is a simple query and selecting this index is an obvious choice for the optimizer to make, so ANALYZE TABLE is unlikely to make any actual difference in performance.
It is possible, however, that your overall performance might see some marginal improvement with an occasional OPTIMIZE [LOCAL] TABLE, because you are not inserting rows in primary key order (as would be the case with an auto_increment primary key)... but on large tables this can be time-consuming because it rebuilds a new copy of the table... but, again, I wouldn't expect any significant change.


Get output in comma separated format in sql server

You can use + or the CONCAT function to combine multiple columns into a single string. If you need the column names at the top you can do that with UNION ALL.

Here is your sample data:

CREATE TABLE #X_164768 (
TableName SYSNAME NOT NULL,
TableRows BIGINT NOT NULL,
DataSpaceMB NUMERIC(10,2) NOT NULL,
IndexSizeMB NUMERIC(10,2) NOT NULL
);

INSERT INTO #X_164768
VALUES
('Sec', 2, 0.02, 0.02),
('Key',2, 0.02, 0.01),
('name', 3, 0.01, 0.02),
('agenda', 1, 0.01, 0.05),
('Configuration', 19, 0.01, 0.02),
('Policies', 2, 0.01, 0.01);

Here is one way to get the output you want:

SELECT 'TableName,TableRows,DataSpaceMB,IndexSizeMB'

UNION ALL

SELECT
Tablename
+ ',' + CAST(TableRows AS VARCHAR(20))
+ ',' + CAST(DataSpaceMB AS VARCHAR(20))
+ ',' + CAST(IndexSizeMB AS VARCHAR(20))
FROM #X_164768;


How to Create users in sql server for accessing only one database using Management Studio

Your answer is right here in SO already: Restrict SQL Server Login access to only one database. Just in case the link is broken:
1. Connect to your SQL server instance using management studio
2. Goto Security -> Logins -> (RIGHT CLICK) New Login
3. fill in user details
4. Under User Mapping, select the databases you want the user to be able to access
   and configure

You can add an user either using SSMS or by using CREATE USER. You could use sp_adduser but it's not recommended as it will be removed in a future version of Microsoft SQL Server.
You also need a login to match that user. Again, you can use SSMS or CREATE LOGIN for the task.
Check BOL's page for database roles on SQL Server 2008R2, use the dropdown menu on the top of that page to switch to other version if need to.
Here is the list of the roles that can be assigned:

db_owner Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.
db_securityadmin Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.
db_accessadmin Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareader Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.


What happens when you restore the same transaction log multiple times?

Restoring the same transaction log over and over buys you nothing.  The changes in the log will have already been applied to the database files after the first restore and no further action is needed on subsequent restores of the same transaction log backup.

Also, keep in mind that you cannot access a database until you have issued the with recovery command UNLESS you are using a standby file which will enable read only access.

Depending on WHY you are wanting to keep in sync with your production database, it sounds like you might want to research log shipping


How to create BAG on non-wsfc nodes (SQL Server 2016 STANDARD Edition)

Is it, or is it not possible to configure a Basic Availability Group on a database present in two AD-Servers with each its own standard edition SQL2016sp2 cu2 (default instance) (Both had the SSMS 16.5.3 management studio – now upgraded to SSMS 17.9) None of them are configured as members in any failover cluster.

As far as my knowledge goes the BAG's require WSFC and you cannot configure BAG without WSFC for even SQL Server 2017, till now at the time of writing the answer. MS technology is changing too fast and who knows you might be able to do it in future. The option of "clusterless" AG is for "full fledged" availability groups and not for the basic one and again the feature comes from SQL Server 2017 vNext and not from SQL Server 2016. So if you are using SQL Server 2016 and trying to configure clusterless BAG, it is not possible


Working memory on the connection string for postgres instance

Only a select list of parameters are allowed directly in the connection string.  Others must be imbedded via the "options" parameter.  This leads to much ugliness and escaping and quoting challenges.

Here is an example that sets work_mem to 1234 kB:

psql "postgresql://jjanes@localhost/jjanes?connect_timeout=10&options=-c work_mem%3D1234"


'Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation '='' on query

NAME_CONST is for internal use and has no possibility to convert text or use a collation
use the conventional way with CAST or CONVERT
UPDATE site_user_master
SET
    is_active = 'N',
    modification_date =  1615022896
    ,passkey =  CONVERT(_latin1'9419' USING latin1_swedish_ci)
WHERE
    email_id = CONVERT(_latin1'Raj.fm.kangra@gmail.com' USING latin1_swedish_ci)

Also it is not a good practrice to save passwords in plain text, there are a lot of function to hash the password https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html


Oracle DB - Is it possible to distribute data of a table onto multiple servers?

The short answer is: NO, Oracle does not do this in the way you propose BUT:

there is a way to emulate this but don't forget that Oracle has a share everything architecture, meaning that all data is available for all instances on all nodes. Using services and application code that connects to specified services running on their own instances can use specified partitions of tables to partition the application. This is a big effort to create.

Another (BETTER) way is to use Active Data Guard and have your application perform all queries on a physical standby database. With Active Data Guard you can ensure that they always contain up to date data. If you need to do an update/insert/delete operation, do that on the single primary database. Doing so takes away the reading load from the primary and also enables the standby databases to use their own storage, even in their own Data Center. If you really want to scale out, this is the way to do that with Oracle. At any time you can exchange the role of a standby with the primary database allowing for maintenance, giving very good availability. There are several ways to select an instance on which to perform the queries, like round-robin but also based on location or load.


When you move a database from one server to another via backup /restore, the users within the database stay intact .However, the logins associated with the users might not exist in the destination server.

For this to work as expected, you will need to move the associated logins and then check for any orphan users.

Use this link to get the script for transferring the logins. You can verify the logins with the users in the database and move only those:

How to transfer logins and passwords between instances of SQL Server (Microsoft Support)
Once the logins associated with the particular databases are moved, check for any orphan users. This blog will help you understand more about this:

Understanding and dealing with orphaned users in a SQL Server database by  Atif Shehzad

EXEC sp_change_users_login 'Report'

EXEC sp_change_users_login 'Auto_Fix', 'user'


When we are estimating the table size for mySQL table, why do we add primary key twice?

why do we add primary key twice?

This is an effect of InnoDB index structure.

MySQL 8.0 Reference Manual  /  ...  /  Clustered and Secondary Indexes

  All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index.

In table structure shown in the thread there is 2 indices: primary (by ID) and secondary (by CountryCode). The PK expression value is included in both - explicitly in PK and implicitly in SI. It is described in RolandoMySQLDBA's answer.

If there were three indexes in the table, the size of the primary index would be counted three times. 4 indexes - 4 times. And so on...

If no PK / UK in the structure then InnoDB adds inner 6-byte record number as a PK, and you must add 6 * (1 + indices count) bytes to each record's indices storage size.


How to find column with all same repeating character

Actually there is REPLICATE() function:

WHERE <column> = REPLICATE('0',15)

Funny thing, it's actually using 17 characters, exactly the same as the '000000000000000' literal.

(Reading again the question, I suppose you don't want the above either because it has the 15 hard coded.)

The LIKE condition you have is not working as expected because the LIKE '[^0]' would be true only if the column had exactly 1 character. I think you need to check if it has only 0s or not:

WHERE <column> NOT LIKE '%[^0]%'

To check if it has only 0s or only spaces, this do (for CHAR and VARCHAR columns) and you don't even have to trim the column:

WHERE <column> = ''
   OR <column> NOT LIKE '%[^0]%'


SQL Server - Does adding a subscriber to an existing publication generate a new snapshot or create locks on the publisher DB?

No it directly does not, but a new snapshot needs to eventually be generated in case the data or schema has changed since the last snapshot was generated before replication begins for that subscriber, and regardless a snapshot needs to be initialized in the new Subscriber database as a starting point for further transactions that occur. BUT it doesn't have to happen at the point that you add the Subscriber. There's an option you can select for when to initialize and instead of selecting "Immediately" you can select "Manual". Then at your convenience (off-hours if needed) you can right click on the Publication and manually generate a new snapshot for that subscriber.

The locks on the Publisher from initializing a snapshot should be rather minimal as replication is a fairly efficient feature of SQL Server. But the part that adds the actual Subscriber doesn't cause any locks to the Publisher.


Should Archive Tables have their own Surrogate Identity Key

Fragmentation is not as bad as you think it is. Page fullness (internal fragmentation) is much more important than the physical order of the pages (external fragementation).

Having rows inserted out of order might affect the performance of scans when the pages are read from disk. When reading pages from memory, it is not important whether the pages are in the correct order or not. Also, you are probably not working with a physical server and your disk is likely a LUN sitting on a SAN, so the advantage of page contiguity is negligible.

A good visual description of internal vs. external fragmentation can be found here: http://www.sqlservercentral.com/blogs/practicalsqldba/2012/04/05/sql-server-index-fragmentation-understanding-fragmentation/

A great article on why fragementation is not what you think can be found on Brent Ozar's blog: https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/

That said, using an additional surrogate key just for avoiding fragmentation is not a good idea. A good clustered index is, first of all, the key that you use the most to locate the data, so that you don't have to perform additional lookups. If you alter the clustering key when you archive the data, to locate the rows you would have to use a unique nonclustered index on the column(s) of the previous clustering key. A nonclusterd index is a copy of the data that requires additional space and also requires lookups to extract columns that are not part of the index.

Wrapping up: no, you don't need an additional "archiveId" surrogate key.


Moving a date between tables is causing error ORA-01841

Zgguy answered this in comments:

Explicitly stating columns in the insert solved the problem, as it was trying to put columns in the wrong columns.

INSERT INTO BRAND_RECORD (STRENGTH, GENERIC_NAME, INTERCHANGEABILITY_DATE)


Is there a PostgreSQL proxy running queries on several servers at once?

Have you checked pgpool already? It has query replication as a feature (look for 'parallel query').


Question about composite indexes and WHERE + GROUP BY

The index isn't covering since B isn't part of the WHERE clause as a predicate, so the index is not applicable to this query. In other words, the index stores the data sorted by A then by B then by C, but since you're only filtering on A and C, there's no direct way to seek out C without B. You might end up seeing an index scan or table scan get used instead, in this case.


Permissions issue in Docker SQL Server 2017 while restoring certificate

Is the code shown under "Relevant code:" (in the question) the exact code being executed, as in copied and pasted into here and not re-typed? I ask because the file extension on the sqlserver_backup_cert file is different between the BACKUP and CREATE (i.e. restore) statements. It is backed-up as .cer but then you are looking for .crt in the CREATE.

Also, have you tried skipping files altogether and using hex bytes (i.e. a VARBINARY literal)? You would do the following:

On Prod

SELECT CERTENCODED(CERT_ID(N'sqlserver_backup_cert')) AS [CertificateAndPublicKey],
       CERTPRIVATEKEY(CERT_ID(N'PrivateKeyTest'),
                      'new_password_for_extract(A)',
                      'current_password(B)') AS [PrivateKey];

On Test

CREATE CERTIFICATE [sqlserver_backup_cert]
    FROM BINARY = {output of CERTENCODED}
    WITH PRIVATE KEY (
        BINARY = {output of CERTPRIVATEKEY},
                 DECRYPTION BY PASSWORD = 'new_password_for_extract(A)',
                 ENCRYPTION BY PASSWORD = 'current_password(B)'
                     );

UPDATE

O.P. has confirmed that the issue was indeed the filename extension mismatch.


MYSQL: weird bug with nested stored procedure

Beware when using the same name:

WHERE  `language_code` = language_code

Suggest you precede any arguments or locally DECLAREd variables with a _.

You can probably combine both procedures into a single SQL statement.


Is it possible to drop export some shema without data and the other with data

You can export specific schema using the SCHEMAS parameter:

http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#i1007509

You can export the definitions of data alone using the CONTENT parameter:

http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL838

Combine the two:
        expdp user/pass SCHEMAS=a,b,c CONTENT=METADATA_ONLY [........]

This will achieve the aim of just doing the schema.  You'll need to do the 3 schema where you want the data as a separate job.


In this case, while an index could be used (not in the current state, but if the index changed its column order a bit) to speed up the ordering, the optimizer has to decide between favoring the filtering or the ordering.

As it probably starts by filtering by the constant a.checkNo ='893881996', and accessing first the table a, it would be impossible to get the records in b.claimSno order (even with different indexes).

As such, a sorting process is required at the end of the row retrieval, and that requires a temporary table. Do not be too worried unless the difference in time between each execution is very high.

If the table ends up being created on disk, you can try tuning tmp_table_size and max_heap_table_size for that session to try to avoid that - although with the couple of varchar(255) that you have, it probably will be worse, not better. If the number of sort_merge_passes is very high, you can try tuning the sort_buffer_size for that session.

On the other hand, if the query is running fast enough for you, you do not need to worry as it is something normal.


When tables are locked does MySQL queue queries?

Since OPTIMIZE TABLE, ANALYZE TABLE, and REPAIR TABLE are DDL, full table locks are required.
However, if all data is InnoDB, the latest version of MySQL is a little more lenient with DDL locks. Note the current MySQL Documentation on OPTIMIZE TABLE in InnoDB:

Prior to Mysql 5.6.17, OPTIMIZE TABLE does not use online DDL
(ALGORITHM=INPLACE). Consequently, concurrent DML (INSERT, UPDATE,
DELETE) is not permitted on a table while OPTIMIZE TABLE is running,
i.e. the table is locked. Also, secondary indexes are not created as
efficiently because keys are inserted in the order they appeared in
the primary key.
As of 5.6.17, OPTIMIZE TABLE uses online DDL (ALGORITHM=INPLACE) for
both regular and partitioned InnoDB tables. The table rebuild,
triggered by OPTIMIZE TABLE and performed under the cover by ALTER
TABLE ... FORCE, is now performed using online DDL (ALGORITHM=INPLACE)
and only locks the table for a brief interval, which reduces downtime
for concurrent DML operations.

With ANALYZE TABLE

ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for InnoDB and MyISAM. This statement works with InnoDB, NDB, and MyISAM tables. For MyISAM tables, this statement is equivalent to using myisamchk --analyze.

REPAIR TABLE makes no apologies. The documentation sheds no light on reads, so SELECTs would be outright blocked until the repair is completed.
All queries that are writes (INSERT, UPDATE, DELETE) would be queued until those full table locks are released. This is true regardless of the storage engine being InnoDB or MyISAM.
SUMMARY
Read locks mitigate SELECTs depending on the command

ANALYZE TABLE will permit SELECTs
OPTIMIZE TABLE will try to minimize lock time
REPAIR TABLE has nothing to offer queries but waiting


Well, if you have full access to the database host you don't need to delete and re-create the password file.

According to Using Operating System Authentication section in Database Administrator's Guide logging in as an OS user which is a member of dba group on *nix or ora_dba group on Windows and connecting to an Oracle Database instance using SQL*Plus with SYSDBA or SYSOPER privileges should do the trick.

$ ssh root@orcldbxe1
[root@orcldbxe1 ~]# su - oracle
[oracle@orcldbxe1 ~]$ groups
oinstall wheel dba
[oracle@orcldbxe1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Aug 22 08:22:35 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL>

That's why it's called operating system authentication – you use operating system group. Simple and convenient, and no need to remember passwords. I always use it.

Once you're in, you can reset passwords and do other administrative tasks.

SQL> alter user spongebob identified by s3cr3t;


UPDATE (April 14 2016):

From SQL 2016 – It Just Runs Faster: Indirect Checkpoint Default on the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support:

  Indirect checkpoint is the recommended configuration, especially on systems with large memory footprints and default for databases created in SQL Server 2016.

Does indirect checkpoint takes into considerations number of dirty buffers instead of number of transaction which usually automatic checkpoint was using?

Once we have set the target_recovery_time, SQL Server internally calculates the Target Dirty Buffer threshold. As the transactions are logged in the transaction log, a Dirty Page List keeps track of the LSNs and dirty buffers which are modified by the transaction. So for indirect checkpoints, the dirty buffers from each transaction along with the LSN are being tracked.

The Recovery Writer (a new background process in 2012) periodically polls the Dirty Page List and if it finds a number of Dirty Pages in the Buffer Pool greater than the Target Dirty Buffer threshold it flushes the Dirty Buffers and moves the minLSN forward.

Does indirect checkpoint setting is more suitable for Data Warehouse vs OLTP type workload?

From BOL, an online transactional workload on a database that is configured for indirect checkpoints could experience performance degradation. This is because the background writer used by indirect checkpoint sometimes increases the total write load for a server instance.

What scenarios you consider for SQL database in question before you start leveraging indirect checkpoint?

This depends on many factors as each environment is different interms of hardware, memory, transactions occuring, etc . I would recommend you to TEST and double TEST this particuliar feature as it can be helpful to you or else can cause detrimental effect on your workload and possibly jeopardise your databases as well. By default, target Recovery time is set to ZERO meaning, change it if you have tested it thorougly and its behaviour is acceptable to you ...

Advantages :

It may improve database recovery time
It may reduce checkpoint I/O as it writes continuously pages to the disk in the background

Disadvantages :

In OLTP workload it can increase overall writes on server by writing continuously pages to the disk in the background which may reduce the performance.

Ref:

Setting indirect checkpoints by database in SQL Server 2012

How do checkpoints work and what gets logged ?


How do you remove fragmentation from InnoDB tables?

I have addressed this in StackOverflow back in October 2010.

Keep in mind the busiest file in the InnoDB infrastructure : /var/lib/mysql/ibdata1

This file normally houses four types of information

Table Data
Table Indexes
MVCC (Multiversioning Concurrency Control) Data
Table Metadata (List of tablespace IDs)

Running OPTIMIZE TABLE against an InnoDB table stored in ibdata1 does two things:

Makes the table's data and indexes contiguous inside ibdata1, thus faster to access
It makes ibdata1 grow because the contiguous data and index pages are appended to ibdata1

While you can segregate Table Data and Table Indexes from ibdata1 and manage them independently using innodb_file_per_table, the big gaping whole of diskspace in ibdata1 simply won't disaapear and cannot be reclaimed. You must do more.

To shrink ibdata1 once and for all you must do the following:

1) MySQLDump all databases into a SQL text file (call it /root/SQLData.sql)

2) Drop all databases (except mysql schema)

3) Shutdown mysql

4) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

5) Delete ibdata1, ib_logfile0 and ib_logfile1

At this point, there should only be the mysql schema in /var/lib/mysql

6) Restart mysql

This will recreate ibdata1 at 10 or 18MB (depending onthe version of MySQL), ib_logfile0 and ib_logfile1 at 1G each

7) Reload /root/SQLData.sql into mysql

ibdata1 will grow but only contain table metadata. In fact, it will grow very slowly over the years. The only way ibdata1 growth quickly is if you have one or more of the following:

A lot of DDL (CREATE TABLE, DROP TABLE, ALTER TABLE)
A lot of transactions
A lot of changes to commit per transaction

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

mytable.frm (Storage Engine Header)
mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable; and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I have done this many times in my career as a MySQL DBA

In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 500MB.

Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul !!!

UPDATE 2012-04-19 09:23 EDT

After running the above steps, how can you determine what tables need to be defragmentated? It is possible to find out, but you will have script it.

Here is an example: Suppose you have the table mydb.mytable. With innodb_file_per_table enabled, you have the file /var/lib/mysql/mydb/mytable.ibd

You will have to retrieve two numbers

FILESIZE FROM OS : You can ascertain the filesize from the OS like this

ls -l /var/lib/mysql/mydb/mytable.ibd | awk '{print $5}'

FILESIZE FROM INFORMATION_SCHEMA : You can ascertain the filesize from information_schema.tables like this:

SELECT (data_length+index_length) tblsize FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='mytable';

Just subtract INFORMATION_SCHEMA value from OS value and divide the difference by the INFORMATION_SCHEMA value.

From there you would decide what percentage deems it necessary to defrag that table. Of course, you defrag it using one of the following commands:

OPTIMIZE TABLE mydb.mytable;

or

ALTER TABLE mydb.mytable ENGINE=InnoDB;


PUBLIC privilege potential security threat - Oracle database

You cannot revoke the public pseudo-role from users.  Every user always has public.

You can revoke privileges from the public role.  The first question would be whether these privileges were granted by default by Oracle, in which case revoking them might prove difficult.  It might, for example, break upgrades and patches and/or be re-granted by future upgrades and patches.  There are ways to reduce the default set of grants made to public but that tends to be quite a bit of work for very little benefit:

In a default Oracle install, a user that has just create session cannot create objects, cannot read objects in another user's schema, and certainly cannot modify data in another user's schema.  Only if someone in your organization were crazy enough to do something like granting update any table to public would that happen.  If someone has granted a raft of powerful privileges to public beyond what Oracle ships with, that would be a concern absent a compelling justification for each grant.

On the other hand, if a user that has no privileges other than create session can modify data in a system table, though, that implies that you (or someone in your organization) has granted a ton of object privileges to the public role.  Absent a rather compelling justification for each of those grants, that would definitely be a concern.  That really should have been done by creating appropriate roles for your application and granting those roles to whatever users needed them.  If you created objects in the system schema, that would be another source of concern-- only Oracle should be creating objects in sys or system.

Real applications should not be using the connect or resource roles.  If you look at the actual privileges those roles grant, they are likely to be both far more and far less than you would expect.  Plus they change across versions-- Oracle has been locking down the roles in later versions because people were granting them freely without understanding the implications of those grants.

The public role exists because certain privileges rightly should be given to every user by default.  It seems likely, for example, that you want a new user to be able to query user_tables to see the tables they own or all_tables to see what tables they have privileges on.  It would be terribly annoying if every application had to ship with the full list of every data dictionary table it needed to query (some of which would vary based on how different JDBC/ODBC/OLE DB/ etc. providers implemented certain API functions).  It is useful to have a small baseline of functionality that you get by default.


No need to check for existence. Just wrap your statement with an exception handler that ignores away any ORA-00955 it encounters (or modify for whatever exception number you want to ignore):

DECLARE
  ObjectExists EXCEPTION;
  PRAGMA EXCEPTION_INIT(ObjectExists,-955);
  sql_stmt VARCHAR2(100) := 'create table my_table (column_1 int)';
BEGIN
  EXECUTE IMMEDIATE sql_stmt;
-- Ignore ORA-955 errors (object already exists)
EXCEPTION WHEN ObjectExists THEN NULL;
END;


Moving a large table from Localhost to remote server

CREATE TABLE destschema.dest_table AS
SELECT fid, wkb_geometry FROM dblink('host=localhost
                     user=postgres...

This statement could be used to transfer between databases on the same machine, but not across different machines. When you're logged to the AWS database, localhost designates the AWS host, not your PC. This is why the credentials of your PC don't work.

Copying the output of pg_dump to the remote, as a file and playing it there locally is probably the fastest way to achieve the transfer. Steps:

on the PC: pg_dump.exe -Fc [other options] dbname -f dumpfile
-Fc as an option selects the custom format, which has the advantage of compressing the dump.
transfer dumpfile to the remote AWS host, with a method of your choice. On Unix scp would probably be used: scp dumpfile username@remotehostname.amazonaws.com:. On Windows, it may also be available if it got installed, but otherwise any sftp client should work.
in a shell on the remote host, restore the dump: pg_restore dumpfile | psql [options] dbname.

If you only need to dump one table (as opposed to an entire database), see the -t option of pg_dump.

For the method executing SQL remotely (that is, on your PC, psql.exe -h remotehostname.amazonaws.com [other options] or the equivalent with pgAdmin connecting to the remote), note that compression is not used unless it happens at the network layer, and with 20GB to transfer, you really want to benefit from compression.


It is mandatory to create a route when creating an event notification?

If you are using WMI event alerts for server events then service  broker is not required as it uses Service Broker SQL/Notifications/ProcessWMIEventProviderNotification/v1.0 in msdb and Service Broker is enabled by default for msdb for 2005 and up.

Read up on - understanding WMI provider for server events

To Answer your questions :

  But I see lot of recommendations to use service broker too. What's the difference between them?

Its your choice to use either Service Broker or WMI Providers for server events. I find WMI Providers for server events more easy to implement.

Basically, using Event notifications allows you to capture information using SQL Server Service Broker by creating a service and queue for the events. For e.g. if you set up Event notification to capture DEADLOCK GRAPH then 3 service broker objects are created :

A QUEUE to hold up DEADLOCK_GRAPH event messages
A SERVICE to route the messages to the queue and
An Event NOTIFICATION to capture the deadlock graph and package it in a message that is sent to the Service.

For WMI Providers for server events - Any event that can be captured through event notifications has a corresponding WMI Event Object, and any WMI management application can subscribe to these event objects. SQL Server Agent was updated to manage WMI events, through the use of WMI Query Language (WQL), a query language similar to T-SQL that is used with WMI and Agent Alerts for WMI events.

  By using WMI I can avoid the hassle of creating service/queues?

Yes but it is in no way a replacement of Service Broker. Event Notification uses Service Broker. WMI Provider for server events uses WMI event objects.

  WMI is broader and allows to track more event types? Or it is something else?

WMI can only handle these DDL Event Groups. Service Broker is much broader.

For monitoring and alerting on Deadlocks, you can use my script.

Refer to : Jonathan's excellent article on Handling Deadlocks in SQL Server


When it comes to MySQL, there is no comparison among storage engines except that it falls into two basic categories:

Transactional (ACID-compliant)
Non-Transactional

MySQL features the use of several storage engines

InnoDB (default as of MySQL 5.5)
MyISAM (default prior to MySQL 5.5)
ARCHIVE
BLACKHOLE
CSV
MEMORY
MRG_MYISAM
FEDERATED
NDB a.k.a. MySQL Cluster

As for the storage engines listed, the only ones that have ACID-compliance are InnoDB and NDB. Why is this importatnt to mention? Two reasons:

Other storage engines are simply not benefited with the presence of more cores, other than basic disk I/O, CPU usage, and overall throughput.
The code for each non-transactional storage engine, which dictates basically 14 internal operations regardless of storage engine, was not designed to leverage the accessing of multiple cores.

InnoDB under MySQL 5.5, InnoDB Plugin), and Percona Server's XtraDB have options you can set in order to access multiple cores (Percona Server has been doing so longer). In fact, Percona injects about 30,000 lines of code specifically for performance enhancement of InnoDB with each new GA release of MySQL source code. We can be sure Oracle has included its own enhancements from their own think tank to run within InnoDB for multicore operation (since MySQL 5.1.38).

With the need to perform MVCC on data in conjunction with row/page locking, transaction performance can now be instrumented, measured, and configured.

If there is one thing I have learned about using multiple cores, it is that you must tune InnoDB effectively and not just rely on InnoDB out of the box.

UPDATE 2011-09-20 08:03 EDT

With regard to InnoDB benefiting from all cores, we need to keep things in persepctive. The cores must also tend to other matters (OS, Disk, Memory, Applications, Monitoring, etc.) in the Database Server. For those with modest budgets, many tend to have a Database Server also provide NFS, monitoring from Munin, app support for JBoss, PHP, and the list goes on. If you want MySQL, more specifically InnoDB, to use more cores, the Database Server must be dedicated exclusively to MySQL and the OS/Disk/Memory must tend to MySQL only. Given this perspective, InnoDB will engage more cores withtout a doubt.

As for InnoDB Plugin, it was mentioned simply to show earlier initiatives to have a better InnoDB on the part of MySQL (eh, Oracle. Sorry, still doesn't roll off the tongue yet). New variables to summon more core activity became evident from MySQL 5.1.38.

For example, innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64. The default and max settings being so different (4 - 64) shows that InnoDB is as multithreaded and core intensive as you configure it !!!

Addressing the needs of the MySQL community to access more cores with InnoDB was lead by Percona. Consequently, MySQL started to follow suit. I have to admit that Oracle (yuck) made the necessary improvements for more core activity.


Change Tracking Bad Plans in SQL Server 2016

Based on your question it sounds like you have a highly concurrent application that calls CHANGETABLE on a variety of tables in many different queries. Forcing each query plan to have a specific shape with a USE PLAN hint or a plan guide would be a Herculean effort. Changing your active database to have a compatibility level of 110 without query fixes may be a bad option for other reasons.

The suggestion in this answer could be difficult to implement but it feels like a better option than the ones listed above. The idea is to replace the call to CHANGETABLE with a stored procedure that inserts the data from CHANGETABLE into a temp table. Queries that need change data can use the temp table populated instead of CHANGETABLE directly. Your application code might look like this:

DECLARE @CurrentVersion INT = CHANGE_TRACKING_CURRENT_VERSION () - 1;

CREATE TABLE #changes (SYS_CHANGE_VERSION bigint NULL);

EXEC dbo.CHANGETABLE_API '#changes', 'TEST', 'dbo', 'ChangeTable_Test', @CurrentVersion;

-- run queries that need #changes temp table

DROP TABLE #changes;

Working around scoping issues requires a few tricks. The stored procedure has three parts:

Add static columns to the temp table: SYS_CHANGE_CREATION_VERSION, SYS_CHANGE_OPERATION, SYS_CHANGE_COLUMNS, and SYS_CHANGE_CONTEXT
Add dynamic primary key columns to the temp table. You can use sys.columns and other dmvs for this.
Insert into the temp table from CHANGETABLE under the context of an empty database with 2012 compatibility level and whatever other settings you need to get good performance.

Below is a quick and dirty implementation of most of the above. It doesn't have any kind of error checking or protection against SQL injection attacks. Feel free to do whatever you want with the code:

CREATE OR ALTER PROCEDURE dbo.CHANGETABLE_API (
    @temp_table_name SYSNAME,
    @changes_database_name SYSNAME,
    @changes_schema_name SYSNAME,
    @changes_table_name SYSNAME,
    @last_sync_version BIGINT
)
AS
BEGIN
    DECLARE @sql_to_add_static_cols NVARCHAR(4000),
    @sql_to_add_table_cols NVARCHAR(4000),
    @sql_to_insert_rows NVARCHAR(4000);

    -- okay to hardcode these depending on how you call CHANGETABLE
    SET @sql_to_add_static_cols = N'ALTER TABLE '
    + QUOTENAME(@temp_table_name)
    + N' ADD '
    + N'SYS_CHANGE_CREATION_VERSION bigint NULL, '
    + N'SYS_CHANGE_OPERATION nchar(1) NULL, '
    + N'SYS_CHANGE_COLUMNS varbinary(4100) NULL, '
    + N'SYS_CHANGE_CONTEXT varbinary(128) NULL';

    EXEC (@sql_to_add_static_cols);

    -- this should be dynamic based on sys.columns and other dmvs
    SET @sql_to_add_table_cols = N'ALTER TABLE '
    + QUOTENAME(@temp_table_name)
    + N' ADD id BIGINT NOT NULL';

    EXEC (@sql_to_add_table_cols);

    -- key is to run the insert in a database with the settings that you need for a good query plan
    SET @sql_to_insert_rows = N'USE DB_2012_COMPAT; '
    + N'INSERT INTO '
    + QUOTENAME(@temp_table_name)
    + N' SELECT * FROM CHANGETABLE(CHANGES '
    + QUOTENAME(@changes_database_name)
    + N'.' + QUOTENAME(@changes_schema_name)
    + N'.' + QUOTENAME(@changes_table_name)
    + N', ' + CAST(@last_sync_version AS NVARCHAR(30))
    + N') AS C';

    EXEC (@sql_to_insert_rows);
END;

When I call the stored procedure in a database with compatibility level 130 I get a query plan that only does seeks as desired:

In the same session if I run the following code:

DECLARE @CurrentVersion INT = CHANGE_TRACKING_CURRENT_VERSION ()-1;
SELECT * FROM CHANGETABLE(CHANGES ChangeTable_Test,@CurrentVersion) AS C
OPTION (RECOMPILE);

I get a scan which you wanted to avoid: