CLR Assemblies and SQL Server Availability Groups I have a SQL 2012 Availability group. On that I also have a database with a .Net assembly that is using PERMISSION_SET = UNSAFE.

CREATE ASSEMBLY drawing
  FROM C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Drawing.dll'
  WITH PERMISSION_SET = UNSAFE;
GO

When the system fails over to the Secondary Node the Assemblies don't work with an error like:

  Could not load file or assembly System.Drawing....

I can't seem to find a good reference on how CLR assemblies function in an Availability Group. Any assistance is greatly appreciated.

When you create a mirror of the database the "trustworthy" flag is set to false by default. And you need to sync the SID of the dbo on each side (I don't understand how this works for the SA user. Need to study more.

To resolve the issue I failed the database over to the secondary node. Set Trustworthy to True. After that I attempted to run the Procedure and got a failure related to the SID not being registered (sorry forgot to capture the message). Since the database was owned by SA i decided to remove and readd the assemblies.

After removing and readding the assemblies i was able to then run the assembly on the node. At that point I failed everything back to the primary node where I was able to run the assembly. So I failed back and forth a few times and the assembly works on both sides. While this worked for us I'm still not entirely certain how SIDs for the SA user work on both sides.

To summarize:
- You have to failover to the secondary to set Trustworthy to True
- While the secondary was in read-write Drop and ReADD the assemblies on the second node (while acting as primary)
- That seemed to enable the assembly to run on both sides.

Thanks to Aaron and srutzky who guided me to a working solution. If anyone can better explain why what I did worked I'd be glad to know.


What's the most effective way to duplicate an instance on SQL 2008? We've got a simple database instance with just enough data in the databases to keep it useful, and we can use it for demonstration purposes, and I want to put a copy of it on my laptop so I can use it for dev work when I'm not at the office. (The regular dev database instances are about 150GB, this one instance is 3GB)

What's the most repeatable/scriptable method for recreating the instance on my laptop? I'm sure I'm going to have to run through the installer again for SqlServer2008, and set up an instance, but after that ... I'm thinking I want to be able to drop my old databases every month or so and copy all the new stuff over to my laptop, as my instance won't change on either the one demo system or the laptop. But the databases themselves will update, with refreshes on the sprocs and the like.

I've considered scripting all the "drop database-files; add database-files {path}" and keeping them in a runnable batch file of some sort, and then I can "drop, delete, copy, add" but wondered if there was a better way than just xcopy and batch?

I'm trying to do this on the cheap (don't we always try and do things on the cheap) so I'm not particularly interested in RedGate products or the like. I want something I can maintain easily in a text editor for myself.

You've hit the nail on the head: copy the files. I've used this to good effect.

I'd say you have to define "cheap" to include investment of time to prepare a solution that doesn't involve xcopy


PostgreSQL ignoring (unique) index on TEXT field I have a users table similar to

CREATE TABLE users (
  id bigserial NOT NULL,
  msisdn text NOT NULL
);

and a UNIQUE constraint defined on msisdn:

ALTER TABLE users ADD CONSTRAINT UNIQUE (msisdn);

Now, I tried running

EXPLAIN SELECT * FROM users WHERE msisdn = '000000000001';

and PostgreSQL is returning

Seq Scan on users  (cost=0.00..7.80 rows=1 width=400)
  Filter: (msisdn = '000000000001'::text)

The table has 256 rows at the moment. Could it be that the table is so small that PostgreSQL thinks it's better to do a full-table scan instead of scanning the index? How can I make sure that PostgreSQL actually uses the index instead of doing a full-table scan?

ypercubeᵀᴹ and a_horse_with_no_name were right:

I had too few rows in the table. For just 256 rows an index gives no benefit at all.

I added 100,000 extra rows and EXPLAIN started showing usage of the index.


How can I order rows returned by the RETURNING clause of a DML statement? I am using this statement to get all the rows ordered by timestamp in ascending order

DELETE FROM @tableName
        WHERE id = ANY (
            SELECT id
            FROM @tableName
            WHERE source = :p1 AND target = :p2 @readCondition
            ORDER BY createddate
            LIMIT @limit
            FOR UPDATE SKIP LOCKED
        )
        RETURNING *;

And I am getting these results :

"MessageType":"AssignmentChange.v1","CreatedDate":"2019-12-05T10:55:22.230886"

"MessageType":"AssignmentChange.v1","CreatedDate":"2019-12-05T10:55:22.279604"

"MessageType":"AssignmentChange.v1","CreatedDate":"2019-12-05T10:55:22.276191"
"MessageType":"AssignmentChange.v1","CreatedDate":"2019-12-05T10:55:22.202338"

As you can see they are not sorted by createdate. Is there a way to sort rows from the RETURNING clause?

You need to apply an ORDER BY to the result. To do that, put the DELETE statement into a common table expression. Then you can apply an ORDER BY when you select from it

with deleted as (
  DELETE FROM @tableName
  WHERE id = ANY (
        SELECT id
        FROM @tableName
        WHERE source = :p1 AND target = :p2 @readCondition
        ORDER BY createddate
        LIMIT @limit
        FOR UPDATE SKIP LOCKED)
  RETURNING *
)
select *
from deleted
order by createddate;


How to run OPTIMIZE TABLE on a 3-node cluster? I've my hands on a 3-node Percona XtraDB Cluster where, according to mysqlcheck, some tables are corrupted (some indexes contain the wrong number of entries):

mydb.mytable
Warning  : InnoDB: Index 'foo' contains 1393929 entries, should be 1393918.
Warning  : InnoDB: Index 'bar' contains 1393921 entries, should be 1393918.
error    : Corrupt

What is the best practice to run OPTIMIZE TABLE on a cluster?

I've done some experiments in a test environment without users, and it appears that an OPTIMIZE TABLE on a node does not automatically propagate its effect to the other nodes. This is consistent with the fact that this command modifies the indexes and the table's storage space, not its contents or its definition.

What could be the drawbacks in running the command in a production environment in each node, letting it complete before running it in the following node?
What would be the effect on users, considering that MySQL (and Percona XtraDB Cluster, as far as I know) do not support distributed table locks? Would this leave the cluster in a inconsistent state?

If there is a need to run OPTIMIZE TABLE in PXC, you could achieve this by denying its recording into binary logs into one of three(3) ways:

METHOD #1

SET sql_log_bin = 0;
OPTIMIZE TABLE mydb.mytable;
SET sql_log_bin = 1;

METHOD #2

OPTIMIZE NO_WRITE_TO_BINLOG TABLE mydb.mytable;

METHOD #3

OPTIMIZE LOCAL TABLE mydb.mytable;

SUGGESTION

Run the one of the above OPTIMIZE TABLE scenarios on one PXC node at a time while redirecting reads and writes from the cluster. If the data is huge, remove the PXC node from the cluster, run OPTIMIZE TABLE at will, and add the PXC back into the cluster.


SQL DBA data type is changed after upgrade We have recently upgraded our SQL Server Database from SQL 2005 to SQL 2014.
We are experiencing slowness in both SSMS and application as well while executing a SP in SQL 2014. After checking through the sql profiler.
This is the difference.

Source Server : SQL 2005
JDBC VERSION  : 4.1
Source Data type : Big Decimal
Conversion on DB side : It is converted as Numeric (Checked through sql profiler)
Execution Time : 1 Second

The below target is the server we have upgraded from 2005.

Target Server : SQL 2014
JDBC VERSION  : 6.2
SOURCE Datatype : Big Decimal
Conversion on DB Side : It is converted as Decimal
Execution Time : 25 Seconds.

Any idea what I can do to fix this?

You should perform the following tasks after updating :

Upgrade Database Compatibility Level

The database compatibility level needs to be manually changed on the 2014 instance

Run DBCC CHECKDB WITH DATA_PURITY

Checks for invalid data values based on column data type

Run DBCC UPDATEUSAGE

Corrects page count inaccuracies to reflect accurate information when using sp_spaceused

Run sp_refreshview

Ensures that views are up to date

Update Statistics

Brings database statistics are up to date to allow the query optimizer to choose the best query plan

Take a Full Backup of the Database
Make sure you can recover the database with all of the migration efforts
Monitor Performance between Cardinality Estimators

It’s important to note: SQL Server 2014 received a major overhaul of the cardinality estimator. For the vast majority of queries, performance will improve due to better row estimations. However, in some scenarios estimations could be worse which may result in performance degradation. It’s important to monitor most commonly used queries to see if any performance degradation is resulting from poor estimations. It is possible to utilize the old cardinality estimator by leveraging a database compatibility level of 110 or lower or by leveraging trace flag 9481.


SQL Server 2008 R2 Express for a single website? Someone recently told me I could use SQL Server 2008 R2 Express edition for a single website. But don't you need the Server Agent for connections (I think. I'm trying to remember things from over several years ago now!) and Express has it's Server Agent disabled?

You do not need the SQL Server Agent. It has nothing to do with database connectivity.

http://msdn.microsoft.com/en-us/library/ms189237.aspx

  SQL Server Agent is a Microsoft
  Windows service that executes
  scheduled administrative tasks, which
  are called jobs. SQL Server Agent uses
  SQL Server to store job information.
  Jobs contain one or more job steps.
  Each step contains its own task, for
  example, backing up a database. SQL
  Server Agent can run a job on a
  schedule, in response to a specific
  event, or on demand. For example, if
  you want to back up all the company
  servers every weekday after hours, you
  can automate this task. Schedule the
  backup to run after 22:00 Monday
  through Friday; if the backup
  encounters a problem, SQL Server Agent
  can record the event and notify you.


slow log contains query, but query time is less than long_query_time mysql> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
|          2.000000 |
+-------------------+
1 row in set (0.00 sec)

mysql> select @@log_queries_not_using_indexes
    -> ;
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set (0.00 sec)

but the slow query log contains:

# Query_time: 1.000111  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0 SET timestamp=1559116596; select sleep(1);

Is there any other variable that affects this?

If you add to my.cnf ( or my.ini )

min_examined_row_limit=1

the line should be omitted in the slow query log - in this case, no rows were examined.


Multiple databases through a single connection MongoDB, GridFS I want to use GridFS in your project. As the main database using mongodb.

My application can do only one connection to the database (such conditions).

Can I make calls to other databases using the primary as a proxy?

Yes, I had the same issue. I use the db.getSiblingDB('yourdb') command in my scripts to make many calls to various databases.

This is documented further at http://docs.mongodb.org/manual/reference/method/db.getSiblingDB/ .


Create a mysql replication from multiple master and single slave server? How to create mysql replication with multiple master server and with single slave server? how to edit the "CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  107;
" command to channelise different master db at the same time.

You need to modify the master and relay repositories to be stored into tables and then define channels for each master.

STOP SLAVE;
SET GLOBAL master_info_repository = 'TABLE';
SET GLOBAL relay_log_info_repository = 'TABLE';

CHANGE MASTER TO MASTER_HOST='master1', MASTER_USER='rpl', MASTER_PORT=3451, MASTER_PASSWORD='' \
MASTER_LOG_FILE='master1-bin.000006', MASTER_LOG_POS=628 FOR CHANNEL 'master-1';

Refer: https://dev.mysql.com/doc/refman/5.7/en/replication-multi-source.html


Determining Transitive Dependencies I am working on figuring out partial and transitive dependencies.  The Transitive ones are really confusing me because I can see a transitive dependency on nearly all of these.

The partial dependencies are Agree_No -> Agree_Date, Cust_No -> Cust_Name and Cust_Phone; Serial_No -> Item_Name and Item_Rate.

The Transitive Dependencies that I see are Item_Rate -> Item_Name.  But could Cust_Phone be a transitive to Cust_Name and the Agree_No/Date?  Are there others I am missing?

A FD (functional dependency) X -> Z is transitive when there exists a Y such that X -> Y AND Y -> Z AND NOT Y -> X AND NOT Y = Z. (X, Y & Z are attribute sets.)
There can be lots of X, Y & Zs where X -> Y AND Y -> Z but X -> Z is not transitive because Y -> X. Eg this happens whenever X and Y are CKs (candidate keys). (Because CKs functionally determine all attributes--each determines the other and Z.) And there can be lots of transitive FDs. Eg this happens whenever there is a FD whose attributes are non-prime. (Because a CK determines that FD's determinant which itself doesn't determine the CK--if it did then it would be a CK, which with non-prime attributes it isn't.) The FDs among all those ones that are important for normalization are transitive FDs of non-prime attributes on CKs when a relation is in 2NF. Because it is always possible to losslessly decompose such a relation to components free of them, ie in 3NF.


Could a large log file be the reason cron job keeps dying? The log is 15 Gigabytes. I guess I have to back it up and delete. I have enough hard disc space, but did not realize log was so large. And overnight cron jobs suddenly started to die in the middle.

If you are talking about the MongoDB log file becoming too large you should take a look at the log rotation options here:

http://www.mongodb.org/display/DOCS/Logging#Logging-Rotatingthelogfiles

Short version - you can rotate the logs via an admin command within MongoDB or you can send a SIGUSR1 - combine with your favorite log management method and you should be good.

Something else to consider though is why the log is so large - unless it has been running for a long, long time, 15GB is a lot of logging.  You may want to make sure that you are not starting up with logging set too high (-vv etc.).  You can also check the log level with the setParameter command.  For example, to set to 0 (the default), without restarting, just issue it like this:

db.adminCommand({setParameter:1, logLevel:0})


How to quote qualified table name with format() function? I have some problems in properly quoting a table name using the format function in the example below.

CREATE OR REPLACE FUNCTION copy_table(_source_tbl regclass, _target_tbl text)
 RETURNS bool AS $func$
DECLARE query_str text;
BEGIN
  query_str = format($fmt$ DROP TABLE IF EXISTS %1$I; CREATE TABLE %1$I AS (TABLE %s); $fmt$, _target_tbl, _source_tbl);
  EXECUTE query_str;
  RAISE NOTICE '%', query_str;
 RETURN True;
END $func$ LANGUAGE plpgsql;

My dilemma is that I want to quote the input table name _target_tbl as an identifier (to avoid SQL injection). However, given a full table name ex.test1, this causes the schema part ex. to be treated as part of the table name and the table public."ex.test1" created in the default public. schema as shown below.

How should I quote/format the identifier properly here?

=> SELECT copy_table('ex.test', 'ex.test1');
NOTICE:  table "ex.test1" does not exist, skipping
NOTICE:   DROP TABLE IF EXISTS "ex.test1"; CREATE TABLE "ex.test1" AS (TABLE ex.test);

=> \dt ex.test1
Did not find any relation named "ex.test1".
=> \dt "ex.test1"
         List of relations
 Schema |   Name   | Type  | Owner
--------+----------+-------+-------
 public | ex.test1 | table |
(1 row)

This is with PostgreSQL 10.3.

To avoid ambiguity in the target table name, provide names of schema and table separately. With existing tables Postgres can use the current search_path to default to the first schema with an object of that name for unqualified table names. Since that's obviously not possible for (yet) non-existing tables, we have to be more explicit there.

If you typically want to place the target table in the same schema as the source, you can still omit the schema name and make the function default to the schema of the (existing!) source table for convenience. Like:

CREATE OR REPLACE FUNCTION copy_table(_source_tbl regclass
                                    , _target_tbl text
                                    , _target_schema text = NULL)
  RETURNS bool AS
$func$
DECLARE
   query_str text;
BEGIN
   IF _target_schema IS NULL THEN               -- if no target schema provided ...
      SELECT c.relnamespace::regnamespace::text -- ... default to schema of input table
      FROM   pg_class c
      WHERE  c.oid = _source_tbl
      INTO  _target_schema;
   END IF;

   query_str = format('DROP TABLE IF EXISTS %1$I.%2$I;
                       CREATE TABLE %1$I.%2$I AS (TABLE %3$s);'
    , _target_schema
    , _target_tbl
    , _source_tbl);

   EXECUTE query_str;
   RAISE NOTICE '%', query_str;
   RETURN true;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT copy_table('ex.test', 'text1', 'ex');

Since the 3rd (last) parameter _target_schema has a default value, we can omit it, in which case the function defaults to the schema of the source table:

SELECT copy_table('ex.test', 'test1');

Even if we don't provide the source schema explicitly, relying on the current search_path:

SELECT copy_table('test', 'test1');

Related:

Table name as a PostgreSQL function parameter


select data from table which dont have row in another table I have 2 tables. First "product" (id). Second - "product_has_option_value" (product_id, option_value_id).

How can I get product ids which doesn't have some option_value_id (for example 10)? "product_has_option_value" has many to many relationships.

Ok. I think I found the solution:

select p.id
from product p
where p.id not in
  (select distinct(product_id)
   from product_has_option_value
   where option_value_id=543)

What you want is called "anti-join" (or "anti-semijoin"). There are 3 main ways to do this kind of query in MySQL (and some more in other DBMS, that have implemented EXCEPT operator):

NOT IN subquery (no need for DISTINCT here but you may need to add the check AND pov.product_id IS NOT NULL if that column is nullable):

select p.id
from product as p
where p.id not in
  ( select pov.product_id
    from product_has_option_value as pov
    where pov.option_value_id = 543
      and pov.product_id is not null
  ) ;

LEFT JOIN with IS NULL check:

select p.id
from product as p
    left join  product_has_option_value as pov
      on  pov.option_value_id = 543
      and pov.product_id = p.id
where pov.product_id is null ;

NOT EXISTS correlated subquery:

select p.id
from product as p
where not exists
  ( select 1
    from product_has_option_value as pov
    where pov.option_value_id = 543
      and pov.product_id = p.id
  ) ;


How to find "outer join in the where" syntax in SQL 2005 using cmptlevel 80? Well last Christmas I was very happy, because we ceased support for SQL Server 2000.

I could stop twisting my brain and use friendly analytical functions. (Believe me, when it comes to migrate stored procedures from SQL-Server to Oracle Analytical functions are one of your best friends).

Later we discovered that we overlooked a tiny annoyance.

When customers replaced there old machines by new ones, by default their compatibility level is set according to the old machine and problems are postponed until later.

Which problem? Analytical functions work in SQL Server 2005 despite compatibility level 80.

I'm afraid to tell that other new features like PIVOT is inhibited.

Now it is good time to try to get rid of cmptlevel 80, but how?

For me it is mostly scanning the procedures, views, functions and triggers for old style outer joins like '*=' and '=*'.

First let us find the defining text from the system tables. Initially it was saved in small chunks in syscomments. Hhm how to deal with the case when '*' and '=' are split onto to succeeding chunks.

Next don't waste your time with Select ROUTINES from INFORMATION_SCHEMA.ROUTINES it truncates the definition to 4000 characters.

Finally this is the code to query for the problematic objects:

select o.name , o.type, datalength(definition) len, len(definition) len, definition from sys.sql_modules m
join sysobjects o on o.id = m.object_id
where (definition like '%*=%' or definition like '%=*%')

But there is a further hurdle. Because default is derived from default the default width for text results is too small ( I think it is 256, but as it is the first thing I change to 8192 in SSMS, I wont bet).

Any case this helps to identify the rows which need deeper examination.

Perhaps sp_help displays the full definition for all possible cases.

Perhaps it does, I don't know for sure, because I got tired of that problems created by punchcard heads. Normally I use ADO and PowerShell, but they may not be installed on the customers machine.

Are there any 100% waterproof T-SQL scripts to do the job of displaying or exporting the full definition of procedures found by some condition?

A quick search of StackOverflow would show you use sys.sql_modules or OBJECT_DEFINITION to search for code on SQL Server 2005+. You can simply restore onto SQL Server 2005+ but not run to allow this.

That's if you want fool proof T-SQL. You could concatenate the syscomments rows together if you wish though in SQL Server 2000. I wouldn't personally.

Alternatively, use the SQL Server 2008 upgrade advisor to pick out most (maybe all) possible issues (such as ambiguous ORDER BY clauses which caught me out).

Note the Red Gate SQL Search sensibly only supports SQL Server 2005+


Best methods for ingesting XML into Oracle 11gR2 I have a large Oracle 11g Instance. I now have a requirement to convert large amounts of XML into records in several related tables.

The xml will arrive frequently at irregular intervals. The documents will vary in size (100megs avg)

The XML is well defined and static in structure.
The documents contains primary elements which should become the records.
Each element has two child elements for related entries.

So for a element that looks like this

 <element>
    <pet>
        <row0>1</row0>
        <row1>Dog</row1>
        <row2>Rex</row2>
        <row3>ownerRow3</row3>
    </pet>
    <owner>
        <row1>Bob</row1>
        <row2>banker</row2>
        <row3>1</row3>
    </owner>
</element>

would be come one "row" in two tables.

What is the most reasonable ( time/money vs "best" ) solution for this relatively simple (and common) task ?

I am not opposed to writing my own "digester" but I do not want to overlook simple solutions ( commercial or free)

My familiarity with available Oracle tools is low and Web searches are not providing a reasonable answer.

The fastest way to get things in is via SQLLoader, also for XML. The following will show you a method based on bfilename, which should be able to load XML files into the database in around a minute or 2 per file.

https://forums.oracle.com/forums/thread.jspa?threadID=2284390&tstart=30

You got some repeating elements in that file, so if you want to be able to query it efficient you should created specific, based on (x)path setting xmlindexes.

Some extra info can be found here

forums.oracle.com/forums/thread.jspa?threadID=2234618&tstart=120

If you need a short introduction into, or howto examples, search the needed info via blog.gralike.com

HTH

Marco


CREATE/ALTER INDEX permission How can I grant a user create/alter index permissions without giving db_ddladmin? I want them to have the minimum permissions possible.

The minimum permission to create/alter index can be easily found in corresponding BOL articles CREATE INDEX (Transact-SQL) and ALTER INDEX (Transact-SQL):

  Requires ALTER permission on the table or view.

This means you can grant alter table on every table of interest that already exists.

If you don't want to grant so granular permissions you can grant alter on schema(s) where your table(s) reside, this will assure that the user would be able to create/alter indexes on any future table/view.


Invalid column for SELECT with OVER I have the following query:

SELECT 1,
       row_number() over (partition by lower(X), lower(Y) order by X DESC) AS rn
FROM dbo.MyTable
WHERE rn = 1

When I run it, I get a error Invalid column name 'rn'..

I don't understand why I'm getting this as rn is defined in the SELECT portion.

I've tried replacing the WHERE with a HAVING too.

WHERE runs before the window function. So run the whole thing in a derived table.

SELECT t.*
FROM (
  SELECT 1,
    row_number() over (
      partition by lower(X), lower(Y) order by X DESC
    ) AS rn
  FROM dbo.MyTable
) AS t
WHERE rn = 1;


Using the hash of text instead of text itself to address a row in Postgres I have a project for a website where I have to address thousands or maybe tens of thousands of objects in a postgres table.

Initially I had the url to be:

example.com/object/{ID}/{TEXT SLUG}/

where {ID} is just an integer that corresponds to a serial primary key in my table, {TEXT SLUG} does nothing practically here except for SEO friendliness reasons.

Now, I want to hide my {ID}, since someone may just request all objects incrementing the {ID} using some easy script and reconstruct my database simply, I thought to design the url to be more modern like

example.com/object/{TEXT SLUG}/

Now, if I just address the table using {TEXT SLUG} directly, wouldn't it be much slower than simply address using {ID} as before even with using some index especially when it gets bigger (I expect something in the range of 40K to 60K rows at maximum but I only have 2K currently in the table)?

I thought to use some lightweight hashing algorithm to hash the {TEXT SLUG} and address the table using the hash with adding an additional column having a copy of the hash. Since Postgres has 4 or 8 byte integers,  I am tempted to use some lightweight 128-bit hash algorithm (e.g. Murmurhash), am I thinking right about using the idea of hashing the slug or is there something I am unaware of?

I would use an UUID instead of a serial. There is a module for this: https://www.postgresql.org/docs/current/static/uuid-ossp.html


database management system vs database vs tables vs file I have been trying to understand the way a database system works. Digging through the internet, I have come to following picture.

Database system is required because we need a way to store data, mainly on large scale, so that we can update it, query it, delete it, manipulate it plus perform other operations easily. The traditional file system is not designed for this and fails to serve the purpose.
We can implement the above database system in different ways, which leads to different data models, RDBMS being the most dominant one.
There is a database management system, which is a software and is in a way like file management system, and it manages the requests of creation, deletion, insertion of data etc. Eg. SQLite.
We interact with database using an interface, which is provided by database management system, and a language which is compatible with the database management system. Eg. SQL.

I hope my understanding till now is correct.
Now, the database management system interacts with database and I am not able to understand what database is exactly. I used to think the collection of all the tables is termed a database and is just a name. But then I found out there are several databases possible and a database can be created using DBMS just like files. Further research indicated me that database is a file.
My doubts are following:

If database is a file, how does it differ from ordinary files (in terms of implementation)?
I believe that tables are also files. But tables are stored in a database. How can a file be inside a file and both can be considered separate.
The implementation of different data models, like relations to store data in rdbms, objects to store data in OOP based database systems, are they implemented in database management system or database?

And if database is not a file what is it exactly and how tables, database and database management system are related in terms of implementation?
I have tried to find answers to these but it seems like the content on these are not much and I have had no luck. Also, please explain it avoiding very involved code snippets, as I am an analys

It is probably helpful to start thinking not about the low level details, but the high-level properties/behaviours of different kinds of storage.
For example, one might consider simple files (flat, binary content), indexed files (structured access layered on top of a single simple file), with or without locking in either case supplied by OS level primitives.
In such terms, one might say that different database systems (using the term very loosely) can be characterised and contrasted with files by things like:

A query language that is used to read (and to some extent, write) data according to some complex criteria. Simple files can only be read or written by file offset. Indexed files can have some criteria, but the query language is often very simple.

A database system often has multiple types of object stored in it, with metadata structures that allow those objects to be treated in a uniform manner by the query language. With simple files, and indexed files, you have to build all those aspects yourself.

A database system is often network-accessible, including with locking semantics. Simple files can be exposed by a network filesystem layer, but the locking semantics are often lost or changed when you do so. I'm not aware of any system (other than possibly some obsolete systems from the 1980s) that even attempt this for indexed files.

One might continue such an analysis for many other behaviours and properties. As you do that, the details of your questions and assumptions can be answered with regard to those behaviours and properties.
Of course, it is also true that many database systems are built on top of simple files provided by the operating system. But that is not a given - it is in fact common for high performance database systems to access the disks using some kind of "raw" I/O too. Files just happen to be a convenient abstraction to avoid the database system having to replicate a lot of things that the operating system's file access layers already do.


Bitwise operations in TSQL Take the following code:

Declare @a integer
set @a = 0
set @a = @a | cast (1 as BINARy)
set @a = @a | cast (100 as BINARy)
select cast (@a as integer)

Now, shouldn't the output of this code be 5 ?

I have an integer, set it to 0, so in binary its all 0's

then I OR it with 1 , so it becomes 0..001

then I OR it with 100 in binary, so it should become 00..0101 which, in decimal is 5

The output I actually get is 101

EDIT: for more clarity, I'm trying to accomplish with TSQL what the following code does in C++

#include<stdio.h>
#include<iostream>
using namespace std;
int main()
{
int a;
a = 0;
a = a | 1;
cout<< a;
a = a | 0b100;
cout<<endl<<a;
}

Your cast isn't doing what you think it should.

select cast(cast(100 as binary) as int) equals 100.  In other words, you are casting the decimal value 100 to binary, not taking '100' in base-2 notation.

Your expectation is actually this:

Declare @a integer
set @a = 0
set @a = @a | cast (1 as BINARY)
set @a = @a | cast (5 as BINARY)
select cast (@a as integer)

Incidentally, if this is new development, I very highly recommend against bit shifting in SQL Server.  It's slow, difficult to maintain, and violates first normal form.  If it's not, I'll guess that you probably know this already...

To convert binary to decimal, you can create a function.  There are other ways of going from integer to display base-2 notation, but this feels like it would be quite slow.


How do I script SQL Server 2012 replication to turn it off during a deployment and then turn it back on after the deployment? The scenario that I'm in is:

We have a database that is replicated but its objects (tables, etc.) are updated via Entity Framework. Whenever a schema change is made to the database by the SEs, we need to turn off replication, allow EF to make the changes to any replicated articles, and then turn replication back on. Currently this is done manually but we want to automate it for Octopus Deploy.

What is the process to script turning off replication and then turning it back on?

If you open the properties of the publication, then select the Subscription Options section, you will see a property named Replicate Schema Changes. Set that to true and any schema change will be replicated and display in the Distributor to Subscriber History in Replication Monitor as DDL change. No need to stop and restart any agents. I have found that you need to use alter and cannot use sp_rename to rename a column.

Of course, test in a non-production environment first.


Move a Filestream column between tables I have a table which has a Filestream column which I would like to move to another table.

Is this possible without having to do a "manual" move of the data (create target column; issue UPDATE/INSERT to target column from source column; DELETE source column)?

I'm hoping there might be a way to simply "detach" the column and move it to a different table.

I found a similar/related question about altering Filestream tables which makes me think that it isn't possible, but hoping that someone might be able to help!

You can't move the column without copying the data. There's one rather exotic idea to pull this of: You move all other columns and leave the filestream column in place.

Depending on the number of rows and schema changes required this can be a lot faster (or slower). You might have to completely gut some tables and create complicated scripts. I only would do this approach if you really need to avoid this downtime.


What happens when a MySQL Cluster node is disconnected I am trying to design a multi-master clustered MySQL database which will be spread through out the country.

The main reason for having a cluster is so that when the internet, in one location (or node), is disconnected, that location should still be able to read and modify the contents of the database locally. So that when the internet connection is restored, the rest of the cluster get updated.

I've went through the MySQL cluster documentation, however, I couldn't find this specific question. Is it possible to achieve this using MySQL cluster?

In short, MySQL Cluster isn't a good fit for your use case.

MySQL Cluster's shared-nothing architecture provides excellent protection against component-level failure.  When nodes fail or become unresponsive, Cluster takes quick action to disable affected nodes.  It does not focus on conflict resolution, instead works to avoid the "split-brain" problem where a single cluster begins processing work in parallel without coordination.  This is exactly the opposite the use case you describe:  Database activity continuing as normal when partitioned by the network, to be reconciled later when everything reconnects.

MySQL Cluster also typically does not fare well when nodes lack low-latency connections.  Relying on WAN connections between nodes is a bad idea.  MySQL Cluster does support asynchronous replication between data centers.

You might consider using multi-source replication in MySQL Server 5.7 as an alternative.


FILESTREAM_WORKITEM_QUEUE high waits I've been using sp_BlitzFirst and logging the results every 15 minutes for the past 5 days. Using the new Power BI report from FirstResponderKit.org, I'm seeing a VERY large range of FILESTREAM_WORKITEM_QUEUE waits. The range for this wait is anywhere from near 0, to 2942 minutes. Total for the past 5 days is 16,406 minutes. This includes the weekend, which the server only has light usage.

What causes this wait and should I be concerned that it sometimes get above 2000 minutes for a 15 minute period?

This is SQL Server 2012 Standard with a 10-core processor.

Well, almost universally, any wait that is a queue or timing wait (especially one with QUEUE in the name), can largely be ignored. While this one is not explicitly listed in Paul Randal's list of waits to ignore, it may be more that this wait is rare than because it shouldn't be ignored. Across all of the instances uploading SQL Sentry wait data to the cloud, this wait type has yet to be encountered (compare that to, say, LCK_M_SCH_M, a much more frequently observed wait type - seen on close to 1% of instances).

With the combination of:

there are no blog posts about this wait type (that I could find);
this is the first time it has been mentioned on this site or on Stack Overflow; and,
Microsoft hasn't even bothered to say anything about it beyond "TBD"...

...leads me to believe this is an innocuous wait that you shouldn't be concerned about.

And this is one of the problems I have with using a waits methodology for finding "problems" - is there any actual performance-related symptom involved with these waits? You're always going to have a wait that is the highest; that doesn't mean it's a problem. Now, if you can associate this wait type with observable performance symptoms in something related (such as reading from or writing to a filestream container), there might be more to investigate.

Also, are you sure you are accurately translating to minutes? There have only been 7,200 minutes in the past 5 days, so I'm not sure how this wait type could have registered more than double that. I already feel this wait type can't be anything more than a timer, which means it's simply accumulating benign wait time, and I can't imagine this also happens in parallel. So maybe the minutes you quote are actually seconds or some other unit.


highlight an instance where a volume reported is unchanged from the previous month I have a table as follows:

Fields:         Name   Month   Amount
Instance1:      Alan   Jan2018  120
Instance2:      Alan   Feb2018  140
Instance3:      Alan   Mar2018  140
Instance4:      John   Jan2018  50
Instance5:      John   Feb2018  50
Instance6:      John   Mar2018  60

In these set of values, I would like to highlight Instance 3 and Instance 5 because the amount remains unchanged from previous month for a same individual. Can someone please help?

Highlighting would most likely be done in a client tool like Reporting Services. From a TSQL point, you could add an additional column to your result to signify 'No change'.  If you're on at least SQL Server 2012, you can use LAG.

--Setup demo data
declare @t table (Fields varchar(30), Name varchar(30), [Month] varchar(10), Amount int)
insert into @t(Fields,name, [Month],Amount) values
('Instance1','Alan', 'Jan2018', 120),
('Instance2','Alan', 'Feb2018', 140),
('Instance3','Alan', 'Mar2018', 140),
('Instance4','John', 'Jan2018', 50),
('Instance5','John', 'Feb2018', 50),
('Instance6','John', 'Mar2018', 60)

--The actual query
--Using a Common Table Expression, create a SortOrder column
;
With _cte as
(
SELECT *
    ,SUBSTRING([month], 4, 4) + CASE
        WHEN substring([month], 1, 3) = 'Jan'
            THEN '01'
        WHEN substring([month], 1, 3) = 'Feb'
            THEN '02'
        WHEN substring([month], 1, 3) = 'Mar'
            THEN '03'
        WHEN substring([month], 1, 3) = 'Apr'
            THEN '04'
        WHEN substring([month], 1, 3) = 'May'
            THEN '05'
        WHEN substring([month], 1, 3) = 'Jun'
            THEN '06'
        WHEN substring([month], 1, 3) = 'Jul'
            THEN '07'
        WHEN substring([month], 1, 3) = 'Aug'
            THEN '08'
        WHEN substring([month], 1, 3) = 'Sep'
            THEN '09'
        WHEN substring([month], 1, 3) = 'Oct'
            THEN '10'
        WHEN substring([month], 1, 3) = 'Nov'
            THEN '11'
        WHEN substring([month], 1, 3) = 'Dec'
            THEN '12'
        END AS SortOrder
FROM @t)

SELECT *
    ,CASE
        WHEN LAG(amount) OVER (
                ORDER BY NAME
                    ,SortOrder
                ) = amount
            THEN '**'
        ELSE ' '
        END AS NoChangeFromPreviousMonth
FROM _cte
order by name,SortOrder

| Fields    | Name | Month   | Amount | SortOrder | NoChangeFromPreviousMonth |
|-----------|------|---------|--------|-----------|---------------------------|
| Instance1 | Alan | Jan2018 | 120    | 201801    |                           |
| Instance2 | Alan | Feb2018 | 140    | 201802    |                           |
| Instance3 | Alan | Mar2018 | 140    | 201803    | **                        |
| Instance4 | John | Jan2018 | 50     | 201801    |                           |
| Instance5 | John | Feb2018 | 50     | 201802    | **                        |
| Instance6 | John | Mar2018 | 60     | 201803    |                           |


Advanced threat detection vs IP restriction If we configured IP restriction to allow only internal network, then why we need threat detection?
IP restriction already blocks connections from other resources. Moreover, advanced threat detection is not free.

Even from your own internal network you can receive  anomalous database activities. Your database could face internal and external threats every day. Threat detection can detect SQL injection attacks and anomalous database access patterns coming from your own internal networks.


How safe are PostgreSQL triggers against attacks? in my PostgreSQL database I setup some triggers to prevent DELETE and EDIT operations (the triggers make sure that these operations lead to nothing). However, I'm wondering how safe these triggers are? Is it possible to circumvent these triggers and delete data by trying to illegally hack the data?

Security in ANSI relational databases is normally based on layers and one of those is role based access.  A business case that no one will ever need to delete or edit data in some tables will probably need to be revised when someone makes an oopsie and bad data is entered.

Any code that is written whether it's in a trigger or stored procedure has a chance of being misused but this requires access. A better solution than writing triggers that prevent basic operations on data is to control access and permissions by creating roles for each type of user and granting permissions appropriate to those roles.


On a single table, how is an indexed view different from a non clustered index? Assuming that no joins are involved, and the PK of the view (the UNIQUE Clustered Index) is the same as that of the base table, is there a difference between how a nonclustered index (NCL) and an indexed view is treated by SQL Server?

See this article: Resolving Indexes on Views for detail on how the query optimizer treats indexed views.

The normal use case for indexed views is aggregations (eg COUNT_BIG, SUM with GROUP BY etc) or complex formulas / calculations you want to keep in one place but do not want to use a computed column.  Basically the indexed view would look different to the base table, or to put it another way, you can do things with indexed views you can't do with a base table.

If you created an indexed view that is simply the base table and a unique clustered index (which is compulsory for an indexed view), then conceptually it is a duplicate of the base table and you may have missed the point of indexed views as described above.  You have also incurred a considerable index maintenance overhead for no reason.

Indexed View matching, the feature by which the Query Optimizer will use an indexed view even if it is not referenced in the query, (provided that view can 'answer the question') is harder than normal index selection so you also incur a risk of this not happening.  Although in your case where the view is a duplicate of the table it is irrelevant.  Indexed view matching is also an Enterprise edition feature, although you can override this with the NOEXPAND hint in Standard edition.

None of this is a concern for a conventional non-clustered index which may or may not be used depending on a large number of factors, although selection is probably more straightforward than for indexed views.

In summary, an indexed view implemented as you describe is simply an unnecessary duplicate of the base table.  I would be very careful about implementing them, unless there was a specific use case/example where it was shown to be significantly faster using an indexed view, and/or you can live with the overhead having trialled it extensively with your other workloads.

Indexed views do have their uses and there are some interesting discussions on them recently here, here and here.

HTH


Oracle DB : Export only table data from from all tables of given schemas Scenario :

I want to export all data(or rows) from all tables from given schemas let say s1, s2, s3 from oracle DB installed on unix server(U1) .
This data has to imported on another oracle DB installed on another unix server(U2) .
On U2 all the tables are created already, just need to import data from U1 db .
I tried exporting for few schema using sql developer . But its a kind of manual thing and have around 20 schemas .
And I don't want to export other objects like views, synonymn, procedure etc only data from each table .

Question:

So, how this can be achieved using expdp, impdp command line utility ? or is there any better method ?

Use Datapump (expdp/impdp):
expdp username/password directory=DATAPUMP_DIR schemas=S1,S2,S3 content=DATA_ONLY dumpfile=schema_dump.dmp logfile=export.log

impdp username/password directory=DATAPUMP_DIR dumpfile=schema_dump.dmp logfile=import.log

See here for an excellent reference: https://oracle-base.com/articles/10g/oracle-data-pump-10g
Note: If you export the metadata too (lose the "content=DATA_ONLY" option) then you can have the import process truncate your target tables before reloading them, by specifying the "TABLE_EXISTS_ACTION=TRUNCATE" option.


How to calculate streaks of consecutive dates for a user Assume a results table like so:

date|user_id

A user can have one or more result per day..

I need to calculate a) users who have entered a result in 30 or more consecutive days b) preferably the longest current streak for a specific user.

The results table is large (2 million results) with approx 50k users.

Alternatively, if this is too difficult, any suggestions of a different way of working this out?

(e.g. create a 'streak' table with user_id, streak number, start date etc and then update that on insert into results, but you'd still need to know if somebody entered a result whether that was part of a streak etc)

To keep track of longest streak and when it occurred.

Table columns needed:

current_streak          int
longest_streak          int
longest_streak_end_date datetime

Pseudocode:

if user has logged in today already
    return
if user logged in yesterday
    add 1 to current_streak
    if current_streak > longest_streak
        set longest_streak = current_streak
        set longest_streak_end_date = today
else
    set current_streak = 1


Database Mirroring - Mixed SQL Server 2012 to SQL Server 2016 I am pretty sure what the answer is but I want to confirm this.
Is there a way to create mixed DB mirrors SQL Server 2012 to SQL Server 2016.

The short answer is that yes, you can Mirror up to a higher version, but you should really only do this if you're going to upgrade.

Once you fail over, you can't fail back down to a lower version.


Is it possible to obtain number of prepared statements a connection holds? I have a heavy loaded application that holds connections persistently (it creates them on start and never releases).

During the lifetime it creates and (re)uses prepared statements, which are stored in an LRU structure (so the least used are eventually released).

And the question is: is it possible to obtain the number and other statistics of prepared statements for a given connection?

There is nothing relevant on http://www.postgresql.org/docs/9.3/static/monitoring-stats.html so not sure if there are other interfaces to access postgresql stats.

UPD: I probably was not clear enough: I need to obtain information using the normal DBA connection, since I cannot run arbitrary queries from the production software. For that very reason the pg_prepared_statements solution would not work.

You can view prepared statements with the pg_prepared_statements view. Query this view to get a count.


SQL Server 2008 R2 Setup Client Tools Connectivity During SQL Server 2008 R2 setup I see a checkbox for "Client Tools Connectivity" and "Client Tools Backward Compatibility" installation options. It says these are component for client server communication. Can anyone explains what are these components? What is being actually install on the server when you choose these components.

The client tools installs things like sqlcmd, odbc, db-lib, SMO (SQL Server Management Objects), AMO (Analysis Server Management Objects) etc; the backwards compatibility tools includes wrappers for COM objects such as DTS, STrace, osql etc... These tools can help you in various tasks depending on your need - and in some cases may be mandatory depending on what you are doing.

They can also be required when applications have been written that use them to connect to SQL Server - as such they are available as redistributables. You may not need all of them installed and so you should think about what you really need before installing them as extra components increase the surface area of attack upon a system.

For more information about these options please see the following link:

http://technet.microsoft.com/en-us/library/ms143786.aspx

I hope this helps you.


Storing medical ailments in a database I am trying to find the best method to store medical ailments , like Diabetes, Hypertension, Cancer etc in a
database.

My form will list over 200 ailments and the user will check those that affect him. I could serialize those
selected and then store it in a database but I want to be able to use the information at a later stage.
For example I want to know what ailments are selected with Diabetes and Hypertension.
Does this mean I will have to create a field for each ailment in my database ?

I am confused here.

I would appreciate any assistance.

Thanks
Kimmy

No need to serialize. A relational database (such as MySQL) is intended to store one fact per row. This is the proper way to do it.

Have an Ailments table. It will have 200 rows - one per ailment. This will be used to populate the user's check list.

On save, write each checked ailment to a UserAilment table. It will consist of a foreign key to the user table and a foreign key to Ailments.


Optimising plans with XML readers Executing the query from here to pull the deadlock events out of the default extended events session

SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
            '<victim-list>', '<deadlock><victim-list>'),
        '<process-list>', '</victim-list><process-list>')
    AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
    WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

takes about 20 minutes to complete on my machine. The stats reported are

Table 'Worktable'. Scan count 0, logical reads 68121, physical reads 0, read-ahead reads 0,
         lob logical reads 25674576, lob physical reads 0, lob read-ahead reads 4332386.

 SQL Server Execution Times:
   CPU time = 1241269 ms,  elapsed time = 1244082 ms.

Slow Plan XML

If I remove the WHERE clause it completes in less than a second returning 3,782 rows.

Similarly if I add OPTION (MAXDOP 1) to the original query that speeds things up too with the stats now showing massively fewer lob reads.

Table 'Worktable'. Scan count 0, logical reads 15, physical reads 0, read-ahead reads 0,
                lob logical reads 6767, lob physical reads 0, lob read-ahead reads 6076.

 SQL Server Execution Times:
   CPU time = 639 ms,  elapsed time = 693 ms.

Faster Plan XML

So my question is

  Can anyone explain what's going on? Why is the original plan so
  catastrophically worse and is there any reliable way of avoiding the
  problem?

Addition:

I've also found that changing the query to INNER HASH JOIN improves things to some extent (but it still takes > 3 mins) as the DMV results are so small I doubt that the Join type itself is responsible though and presume something else must have changed. Stats for that

Table 'Worktable'. Scan count 0, logical reads 30294, physical reads 0, read-ahead reads 0,
          lob logical reads 10741863, lob physical reads 0, lob read-ahead reads 4361042.

 SQL Server Execution Times:
   CPU time = 200914 ms,  elapsed time = 203614 ms.

(And plan)

After filling up the extended events ring buffer (DATALENGTH of the XML was 4,880,045 bytes and it contained 1,448 events.) and testing a cut down version of the original query with and without the MAXDOP hint.

SELECT COUNT(*)
FROM   (SELECT CAST (target_data AS XML) AS TargetData
        FROM   sys.dm_xe_session_targets st
               JOIN sys.dm_xe_sessions s
                 ON s.address = st.event_session_address
        WHERE  [name] = 'system_health') AS Data
       CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE  XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

SELECT*
FROM   sys.dm_db_task_space_usage
WHERE  session_id = @@SPID

Gave the following results

+-------------------------------------+------+----------+
|                                     | Fast |   Slow   |
+-------------------------------------+------+----------+
| internal_objects_alloc_page_count   |  616 |  1761272 |
| internal_objects_dealloc_page_count |  616 |  1761272 |
| elapsed time (ms)                   |  428 |   398481 |
| lob logical reads                   | 8390 | 12784196 |
+-------------------------------------+------+----------+

There is a clear difference in tempdb allocations with the faster one showing 616 pages were allocated and deallocated. This is the same amount of pages used when the XML is put into a variable too.

For the slow plan these page allocation counts are into the millions. Polling dm_db_task_space_usage whilst the query is running shows it seems to be constantly allocating and deallocating pages in tempdb with anywhere between 1,800 and 3,000 pages allocated at any one time.

The reason for the performance difference lies in how scalar expressions are handled in the execution engine. In this case, the expression of interest is:

[Expr1000] = CONVERT(xml,DM_XE_SESSION_TARGETS.[target_data],0)

This expression label is defined by a Compute Scalar operator (node 11 in the serial plan, node 13 in the parallel plan). Compute Scalar operators are different from other operators (SQL Server 2005 onward) in that the expression(s) they define are not necessarily evaluated at the position they appear in the visible execution plan; evaluation can be deferred until the result of the computation is required by a later operator.

In the present query, the target_data string is typically large, making the conversion from string to XML expensive. In slow plans, the string to XML conversion is performed every time a later operator that requires the result of Expr1000 is rebound.

Rebinding occurs on the inner side of a nested loops join when a correlated parameter (outer reference) changes. Expr1000 is an outer reference for most of the nested loops joins in this execution plan. The expression is referenced multiple times by several XML Readers, both Stream Aggregates, and by a start-up Filter. Depending on the size of the XML, the number of times the string is converted to XML can easily number in the millions.

The call stacks below show examples of the target_data string being converted to XML (ConvertStringToXMLForES - where ES is the Expression Service):

Start-up Filter

XML Reader (TVF Stream internally)

Stream Aggregate

Converting the string to XML each time any of these operators rebind explains the performance difference observed with the nested loops plans. This is regardless of whether parallelism is used or not. It just so happens that the optimizer chooses a hash join when the MAXDOP 1 hint is specified. If MAXDOP 1, LOOP JOIN is specified, performance is poor just as with the default parallel plan (where the optimizer chooses nested loops).

How much performance increases with a hash join depends on whether Expr1000 appears on the build or probe side of the operator. The following query locates the expression on the probe side:

SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
            '<victim-list>', '<deadlock><victim-list>'),
        '<process-list>', '</victim-list><process-list>')
    AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
    FROM sys.dm_xe_sessions s
    INNER HASH JOIN sys.dm_xe_session_targets st ON s.address = st.event_session_address
    WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

I have reversed the written order of the joins from the version shown in the question, because join hints (INNER HASH JOIN above) also force the order for the whole query, just as if FORCE ORDER had been specified. The reversal is necessary to ensure Expr1000 appears on the probe side. The interesting part of the execution plan is:

With the expression defined on the probe side, the value is cached:

Evaluation of Expr1000 is still deferred until the first operator needs the value (the start-up filter in the stack trace above) but the computed value is cached (CValHashCachedSwitch) and reused for later calls by the XML Readers and Stream Aggregates. The stack trace below shows an example of the cached value being reused by an XML Reader.

When the join order is forced such that the definition of Expr1000 occurs on the build side of the hash join, the situation is different:

SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
            '<victim-list>', '<deadlock><victim-list>'),
        '<process-list>', '</victim-list><process-list>')
    AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets st
    INNER HASH JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

A hash join reads its build input completely to construct a hash table before it starts probing for matches. As a result, we have to store all the values, not just the one per thread being worked on from the probe side of the plan. The hash join therefore uses a tempdb work table to store the XML data, and every access to the result of Expr1000 by later operators requires an expensive trip to tempdb:

The following shows more details of the slow access path:

If a merge join is forced the input rows are sorted (a blocking operation, just like the build input to a hash join) resulting in a similar arrangement where slow access via a tempdb sort-optimized worktable is required because of the size of the data.

Plans that manipulate large data items can be problematic for all sorts of reasons that are not apparent from the execution plan. Using a hash join (with the expression on the correct input) is not a good solution. It relies on undocumented internal behaviour with no guarantees it will work the same way next week, or on a slightly different query.

The message is that XML manipulation can be tricky things to optimize today. Writing the XML to a variable or temporary table before shredding is a much more solid workaround than anything shown above. One way to do this is:

DECLARE @data xml =
        CONVERT
        (
            xml,
            (
            SELECT TOP (1)
                dxst.target_data
            FROM sys.dm_xe_sessions AS dxs
            JOIN sys.dm_xe_session_targets AS dxst ON
                dxst.event_session_address = dxs.[address]
            WHERE
                dxs.name = N'system_health'
                AND dxst.target_name = N'ring_buffer'
            )
        )

SELECT XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')
FROM @data.nodes ('./RingBufferTarget/event[@name eq "xml_deadlock_report"]') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

Finally, I just want to add Martin's very nice graphic from the comments below:


Query slows with additional FROM item (even with no extra actions) I have a locally hosted table comment on Postgres 9.5 with ~ 2.6 million rows. When I run the following query, it executes in .867 ms:

SELECT COUNT(body)
FROM comment
WHERE body LIKE '%[deleted]%';

But the following query takes > 50 seconds (stopped measuring at this point):

SELECT COUNT(c1.body)
FROM comment c1, comment c2
WHERE c1.body LIKE '%[deleted]%';

I ask because I'm trying to implement a query like the following:

SELECT CAST(COUNT(c1.body)/COUNT(c2.body) as percent_deleted
FROM comment c1, comment c2
WHERE c1.body LIKE '%[deleted]%'
AND c1.id = c2.id;

As far as I understand if you want to know which is % of 'deleted' rows, do not cross join with the same table, get total rows and calculate the result on your initial query.

with totalc2 as
(
    select count(*) nrows
    from comment
)
SELECT CAST(COUNT(c1.body) AS float) /totalc2.nrows) as percent_deleted
FROM comment c1
WHERE c1.body LIKE '%[deleted]%'

You can use it as a subquery:

SELECT CAST(COUNT(c1.body) AS float) / (select count(*) from comment) as percent_deleted
FROM comment c1
WHERE c1.body LIKE '%[deleted]%'


List MATERIALIZED VIEWs in PostgreSQL that do not have a UNIQUE index Postgresql 9.4 allows to REFRESH materialized views CONCURRENTLY, if they have a UNIQUE index.

I am looking fo a SQL-query that lists all materialized views that DO NOT have such a UNIQUE or PRIMARY KEY index.

Tip: use psql's -E flag to get it to show you how what queries it is issuing for e.g. its \dm meta-command, and adjust from there.

Here's a rough and not at all cleaned-up query, but it should do the trick...

WITH matviews_with_unqiue_keys AS (
  SELECT c.oid, c.relname, c2.relname AS idx_name
  FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
  LEFT JOIN pg_catalog.pg_constraint con ON (
    conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u'))
  WHERE
    c.relkind = 'm' AND
    c.oid = i.indrelid AND i.indexrelid = c2.oid AND indisunique
)

SELECT c.relname
FROM pg_class c
WHERE c.relkind = 'm'
EXCEPT
SELECT mwk.relname
FROM matviews_with_unique_keys as mwk;


Tempdb change settings and recommendation In my production database I have below configuration in TempDb and needless to say I am facing a lot of IO issues on the drive .
So, as per recommendation I am planning to change it

Change #1 Adding 2 more .mdf files on different drive.

Change #2 Change initial size of tempdb .mdf size to 1024 and autogrowth to 10%.

Change #3 Change auto-growth of .ldf to 50%

I have three questions:

Do you have recommendation or correction needs to be made?
If I create new file and when I will change the intial size or autogrowth, do I need to restart SQL Server or can it impact SQL Server?
Do I have to create additional .ndf or log file also?

Reference of my other question

How to minimize log operations in SQL Server to avoid "log full" error

Dealing with non active transaction in log file and shrinking

IO request time and less write latency

As per paul recommendation, do I have to create 20 temdb files

As stated in Paul's recommendations and in KB 2154845 use 8 files, and adjust as needed.

  As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.

Now also make sure that the tempdb files are created such that you don't need auto growth and that they are all the same size with the same growth parameters not in percentiles.

Your tempdb is now ~140 GB so that is about the size you need to provision for. Create 8x15-20 GB datafiles and a single log file (as transaction logs are used sequentially) set all the files with exactly the same growth parameters, something that makes sense on your storage 512mb is fine if you have instant file allocation. As your transaction log is just about 5GB create a single 6-8GB file for the transaction log and set autogrowth to be in MB. Log growth is not affected by instant file allocations so make that parameter smaller than the one on the database files.

If you still see contention then go for 16x 8-10 GB files and dont change the transaction log parameters


SQL Server not using nonclustered index I have table which contains about 470mln of rows. I would like to select data based on date. I have two indices created on this table. One is clustered one and other is non clustered on date column (date is stored as INT). I have simple select statement:
select *
from big_table
where [date] BETWEEN 20200820 AND 20200828

The problem is that query plan uses clustered index scan instead of nonclustered seek + keylookup. Plan as follows:

Estimates generated in query plan are fine, statistics are up to date. This date range should deliver about 5mln of rows.
When I provide index hint then this select completes in couple of seconds - without hint it takes couple of minutes to finish.
This is SQL Server 2019 and generaly speaking I noticed that db prefers clustered index scans that using non clustered + keylookups even on bigger tables.
I would rather not using hint because:

sometimes I select wider ranges where clustered index scan should be desirable
table is used in view and I cannot supply index hint to the view

Is there any explanation why db is not using NC index it this case?
Links to query plans:

https://www.brentozar.com/pastetheplan/?id=SkaQFqLmD
https://www.brentozar.com/pastetheplan/?id=Hkh7q587P

It looks like SQL Server is not using that index by default because:

it's a filtered index, and
your query is parameterized

You can see this warning in the execution plan XML:
<UnmatchedIndexes>
  <Parameterization>
    <Object Database="Database1" Schema="Schema1" Table="Object1" Index="Index1" />
  </Parameterization>
</UnmatchedIndexes>
<Warnings UnmatchedIndexes="1" />

SQL Server doesn't know what the parameter values are (because they're in variables), so it can't safely use the filtered index.
One solution is to use index hints (as you mentioned, this isn't ideal).
Another way to work around that is to use dynamic SQL, as described by Jeremiah Peschka here:
Filtered Indexes and Dynamic SQL
I don't know how the filtered index is...filtered.  You might be able to get away with embedding the literal on only one of the two values, to limit plan cache bloat.


SQL Server - Handling localization of strings in nested non-deterministic view stacks While profiling a database I came across a view that is referencing some non-deterministic functions that get accessed 1000-2500 times per minute for each connection in this application's pool. A simple SELECT from the view yields the following execution plan:

That seems like a complex plan for a view that has less than a thousand rows that may see a row or two change every few months. But it gets worse with the following other observances:

Nested views are non-deterministic, so we cannot index them
Each view references multiple UDFs to build the strings
Each UDF contains nested UDFs to get the ISO codes for localized languages
Views in the stack are using additional string builders returned from UDFs as JOIN predicates
Each view stack is treated as a table, meaning that there are INSERT / UPDATE / DELETE triggers on each to write to the underlying tables
These triggers on the views use CURSORS that EXEC stored procedures which reference more of these string building UDFs.

This seems pretty rotten to me, but I only have a few years experience with TSQL. It gets better, too!

It appears the developer who decided that this was a great idea, did all this so that the few hundred strings that are stored can have a translation based on a string returned from a UDF that is schema-specific.

Here's one of the views in the stack, but they are all equally bad:

CREATE VIEW [UserWKStringI18N]
AS
SELECT b.WKType, b.WKIndex
    , CASE
       WHEN ISNULL(il.I18NID, N'') = N''
       THEN id.I18NString
       ELSE il.I18nString
       END AS WKString
    ,CASE
       WHEN ISNULL(il.I18NID, N'') = N''
       THEN id.IETFLangCode
       ELSE il.IETFLangCode
       END AS IETFLangCode
    ,dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS') AS I18NID
    ,dbo.UserI18N_Session_Locale_Key()  AS IETFSessionLangCode
    ,dbo.UserI18N_Database_Locale_Key() AS IETFDatabaseLangCode
FROM   UserWKStringBASE b
LEFT OUTER JOIN User3StringI18N il
ON    (
il.I18NID       = dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS')
AND il.IETFLangCode = dbo.UserI18N_Session_Locale_Key()
)
LEFT OUTER JOIN User3StringI18N id
ON    (
id.I18NID       = dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex,N'WKS')
AND id.IETFLangCode = dbo.UserI18N_Database_Locale_Key()
)
GO

Here is why UDFs are being used as JOIN predicates. The I18NID column is formed by concatenating: STRING + [ + ID + | + ID + ]

During testing of these, a simple SELECT from the view returns ~309 rows, and takes 900-1400ms to execute. If I dump the strings into another table and slap an index on it, the same select returns in 20-75ms.

So, long story short (and I hope you appreciated some of this sillyness) I want to be a good Samaritan and re-design and re-write this for the 99% of clients running this product who do not use any localization at all--end users are expected to use the [en-US] locale even when English is a 2nd/3rd language.

Since this is an unofficial hack, I am thinking of the following:

Create a new String table populated with a cleanly joined set of data from the original base tables
Index the table.
Create a replacement set of top-level views in the stack that include NVARCHAR and INT columns for the WKType and WKIndex columns.
Modify a handful of UDFs that reference these views to avoid type conversions in some join predicates (our largest audit table is 500-2,000M rows and stores an INT in a NVARCHAR(4000) column which is used to join against the WKIndex column (INT).)
Schemabind the views
Add a few indexes to the views
Rebuild the triggers on the views using set logic instead of cursors

Now, my actual questions:

Is there a best practice method to handle localized strings via a view?
Which alternatives exist for using a UDF as a stub? (I can write a specific VIEW for each schema owner and hard-code the language instead of relying on a variety of UDF stubs.)
Can these views be simply made deterministic by fully qualifying the nested UDFs and then schemabinding the view stacks?

Looking at the given code, we can say,

First, this should not be a view but it should be a stored procedure, as it is not just reading from a table, but it uses UDFs.
Second, the UDF should not be called frequently for the same column. Here, it's called once in the select

,dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS') AS I18NID

and Second time for joining

.IETFLangCode = dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS')

One can generate values in a temporary table or use a CTE (Common Table Expression) to get those values in the first place before the join takes place.

I have generated a sample USP that will provide some improvements:

CREATE PROCEDURE usp_UserWKStringI18N
AS
BEGIN
    -- Do operation using UDF
    SELECT b.WKType
        ,b.WKIndex
        ,dbo.User3StringI18N_KeyValue(b.WKType, b.WKIndex, N'WKS') AS I18NID
        ,dbo.UserI18N_Session_Locale_Key() AS IETFSessionLangCode
        ,dbo.UserI18N_Database_Locale_Key() AS IETFDatabaseLangCode
    INTO #tempTable
    FROM UserWKStringBASE b;

    -- Now final Select
    SELECT b.WKType
        ,b.WKIndex
        ,CASE
            WHEN ISNULL(il.I18NID, N'') = N''
                THEN id.I18NString
            ELSE il.I18nString
            END AS WKString
        ,CASE
            WHEN ISNULL(il.I18NID, N'') = N''
                THEN id.IETFLangCode
            ELSE il.IETFLangCode
            END AS IETFLangCode
        ,b.I18NID
        ,b.IETFSessionLangCode
        ,b.IETFDatabaseLangCode
    FROM #tempTable b
    LEFT OUTER JOIN User3StringI18N il
        ON il.I18NID = b.I18NID
            AND il.IETFLangCode = b.IETFSessionLangCode
    LEFT OUTER JOIN User3StringI18N id
        ON id.I18NID = b.I18NID
            AND id.IETFLangCode = b.IETFDatabaseLangCode
END

Please try this


Predicates: in the WHERE clause or the JOIN clause? Looking at "SQL Server 2008 Internals"1 by Kalen Delaney, on page 13, it states the following:

  "The first step in producing such a plan is to normalize each query, which potentially breaks down a single query into multiple, fine-grained queries.  After the Query Optimizer normalizes a query, it optimizes it, which means that it determines a plan for executing that query."

It's been suggested to me by another DBA that performance can be improved for certain queries by moving WHERE clause predicates into the FROM clause, such that:

SELECT *
FROM dbo.table1 t1
    INNER JOIN dbo.table3 t3 ON t1.ID = t3.ID
    LEFT OUTER JOIN dbo.table2 t2 ON t1.ID = t2.ID
WHERE t1.CreateDate >= '2015-07-31 00:00:00';

would become:

SELECT *
FROM dbo.table1 t1
    INNER JOIN dbo.table3 t3 ON t1.ID = t3.ID
        AND t1.CreateDate >= '2015-07-31 00:00:00'
    LEFT OUTER JOIN dbo.table2 t2 ON t1.ID = t2.ID;

Apparently the implication for the first example is the query optimizer will perform the JOIN first, then apply the WHERE clause, which for complicated queries with large tables will not perform as well as if we manually re-write the query into the form presented in the second example.  Apparently this only applies to outer joins, i.e. if this were an inner join, the normalizer would in fact move the WHERE predicate into the FROM clause.

Can someone confirm this behavior?  Is there some documentation somewhere I can look at?  Can I see this in the execution plan?

1 - yes, I know this is ancient.

I guess this is one of the "It depends"-moments.
In a regular execution there is no or at least nearly no difference.

Thought it will become more interesting once the queries are more complex.
More complex scenarios are e.g.:

Nested Joins
FORCE ORDER  (Query Hint)

I would have added LEFT and CROSS Joins as well, thought they are already part of your example.

About the "experience in SQL Server normalization" - I'd say, besides looking in the code, there will not be much you can hold your self on. It is often just a "It depends!"


Should PgBouncer be installed on web application server or database server? I have a django website where the application and database server are different. I need to install pgbouncer to enable database pooling, however, I'm confused about where to install it: web application server or database server?

Note that in this set up, there is exactly 1 application server and 1 database server.

I would recommend first installing it on the database server.  That way you can run top (or whatever) on that machine and have a one-stop-shop to see what is going on.  Also, the dependencies are more likely to already be installed on the database server than they are on the application server.

If you then notice the database server is more heavily loaded than the app server, you can move it to the app server.  But looking at the current loads (when you don't have connection pooling in place) to make that decision isn't going to be terribly useful, because they could change dramatically once you start pooling.


What is the optimal setting for innodb_buffer_pool_instances for a mid level AWS RDS server with 6gb buffer pool? We have a MySQL 5.7 AWS RDS instance running on a db.m4.large instance.  The basic resources for that instance type are:

7.5gb RAM
2 VCPU

RDS defaults to a calculation that allocates 75% of RAM to innodb_buffer_pool_size.

{DBInstanceClassMemory*3/4}

This ends up being a 6gb allocation:

mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                           6.000000000000 |
+------------------------------------------+

With MySQL 5.7 if you do not set the number of buffer pool instances, then MySQL now defaults to 8 pools.  However the MySQL 5.7 manual states this when discussing buffer pool instances:

  The total size you specify is divided among all the buffer pools. For
  best efficiency, specify a combination of innodb_buffer_pool_instances
  and innodb_buffer_pool_size so that each buffer pool instance is at
  least 1GB.

For this reason, I plan on reducing the innodb_buffer_pool_instances to a number <= 6.

Here are my questions:

Is there some correlation to be made between the number of CPU's and
the buffer_pool_instances setting?
Would it be better in this scenario to go with 4,5, or 6
buffer_pool_instances, and if so, what is the reasoning behind that
recommendation?  My simplistic response would be to change the setting to 6 buffer pool instances to satisfy the "at least 1GB" recommendation, but I would be interested to hear from anyone who has looked into this further, or done benchmarking of this setting whether that be under RDS or not.

Short Answer:  Not a problem.

Long Answer:

The "instances" feature is new (as of 5.5.4) and exists to speed up certain things when the system is running at peak load.  But even then, it does not help the performance more than a few percent.  For lightly loaded servers, the improvement is probably not measurable.

What about "8"?  I have yet to hear of anyone getting in trouble when each instance is less than 1GB.  I suspect it is "advice", not "a hard and fast rule" to make each instance at least 1GB.  In fact, glancing at a few dozen servers, I see that almost half of them violate that.  I see one machine with 10 instances for only 2GB of buffer_pool.

I would trust RDS to provide reasonable settings.  If their settings are bad, they will be bad for all their clients.  Imagine how much hassling RDS would get!


MySQL Workbench is adding --enable-cleartext-plugin option on mysqldump command For some reason my copy of MySQL Workbench on one of my servers has started adding this --enable-cleartext-plugin option on every mysqldump command, which results in this error every time.

  20:13:02 Dumping timesavr_dev (certifications) Running: mysqldump.exe
  --defaults-file="c:\users\admini~1\appdata\local\temp\3\tmpn1an6u.cnf"  --set-gtid-purged=OFF --enable-cleartext-plugin --user=root --host=localhost --protocol=tcp --port=3306 --default-character-set=utf8 --skip-triggers "timesavr_dev" mysqldump: unknown option '--enable-cleartext-plugin'

  Operation failed with exitcode 2 20:13:02 Export of
  C:\inetpub\wwwroot\TimeSavr\DB_Stuff\MySQLBackups\Dump20180608.sql has
  finished with 1 errors

I just noticed this today but I suspect this started happening a few days ago when some other weird things also happened on this server.  After a reboot all the bindings on my website had disappeared, as had the user security privileges on the website folder, and for some reason Shared Config was enabled in my redirection.config file.  It all seems very random, and now this.

These are the options I have enabled, and I'm pretty sure these are default because when I click Restore Defaults nothing changes.

How can I tell MySQL Workbench to stop using the --enable-cleartext-plugin option?

"Enable Cleartext Authentication Plugin" is a connection setting, not a setting of "Data Export".

You can find it under "Database", "Manage Connections", here select your connection; inside the "Connection"-tab is the "Advanced"-tab with a checkbox to (un-)select that option.


Google Cloud SQL Replication Delay We have a project which does write data on a second generation MySQL 5.6

The master's write behaviour:
Average 70 write operations per second.
The thing is 2 times a day, application writes data along 3 hours each time. And the each of them writes 2500 operations per second.

I believe this write operations cause the never ending replication delay.
Once the replication delay starts, not able to recover. Increasing the replica's source(cpu and memory) is not working.

What should i do to make replicas sync? I presume master/slave architecture is not the solution for this write operations.

Should i use bigger master for both read and write operations instead of master-slave?

Thank you.

Current master:
4cpu 15gb ram and 900gb ssd

Current replica:
8cpu 30gb ram and 900gb ssd

EDIT

It's happening like 50k inserts in a single transaction.
Replication is row based.
Inserts can be batched.
All operations going into a single database though there are other databases exist in the master.
Upgrading to 5.7 is likely possible.

I'm trying to create an external read replica because setting global variables is not possible due to super privilege. As you describe, i'm going to increase parallel workers for multi threaded replication and innodb_flush_log_at_trx_commit.

Also i'll try max allowed packets.
The delayed replica is destroyed. So the status and variables below are  represent the master.

SHOW GLOBAL STATUS

SHOW GLOBAL VARIABLES

I have seen this happens a lot in Amazon RDS. This occurs with row-based replication when a Master get hammered with lots of writes and they get serialized to one I/O thread.

What you should do is dynamically change the following:

SET GLOBAL innodb_flush_log_at_trx_commit = 2;

Problem is you need the SUPER privilege, which neither Amazon RDS nor Google CloudSQL allow. For Amazon RDS, you change the value in the DB Parameter Group (the list of server options for the MySQL Instance). If you can change the dynamic option through some option group in Google CloudSQL platform, this is the option to change. When replication catches back up, change back to 1.

The answer posted by Rick James would be great if you had multiple databases in the CloudSQL instance. He said

  All operations going into a single database? Any chance of upgrading past 5.6; multi-threaded replication may help.

Try change the dynamic option for now to get rid of the current replication lag, and try his suggestion of going with MySQL 5.7 and setup multithreaded replication as the long-term solution.


Master-Slave Database. How to activate a secondary database whenever the primary database is down? I need some idea on how to accomplish a master-slave database in MySQL. It's like, there's a primary server which the system connects then when it is down, it will use its local server. Here's the scenario:

I'm doing my thesis as a requirement for my course, I'm currently developing a web-based POS which will be installed for two branches, the system will be connected to the main office as the server is located there. What my client wants is to have a secondary database which will be used whenever the internet connection is down, that secondary database is installed in the computer in the branch. Well ofcourse, the primary database is in the main office, and it needs at internet connection for the POS and the primary database to work together.

Then when the internet connection works again, the primary database will be updated from the latest records in secondary database.

In addition to setting up replication, which will copy the data from your master to your slave, you need a failover technology to make the system use the slave when the master is down.

You can do this manually, but you can also use something like MMM (http://mysql-mmm.org/). It is open-source, so you can look at the code to see the logic of what it is doing. Basically, it is moving aliases (like yourdb-master and yourdb-slave) between two physical servers, so the system always connects to a yourdb-master, wherever that is physically.


Design - Q&A Database - Help I'm developing an application for my last year/degree at school.

This application is for a mechanical workshop;
It will have two types of users:

Administrator,
User;

Administrator will be just to add new users (with this I mean, just the owner of the workshop can add users to this application, via graphic interface);
User will be able to add Questions and Answers (that will be the same thing, because he will have a question, and then will put its resolution, it's like my own tutorial) about what he fix at the mechanical workshop;
User will sometimes need to search for one that he has already input as question/answer/solvedproblem/whatever.
-Questions or Answers procediments are step-by-step, so I guess that we'll need to have a table for components, I'm not sure.
-The application will ask you for noises, smells, whatever that the man that works at the place detect on a car or motorcycle being repaired. We cannot forget this is a step-by-step Q&A and that I will need to put what I'm trying to solve, how I solved and then one month or two later I'll find/searching what I've already did.

I'm very newbie with databases, how can I design this? I'm very good at using database info, but to make one I would really need someone's help. Let me know if you need more details.

you will need something like this.

CREATE DATABASE QA
GO
USE QA
GO
CREATE TABLE Users
(
UserID int identity(-2147483648,1),
Name varchar(150),
fname varchar(150),
isadmin bit,
CONSTRAINT PK_Users PRIMARY KEY CLUSTERED (UserID),
)

GO
CREATE TABLE Question
(
QuestionID int identity(-2147483648,1),
UserID int,
Question varchar(max),
CONSTRAINT PK_Question PRIMARY KEY CLUSTERED (QuestionID),
CONSTRAINT FK_UserQuestion FOREIGN KEY (UserID)
    REFERENCES dbo.Users (UserID)
)
GO
CREATE TABLE Answer
(
AnswerID int identity(-2147483648,1),
QuestionID int,
UserID int,
Answer varchar(max),
IsSolver bit,
CONSTRAINT PK_Answer PRIMARY KEY CLUSTERED (AnswerID),
CONSTRAINT FK_UserAnswer FOREIGN KEY (UserID)
    REFERENCES dbo.Users (UserID) ,
CONSTRAINT FK_AnswerQuestion FOREIGN KEY (UserID)
    REFERENCES dbo.Question (QuestionID)
)
GO

This will generate this diagram.

You will need to add extra fields for other items you need, but the diagram should be what you need to accomplish your task.


List of role permissions including other roles - how can I find out the roles that belong to other roles? I can see the permissions of a role in a database using the scripts in
List all permissions for a given role?

However, It does not show me when the role belongs to a another role.
For instance, on the picture below you can see that db_integrationEngine has the permissions of other roles too.
It is db_datareader and db_datawriter.

However when I run the script below, I can't see this.

here is the script I am using:

SELECT DISTINCT rp.name,
                    ObjectType = rp.type_desc,
                    PermissionType = pm.class_desc,
                    pm.permission_name,
                    pm.state_desc,
                    ObjectType = CASE
                                   WHEN obj.type_desc IS NULL
                                         OR obj.type_desc = 'SYSTEM_TABLE' THEN
                                   pm.class_desc
                                   ELSE obj.type_desc
                                 END,
                    [ObjectName] = Isnull(ss.name, Object_name(pm.major_id))
    FROM   sys.database_principals rp
           INNER JOIN sys.database_permissions pm
                   ON pm.grantee_principal_id = rp.principal_id
           LEFT JOIN sys.schemas ss
                  ON pm.major_id = ss.schema_id
           LEFT JOIN sys.objects obj
                  ON pm.[major_id] = obj.[object_id]
    WHERE  rp.type_desc = 'DATABASE_ROLE'
           AND pm.class_desc <> 'DATABASE'
           AND rp.name <> 'PUBLIC'
    ORDER  BY rp.name,
              rp.type_desc,
              pm.class_desc

The script above, gives me the result on the picture below:

Scripts to add and remove one role to another
sp_addrolemember [db_datareader], [db_IntegrationEngine]
go
sp_addrolemember [db_datawriter], [db_IntegrationEngine]
go

sp_droprolemember [db_datareader], [db_IntegrationEngine]
go
sp_droprolemember [db_datawriter], [db_IntegrationEngine]
go

Ideally I am thinking about a recursive CTE that would go through each role and gets its permissions and when find another role inside it, do the same.
Just so far my attempts to generate such scripts were not successful, any help?
It does not need to be a recursive CTE as long as I can see the roles that are contained inside another roles.

;WITH theRoles (member_principal_id, role_principal_id)
AS
(
  SELECT
   r.member_principal_id,
   r.role_principal_id
  FROM sys.database_role_members r
   UNION ALL
  SELECT
   tr.member_principal_id,
   rm.role_principal_id
  FROM sys.database_role_members rm
            INNER JOIN theRoles tr
                    ON rm.member_principal_id = tr.role_principal_id
)

select  the_role=A.name
       ,A.is_fixed_role
       ,role_member=B.name
       ,B.type_desc
from theRoles tr
        INNER JOIN sys.database_principals A
                ON tr.role_principal_id = A.principal_id
        INNER JOIN sys.database_principals B
                ON tr.member_principal_id = B.principal_id
GROUP BY A.name
       ,A.is_fixed_role
       ,B.name
       ,B.type_desc
order by a.is_fixed_role DESC
        ,a.name

and for testing the script above:

USE [DBA]
GO
CREATE ROLE [role_1]
GO
CREATE ROLE [role_2]
GO
CREATE ROLE [role_3]
GO
CREATE ROLE [role_10]
GO
CREATE ROLE [role_101]
GO
CREATE ROLE [role_1010]
GO

sp_addrolemember @rolename='db_datareader',@membername='role_1'
go
sp_addrolemember @rolename='db_datareader',@membername='role_2'
go
sp_addrolemember @rolename='db_datareader',@membername='role_3'
go
sp_addrolemember @rolename='role_1',@membername='role_10'
go
sp_addrolemember @rolename='role_10',@membername='role_101'
go
sp_addrolemember @rolename='role_10',@membername='role_1010'
go
sp_addrolemember @rolename='role_101',@membername='role_1010'
go


MySQL - Create a function from another function I want to create a function same like my old functions.

When I create a table, I can use `AS  to other table as follows:

CREATE TABLE dummy2 AS SELECT * FROM dummy1;

Can I do the same thing with functions?

You could play some games with mysql.proc, the physical home of all Stored Procedures and Stored Functions.
First, here are the databases on my PC
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| david              |
| dufran             |
| jimelliott         |
| junk               |
| karto              |
| mysql              |
| performance_schema |
| preeti             |
| rolando            |
| sandro             |
| telwit             |
| telwit_french      |
| test               |
| user1162541        |
| yellowseason       |
+--------------------+
16 rows in set (0.00 sec)

mysql>

Let's create a Stored Function in the database rolando called GetUnixTimeStamp:
mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS `GetUnixTimeStamp` $$
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE DEFINER=`root`@`localhost` FUNCTION `GetUnixTimeStamp`(x INT) RETURNS int(11)
    -> DETERMINISTIC
    -> BEGIN
    ->     RETURN UNIX_TIMESTAMP();
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SELECT rolando.GetUnixTimeStamp(9);
+-----------------------------+
| rolando.GetUnixTimeStamp(9) |
+-----------------------------+
|                  1352263567 |
+-----------------------------+
1 row in set (0.00 sec)

mysql>

Let's try to sculpt the CREATE FUNCTION SQL Statement based on the function itself
SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
'` ',type,' `',db,'`.`',name,'`(',param_list,') ',
IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
INTO @ProcedureCreationSQL from mysql.proc
WHERE db='rolando' AND name='GetUnixTimeSTamp';
SELECT @ProcedureCreationSQL\G

What does this generate ?
mysql> SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
    -> '` ',type,' `',db,'`.`',name,'`(',param_list,') ',
    -> IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
    -> IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
    -> INTO @ProcedureCreationSQL from mysql.proc
    -> WHERE db='rolando' AND name='GetUnixTimeSTamp';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @ProcedureCreationSQL\G
*************************** 1. row ***************************
@ProcedureCreationSQL: CREATE DEFINER=`root`@`localhost` FUNCTION `rolando`.`GetUnixTimeStamp`(x INT) RETURNS int(11) DETERMINISTIC BEGIN
    RETURN UNIX_TIMESTAMP();
END
1 row in set (0.00 sec)

mysql>

OK so far, so good. Let's change the SQL to place this function in the database david
mysql> SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
    -> '` ',type,' `david`.`',name,'`(',param_list,') ',
    -> IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
    -> IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
    -> INTO @ProcedureCreationSQL from mysql.proc
    -> WHERE db='rolando' AND name='GetUnixTimeSTamp';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @ProcedureCreationSQL\G
*************************** 1. row ***************************
@ProcedureCreationSQL: CREATE DEFINER=`root`@`localhost` FUNCTION `david`.`GetUnixTimeStamp`(x INT) RETURNS int(11) DETERMINISTIC BEGIN
    RETURN UNIX_TIMESTAMP();
END
1 row in set (0.00 sec)

mysql>

OK Great. We can make the SQL. Can I execute the creation of the new function ???
echo DELIMITER $$ > newproc.txt
mysql -uroot --delimiter="$$" -ANe"SELECT REPLACE(CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),'` ',type,' `david`.`',name,'`(',param_list,') ',IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',IF(is_deterministic='YES','DETERMINISTIC',''),' ',body),'\n',' ') from mysql.proc WHERE db='rolando' AND name='GetUnixTimeSTamp';" >> newproc.txt
echo $$ >> newproc.txt
mysql -uroot < newproc.txt

Does the function exist ???
mysql> show create function david.getunixtimestamp\G
*************************** 1. row ***************************
            Function: getunixtimestamp
            sql_mode:
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `getunixtimestamp`(x INT) RETURNS int(11)
    DETERMINISTIC
BEGIN     RETURN UNIX_TIMESTAMP(); END
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql>

Biggest questiion of all : Does the newly copied function run ???
mysql> select david.getunixtimestamp(8);
+---------------------------+
| david.getunixtimestamp(8) |
+---------------------------+
|                1352265404 |
+---------------------------+
1 row in set (0.00 sec)

mysql>

HOORAY !!! This is how you can copy a function from one database to another.
FINAL QUESTION : Is this all worth it ???
SUMMARY
Here is the original SQL to generate function creation
SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
'` ',type,' `david`.`',name,'`(',param_list,') ',
IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
INTO @ProcedureCreationSQL from mysql.proc
WHERE db='rolando' AND name='GetUnixTimeSTamp';
SELECT @ProcedureCreationSQL\G

This will create Stored Procedures as well
Here is the copy operation, which must take place from the OS level calling the mysql client
echo DELIMITER $$ > newproc.txt
mysql -uroot --delimiter="$$" -ANe"SELECT REPLACE(CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),'` ',type,' `david`.`',name,'`(',param_list,') ',IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',IF(is_deterministic='YES','DETERMINISTIC',''),' ',body),'\n',' ') from mysql.proc WHERE db='rolando' AND name='GetUnixTimeSTamp';" >> newproc.txt
echo $$ >> newproc.txt
mysql -uroot < newproc.txt