Converting varbinary data to varchar in SQLServer leads to unexpected results I need to fix some data conversion issue in SQL server 2008. I got some requirement change on storing the data. Without much care I converted existing varbinary data to varchar using CONVERT(NVARCHAR(max), @bytearraydata, 1).

The same conversion in C# is done using Encoding.Default.GetString and Encoding.Default.GetBytes methods. Encoding.Default.GetBytes(string) gets back the bytearray as it was earlier. But when I try to get back the byte array of string which I converted using CONVERT()  gives me a wrong result.

My work is to fetch byte array stored as string the database and convert it to byte array and finally render the content as PDF. Data going through encoding mechanism (while saving and while fetching) works fine for me. But when I try to fetch the data which was converted using CONVERT it is failing to generate PDF.

How can I resolve this problem?

Summary:

Byte array column has been changed to string.

Existing data conversion done using this function:

Convert(NVARCHAR(MAX), @bytearraydata, 1)

In the application byte array conversion is done using Encoding.Default.GetString(bytearraydata)

Are Encoding and CONVERT not compatible?

There are a few of confusions going on in the question, leading to the unexpected results:

The terms VARCHAR and NVARCHAR are being used interchangeably (or so it seems), yet they are quite different. NVARCHAR is a 16-bit encoding – UTF-16 LE (Little Endian) to be exact – and this does not change. VARCHAR is an 8-bit encoding, and the specific 8-bit encoding being used is determined by the Code Page associated with the Collation of the column (we will ignore VARCHAR data in string literals and variables for now since this question is about data stored in a table). If you want to know what Code Page is associated with a particular Collation, you can use the COLLATIONPROPERTY built-in functions:

SELECT COLLATIONPROPERTY(N'Latin1_General_100_CI_AS_SC', 'CodePage') AS [CodePage];
-- 1252

When converting between VARBINARY and either VARCHAR or NVARCHAR you need to be careful to be consistent with that string datatype. You can't convert from VARCHAR to VARBINARY and then take that same VARBINARY and convert it to NVARCHAR.
The Encoding class in .NET represents a particular encoding of text, whether it be 7-bit, 8-bit, 16-bit, 32-bit, or variable (such as UTF-8). In order to get "expected" results, you need to create an encoding that matches what you need to convert to or from, with respect to the byte[] representation. Strings in .NET are always UTF-16 LE (same as NVARCHAR ), and this is what the Unicode encoding in .NET refers to. The byte[] representation of an encoding will be of whatever encoding was created, but the string representation will always be UTF-16 LE. So, which encoding to create depends on what type of data you are dealing with:

NVARCHAR: Use Encoding.Unicode
VARCHAR: Determine the Code Page of the Collation via COLLATIONPROPERTY(N'collation_name', 'CodePage') and then use that int value in Encoding.GetEncoding(CodePageIntValue).

When using the CONVERT built-in function, be careful which "style" number you are using. For example:

SELECT CONVERT(VARBINARY(50), N'bob');
-- 0x62006F006200

Now take that returned VARBINARY value and convert it back into NVARCHAR, using "style" values of 0 (default) and 1 (which is what your CONVERT function is using in the question):

SELECT CONVERT(NVARCHAR(MAX), 0x62006F006200, 0) AS [Style_0],
       CONVERT(NVARCHAR(MAX), 0x62006F006200, 1) AS [Style_1];

Returns:

Style_0        Style_1
bob            0x62006F006200

So, if the following statement from the question is true:

  In the application byte array conversion is done using Encoding.Default.GetString(bytearraydata)

then that would equate to using VARCHAR instead of NVARCHAR, and a "style" value of 0 (or nothing) instead of 1:

CONVERT(VARCHAR(MAX), 0x62006F006200)

Try using cast instead, that worked for me

C#: Encoding.Default.GetString

SQL: select cast(COLUMN_NAME as varchar(max)), * from TABLE


Why can't I drop this Oracle datafile - even though it seems empty? This system is Oracle 11.2.0.3.0, using ASM.  We had an issue where there seemed to be a corruption issue of some kind - every time a row tried to get inserted into the table, we'd get the following:

ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 23: '<filepath>'
ORA-15081: failed to submit an I/O operation to a disk

Luckily, this is a test system and there happened to be only one table on data file 23, according to dba_extents.  I had the team drop the table and recreate it in another tablespace for the moment.

However, now I'd like to drop the datafile that was reporting the issue.
select count(*) from Dba_Extents where file_id = 23; reports 0 - so there seem to be no extents using that data file.  However, ALTER TABLESPACE tablespaceName DROP DATAFILE '<filepath>'; doesn't work.  I get the following:

Error report -
SQL Error: ORA-03262: the file is non-empty
03262. 00000 -  "the file is non-empty"
*Cause:    Trying to drop a non-empty datafile
*Action:   Cannot drop a non empty datafile

How can I drop this file?

Oracle here warn you from dropping datafile because it contain amount of data, to make sure the datafile is completely  empty please execute the following command

col tablespace_name format A30 heading tablespace
col file_name format A40
col tot_KB format 99,999,999
col tot_freeKB format  9,999,999
col Kb_max format  9,999,999

select f.tablespace_name
      ,f.file_name
      ,f.bytes/1024 tot_KB
      ,sum(nvl(s.bytes,0)/1024) tot_freeKB
      ,max(nvl(s.bytes,0)/1024) maxextent_freeKB
      ,( (sum(nvl(s.bytes,0)/1024)) / (f.bytes/1024) )*100 Pct_available
from   sys.dba_data_files f
      ,sys.dba_free_space s
where  f.file_id = s.file_id(+)
group by f.tablespace_name, f.file_name, f.bytes/1024
order by 1,2
;

if the datafile is completely empty, then you have one of the two option:

1) reboot the database ( sometime it help)

2) move all the data from one tablespace to another one, then drop the tablespace and rename the new one

You probably need to drop the tablespace, not just the datafile.

alter tablespace my_ts offline;
drop tablespace my_ts INCLUDING CONTENTS CASCADE CONSTRAINTS;


SQL Databases Linked to applications I have just become a DBA for a large company they are currently looking to decommission a 2008 R2 server.
Is there any way of being able to find out if the databases are still being used and by which application without taking the databases off line and seeing who yells that it's broken.

Thanks In Advance

A couple of relatively straight forward ways may be to create a logon trigger (it also talks about event notifications in that article), writing the login events to some audit table, or periodically query the sys.dm_exec_sessions DMV say in a SQL Server Agent job and record the information from that.

You can monitor the server's activity using SQL Server Profiler. The usual caveat that running it on the server itself doesn't sound like it should be a concern. Filter out activity from Profiler itself (if that's not done automatically).


SQL Partition Function without actual values I would like to partition a column by fiscalyear. How Do I create a partition function without harding coding values?  This is what I currently have below.

create table dbo.CustomerAccount
(
    CustomerAccountId bigint primary key identity(1,1),
    CustomerId int not null,
    FiscalYear int,
    AccountBalance numeric(15,2)
)

How Do I create a partition function without harding coding values?

CREATE PARTITION FUNCTION DateRangePF (datetime)
AS RANGE RIGHT FOR VALUES ('2015', '2016','2017')

You cannot create a dynamic VALUES clause in a CREATE PARTITION FUNCTION statement.  An alternative is to use the ALTER PARTITION FUNCTION statement to add a new range to the function.

I've encapsulated the required statements into a stored procedure:

IF OBJECT_ID(N'dbo.partition_function_add_date_range', N'P') IS NOT NULL
DROP PROCEDURE dbo.partition_function_add_date_range;
GO
CREATE PROCEDURE dbo.partition_function_add_date_range
(
      @function_name sysname
    , @range_date datetime
    , @scheme_name sysname
    , @next_filegroup sysname
)
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (
        SELECT 1
        FROM sys.partition_functions pf
        INNER JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
        WHERE pf.name = @function_name
            AND prv.value = @range_date
        )
    BEGIN
        DECLARE @cmd nvarchar(max);
        SET @cmd = N'ALTER PARTITION SCHEME ' + QUOTENAME(@scheme_name) + N' NEXT USED ' + QUOTENAME(@next_filegroup) + N';';
        PRINT @cmd;
        EXEC sys.sp_executesql @cmd;
        SET @cmd = N'ALTER PARTITION FUNCTION ' + QUOTENAME(@function_name) + N'() SPLIT RANGE (''' + CONVERT(nvarchar(max), @range_date, 120) + N''');';
        PRINT @cmd;
        EXEC sys.sp_executesql @cmd;
    END
END
GO

An example of how to call this stored procedure:

EXEC dbo.partition_function_add_date_range @function_name = N'DateRangePF '
    , @range_date = '2018-01-01 00:00:00'
    , @scheme_name = N'DateRangeSC'
    , @next_filegroup = N'PRIMARY';

This could be added to a SQL Server Agent Job, scheduled to run once a year, prior to rows being inserted into the new fiscal year.  The job step command would run something like this, which will automatically add the next year to the partition function, and any associated tables.

DECLARE @daterange datetime;
SET @daterange = CONVERT(varchar(30), DATEPART(YEAR, GETDATE()) + 1) + '-01-01 00:00:00';
EXEC dbo.partition_function_add_date_range @function_name = N'DateRangePF'
    , @range_date = @daterange
    , @scheme_name = N'DateRangeSC'
    , @next_filegroup = N'PRIMARY';

It's important to realize partition function modifications will affect ALL tables that use the partition function.

I think something like this has already been asked
https://stackoverflow.com/questions/18842764/how-to-dynamically-declare-partition-range-in-partition-function-in-sql-server

You could also look at interval partitioning
https://sqlfascination.com/2010/09/12/interval-partitioning-in-sql-server-2008/

You could also create a job that created the partition function for you on a regular basis.
https://social.technet.microsoft.com/Forums/sqlserver/en-US/d9f3cb78-a8c8-4bbb-8905-b02da2475a9f/dynamic-partition-creation-on-a-table-in-sql-server-2008


Can SQL Server 2000 and SQL Server 2012 CoExist? We have OLTP Databases on SQL Server 2000 on my client's Prod Server. Planning to create Data Warehouse on the same Server but use SQL Server 2012. Can I install without any issues?

As stated on MSDN side-by-side installation of SQL 2012 with earlier version is supported. However take note of the instance name, if your SQL 2000 instance is a default instance then your SQL 2012 install will have to be a named instance.

I would take note also that some issues may occur with SQL 2000 tools as your SQL Server 2012 installation may attempt to upgrade any shared components it finds. Although I don't know what of SQL 2000 would still exist in SQL 2012.

Now, my opinion and not the opinion of StackExchange, or my dog...I would not suggest configuring a side-by-side install with a SQL 2000 instance. Not knowing the setup, I would highly doubt the server configuration of a SQL 2000 instance is very well suited to what Microsoft suggest for a SQL Server 2012 installation. Especially considering the fact that SQL Server 2012 is only supported on Window Server 2008 SP2 and higher. This all depends on how old the server is and such but just my opinion.

I have not tried your exact case, but you will certainly experience some problems (deprecated functions, sql instructions...). You will also have to name your instances carefully.

I would much prefer having multiple instances with the same version. If you wish to migrate your 2000 database, keep in mind that according to the MSDN, the migration from Sql Server 2000 to Sql Server 2012 is not supported, as you need an intermediate step, usually Sql Server 2008R2.

[25/03/2013 Edit] my initial message looked a bit off-topic.


How can I determine how many IOPs I need for my AWS RDS database? I am migrating part of a mysql database into AWS.  The data in question is 99% write, and each row has about 1k of varchar fields, a datetime and 4 ints.

I estimate that we will need between 20-25k records inserted/hour during peak times.

I ran iostat -h on the current database, and it reported around 40 tps.

How do I figure out what type of IOPS I'll need?

You'll have to test it.

You could do some back of the envelope calculations to approximate the number of I/Os per insert, multiply it by the number of transactions per second, add in some buffer room etc, but it's much easier to just test it.

The easiest thing to do is to allocate a best guess, then go back and increase or decrease it to match you're real world testing. This is one of the luxuries of using a cloud based environment, hardware changes are low in capital costs and such changes usually require only config updates. With EBS volumes you can't just increase the number of IOPS, you'd have to scale up the size of the volume as well1. You can always just create a new volume and copy your data over. There will be some downtime but if you're data isn't huge it shouldn't be much as it'd be a raw copy.

Here's a guess at the number of I/Os necessary. Again it's just a guess though as the specifics depend on the number of indexes and whether your traffic flow will be smooth or spikey. At 25K tx/hour you've got ~7 tx/sec. The size of each row isn't particularly relevant as it's less than the size of a single I/O (4K). Each transaction will do somewhere between 1-5 IOP (primary insert plus a couple index tree inserts) so let's say ~35/s.

I say start out with the bare minimum of  100 IOPS and scale up if necessary.

This may help you to monitor your IOPS:

Burst Bucket Metric Now Available for Amazon EBS General Purpose SSD (gp2) Volumes


You have two right parenthesis more than left ones and a missing alias:

Select *
from wh_acct a                                 -- alias added
where a.rundate =
      (select max(a2.rundate)
       from wh_acct a2                         -- parenthesis removed
       WHERE a2.ORGA = a.ORGA)
UNION
Select *
from wh_acct a                                 -- alias added
where a.rundate =
      (select max(a2.rundate)
       from wh_acct a2                         -- parenthesis removed
       WHERE a2.PERS = a.PERS) ;

You could also write the query with OR instead of UNION:

Select *
From wh_acct a
Where a.rundate =
      (Select max(a2.rundate)
       From wh_acct a2
       Where a2.ORGA = a.ORGA)
   Or a.rundate =
      (Select max(a2.rundate)
       From wh_acct a2
       Where a2.PERS = a.PERS) ;

Or using window functions:

WITH cte AS
    ( SELECT ACCT_NBR, MAIL_TY, ORGA, PERS, RUN_DATE,
             MAX(rundate) OVER (PARTITION BY ORGA) AS max_rundate_orga,
             MAX(rundate) OVER (PARTITION BY PERS) AS max_rundate_pers
      FROM wh_acct
    )
SELECT ACCT_NBR, MAIL_TY, ORGA, PERS, RUN_DATE
FROM cte
WHERE rundate = max_rundate_orga
   OR rundate = max_rundate_pers ;

If, as your sample shows, all rows have NULL in orga where there is a value in pers and vice versa, you can change the UNION to UNION ALL to improve efficiency as there would be no need to check the subresults for duplicates (which is what UNION does). The window-cte version could also be rewritten as a union (ALL) of 2 subqueries.

Indexes on (orga, run_date) and (pers, run_date) would help, too.

You can use the anlytics function for this:
Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <....>] [])

Examples:

http://www.orafaq.com/node/55


PostgreSQL query takes a long time (as it's not using the index) This query takes a long time, as I guess it's doing a full-table scan. It works ok in Oracle, just not in Postgres 12.
select count(*)
FROM EVENT
WHERE PK_EVENT > (select CHECKPOINT1_PKEVENT FROM SYSTEM_CONTROL)

If however, I run this one, it comes back in seconds.
select count(*)
FROM EVENT
WHERE PK_EVENT > (1000755073)

Both the PK_EVENT & CHECKPOINT1_PKEVENT columns are defined as "int8", as they could get very large. EVENT.PK_EVENT is the primary key. I've tried re-arranging the query, but so far nothing works. The count(*) only results in 2537 rows i.e. the number of rows generated today on our test Pg database.
Query plans :
using system_control:-
Finalize Aggregate  (cost=2909961.80..2909961.81 rows=1 width=8)
  InitPlan 1 (returns $0)
    ->  Seq Scan on system_control  (cost=0.00..1.01 rows=1 width=8)
  ->  Gather  (cost=2909960.58..2909960.79 rows=2 width=8)
        Workers Planned: 2
        Params Evaluated: $0
        ->  Partial Aggregate  (cost=2908960.58..2908960.59 rows=1 width=8)
              ->  Parallel Seq Scan on event  (cost=0.00..2866882.04 rows=16831415 width=0)
                    Filter: (pk_event > $0)
JIT:
  Functions: 8
  Options: Inlining true, Optimization true, Expressions true, Deforming true

using hardcoded value:-
Aggregate  (cost=11.42..11.43 rows=1 width=8)
  ->  Index Only Scan using event_pkey on event  (cost=0.57..11.36 rows=25 width=0)
        Index Cond: (pk_event > 1000755073)

The entire query is planned up front, and at the time of planning it doesn't know what value will be found in CHECKPOINT1_PKEVENT.  It makes the generic assumption that the inequality will match 1/3 of the rows, which is obviously quite wrong.  When faced with this situation, I usually just have the client software run the queries separately, stuffing the result of one into a parameter for the other, assuming I can tolerate the prospect that the value may have changed between the two executions.
That said, it should probably be doing an index only scan anyway.  Do you have a very high setting for random_page_cost?  What are you other planner settings?  Has the table EVENT been vacuumed recently?

if you are using the * there is no use of index (apart from the PK). The subquery isn't using a WHERE clause so is doing a full table/index scan on SYSTEM_CONTROL
If you only want to get the number of rows you can change to this code
select count(PK_EVENT)
FROM EVENT
WHERE PK_EVENT > (select CHECKPOINT1_PKEVENT FROM SYSTEM_CONTROL)

To improve the above query you can create a nonclustered index on EVENT table using the PK_EVENT (in case this isn't the PK of that table already)
Also please provide the output of the EXPLAIN command:
EXPLAIN select count(PK_EVENT)
    FROM EVENT
    WHERE PK_EVENT > (select CHECKPOINT1_PKEVENT FROM SYSTEM_CONTROL)

Maybe you can try with this CTE:
WITH SYSTEM_CONTROL_CTE (CHECKPOINT1_PKEVENT )
AS
(SELECT CHECKPOINT1_PKEVENT
 FROM   SYSTEM_CONTROL)
SELECT Count(E.PK_EVENT)
FROM   SYSTEM_CONTROL_CTE as SC
    ,EVENT as E
WHERE E.PK_EVENT > SC.CHECKPOINT1_PKEVENT


Assumptions / Clarifications

No need to differentiate between infinity and open upper bound (upper(range) IS NULL). (You can have it either way, but it's simpler this way.)

NULL vs. infinity in PostgreSQL range types

Since date is a discrete type, all ranges have default [) bounds.
The manual:

The built-in range types int4range, int8range, and daterange all use a
canonical form that includes the lower bound and excludes the upper
bound; that is, [).

For other types (like tsrange!) I would enforce the same if possible:

Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

Solution with pure SQL
With CTEs for clarity:
WITH a AS (
   SELECT range
        , COALESCE(lower(range),'-infinity') AS startdate
        , max(COALESCE(upper(range), 'infinity')) OVER (ORDER BY range) AS enddate
   FROM   test
   )
, b AS (
   SELECT *, lag(enddate) OVER (ORDER BY range) < startdate OR NULL AS step
   FROM   a
   )
, c AS (
   SELECT *, count(step) OVER (ORDER BY range) AS grp
   FROM   b
   )
SELECT daterange(min(startdate), max(enddate)) AS range
FROM   c
GROUP  BY grp
ORDER  BY 1;

Or, the same with subqueries, faster but less easy too read:
SELECT daterange(min(startdate), max(enddate)) AS range
FROM  (
   SELECT *, count(step) OVER (ORDER BY range) AS grp
   FROM  (
      SELECT *, lag(enddate) OVER (ORDER BY range) < startdate OR NULL AS step
      FROM  (
         SELECT range
              , COALESCE(lower(range),'-infinity') AS startdate
              , max(COALESCE(upper(range), 'infinity')) OVER (ORDER BY range) AS enddate
         FROM   test
         ) a
      ) b
   ) c
GROUP  BY grp
ORDER  BY 1;

How?
a: While ordering by range, compute the running maximum of the upper bound (enddate) with a window function.
Replace NULL bounds (unbounded) with +/- infinity just to simplify (no special NULL cases).
b: In the same sort order, if the previous enddate is earlier than startdate we have a gap and start a new range (step).
Remember, the upper bound is always excluded.
c: Form groups (grp) by counting steps with another window function.
In the outer SELECT build ranges from lower to upper bound in each group. Voilá.
Or with one less subquery level, but flipping sort order:
SELECT daterange(min(COALESCE(lower(range), '-infinity')), max(enddate)) AS range
FROM  (
   SELECT *, count(nextstart > enddate OR NULL) OVER (ORDER BY range DESC NULLS LAST) AS grp
   FROM  (
      SELECT range
           , max(COALESCE(upper(range), 'infinity')) OVER (ORDER BY range) AS enddate
           , lead(lower(range)) OVER (ORDER BY range) As nextstart
      FROM   test
      ) a
   ) b
GROUP  BY grp
ORDER  BY 1;

Sort the window in the second step with ORDER BY range DESC NULLS LAST (with NULLS LAST) to get perfectly inverted sort order. This should be cheaper (easier to produce, matches sort order of suggested index perfectly) and accurate for corner cases with rank IS NULL. See:

PostgreSQL sort by datetime asc, null first?

Related answer with more explanation:

Compare multiple date ranges

Procedural solution with plpgsql
Works for any table / column name, but only for type daterange.
Procedural solutions with loops are typically slower, but in this special case I expect the function to be substantially faster since it only needs a single sequential scan:
CREATE OR REPLACE FUNCTION f_range_agg(_tbl text, _col text)
  RETURNS SETOF daterange AS
$func$
DECLARE
   _lower     date;
   _upper     date;
   _enddate   date;
   _startdate date;
BEGIN
   FOR _lower, _upper IN EXECUTE
      format(
         $sql$
         SELECT COALESCE(lower(t.%2$I),'-infinity')  -- replace NULL with ...
              , COALESCE(upper(t.%2$I), 'infinity')  -- ... +/- infinity
         FROM   %1$I t
         ORDER  BY t.%2$I
         $sql$, _tbl, _col)
   LOOP
      IF _lower > _enddate THEN     -- return previous range
         RETURN NEXT daterange(_startdate, _enddate);
         SELECT _lower, _upper  INTO _startdate, _enddate;

      ELSIF _upper > _enddate THEN  -- expand range
         _enddate := _upper;

      -- do nothing if _upper <= _enddate (range already included) ...

      ELSIF _enddate IS NULL THEN   -- init 1st round
         SELECT _lower, _upper  INTO _startdate, _enddate;
      END IF;
   END LOOP;

   IF FOUND THEN                    -- return last row
      RETURN NEXT daterange(_startdate, _enddate);
   END IF;
END
$func$  LANGUAGE plpgsql;

Call:
SELECT * FROM f_range_agg('test', 'range');  -- table and column name

The logic is similar to the SQL solutions, but we can make do with a single pass.
SQL Fiddle.
Related:

GROUP BY and aggregate sequential numeric values

The usual drill for handling user input in dynamic SQL:

SQL injection in Postgres functions vs prepared queries

Index
For each of these solutions a plain (default) btree index on range would be instrumental for performance in big tables:
CREATE INDEX foo on test (range);

A btree index is of limited use for range types, but we can get pre-sorted data and maybe even an index-only scan.

For fun, I gave it a shot. I found this to be the fastest and cleanest method to do this. First we define a function that merges if there is an overlap or if the two inputs are adjacent, if there is no overlap or adjacency we simply return the first daterange. Hint + is a range union in the context of ranges.

CREATE FUNCTION merge_if_adjacent_or_overlaps (d1 daterange, d2 daterange)
RETURNS daterange AS $$
  SELECT
    CASE WHEN d1 && d2 OR d1 -|- d2
    THEN d1 + d2
    ELSE d1
    END;
$$ LANGUAGE sql
IMMUTABLE;

Then we use it like this,

SELECT DISTINCT ON (lower(cumrange)) cumrange
FROM (
  SELECT merge_if_adjacent_or_overlaps(
    t1.range,
    lag(t1.range) OVER (ORDER BY t1.range)
  ) AS cumrange
  FROM test AS t1
) AS t
ORDER BY lower(cumrange)::date, upper(cumrange)::date DESC NULLS first;


Index performance for CHAR vs VARCHAR (Postgres) In this answer (https://stackoverflow.com/questions/517579/strings-as-primary-keys-in-sql-database) a single remark caught my eye:

  Also keep in mind that there's often a very big difference between a
  CHAR and a VARCHAR when doing index comparisons

Does this apply / still apply for Postgres?

I found pages on Oracle claiming that CHAR is more or less an alias for VARCHAR and so index performance is the same, but I found nothing definitive on Postgres.

CHAR and VARCHAR are implemented exactly the same in Postgres (and Oracle). There is no difference in speed when using those data types.

However, there is one difference that can make a difference in performance: a char column is always padded to the defined length. So if you define a column as char(100) and one as varchar(100) but only store 10 characters in each, the char(100) column uses 100 characters for each value (the 10 characters you stored, plus 90 spaces), whereas the varchar column only stores 10 characters.

Comparing 100 character with 100 characters is going to be slower than comparing 10 characters with 10 characters - although I doubt you can actually measure this difference in a SQL query.

If you declare both with the length of 10 characters and always store exactly 10 characters in them, then there is absolutely no difference whatsoever (this is true for Oracle and Postgres)

So the only difference is the padding that is done for the char data type.

  Also keep in mind that there's often a very big difference between a CHAR and a VARCHAR when doing index comparisons

The above quote is only true if (and only if) the char column is defined too wide (i.e. you are wasting space due to padding). If the length of the char column is always used completely (so no padding occurs), then the above quote is wrong (at least for Postgres and Oracle)

From my point of view, the char data type does not really have any real-word use. Just use varchar (or text in Postgres) and forget that char exists.

I found this most useful, and a fast 3 line explanation:

  From CHAR(n) Vs VARCHAR(N) Vs Text In Postgres

  If you want to store some text with an unknown length, use the TEXT data type.
  If you want to store some text with an unknown length, but you know the maximum length, use VARCHAR(n).
  If you want to store some text with a known exact length, use CHAR(N).


Exact behaviour of CHANGE_TRACKING AUTO I am trying to understand population options for SQl server's full text indexes. My target is to access whether our currently used option (default = CHANGE_TRACKING AUTO) is the best to use in our case.

I think I have understood perfectly every bit of information in the related MS reference page, except from this devilish line from the subchapter Population based on change tracking/Enable change tracking/Automatic population:

  ...the tracked changes are propagated automatically. The full-text
  index is updated in the background, however, so propagated changes
  might not be reflected immediately in the index.

Nothing in the following reference, however, gives any information about what exactly not immediately means in regards to the exact time the population happens. I have found around queries that can return the last timestamp the population took place; but how can I have an idea of how often and when the system tries to, or indeed executes, the population?

concerning "The full-text index is updated in the background, however, so propagated changes might not be reflected immediately in the index." from what I can tell and experience it means the change to the full-text is updated as any other clustered index, using a background task and not on the immediate instant that the data was changed and the update was triggered.

Also reading a bit more on the documentation (create fulltext index):
"CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [ , NO POPULATION ] }
Specifies whether changes (updates, deletes or inserts) made to table columns that are covered by the full-text index will be propagated by SQL Server to the full-text index. Data changes through WRITETEXT and UPDATETEXT are not reflected in the full-text index, and are not picked up with change tracking."

This is for the manual option meaning that for the AUTO the reverse can be applied, the change is done by the change tracking event and not immediate.

And here is why (Population based on change tracking):

"Optionally, you can use change tracking to maintain a full-text index after its initial full population. There is a small overhead associated with change tracking because SQL Server maintains a table in which it tracks changes to the base table since the last population. When you use change tracking, SQL Server maintains a record of the rows in the base table or indexed view that have been modified by updates, deletes, or inserts. Data changes made through WRITETEXT and UPDATETEXT are not reflected in the full-text index, and are not picked up with change tracking."


Why would I ever prefer ALGORITHM=COPY to ALGORITHM=INPLACE? Since MySQL 5.6 introduced online DDL, the ALTER TABLE command can optionally have either ALGORITHM=INPLACE or ALGORITHM=COPY specified. The overview of online DDL notes that, by default, INPLACE is used wherever possible, and implies (without ever quite stating it) that the INPLACE algorithm is cheaper than the COPY one is.

So what reason would I ever have to specify ALGORITHM=COPY on an ALTER TABLE statement?

Yes, there are cases when you may specify COPY, but it would be for other reasons than performance.

It is important to understand that MySQL introduced new feature - Online DLL processing in version 5.6. It did not remove offline processing. So there is a need to differentiate between these 2 modes:

Some operations still work in Offline mode only. See Table 15.10, “Summary of Online Status for DDL Operations” for a list of the DDL operations that can or cannot be performed in-place.
Operations in Online and Offline modes have slightly different behavior, so you can choose "old" one for compatibility reasons.

Some examples (please suggest more):

InnoDB tables created before MySQL 5.6 do not support ALTER TABLE ... ALGORITHM=INPLACE for tables that include temporal columns (DATE, DATETIME or TIMESTAMP) and have not been rebuilt using ALTER TABLE ... ALGORITHM=COPY. In this case, an ALTER TABLE ... ALGORITHM=INPLACE operation returns error.
ADD PRIMARY KEY clause in COPY mode silently converts NULL to default values for that data type (0 for INT,  empty string for varchar), whereas IN_PLACE does not do that.

  With the ALGORITHM=COPY clause, the operation succeeds despite the
  presence of NULL values in the primary key columns; the data is
  silently changed, which could cause problems.

Another reason to prefer COPY:

  Operations for which you specify ALGORITHM=COPY or old_alter_table=1,
  to force the table-copying behavior if needed for precise
  backward-compatibility in specialized scenarios.

Although MySQL manual doesn't talk about actual scenarios, you can imagine some. E.g. developer relied on table being locked during ALTER INDEX operation so table is read-only or fully locked and there is a process that reads static table during index rebuild.

In versions of MySQL that support InnoDB tablespace encryption, when you alter a table to add encryption, the alteration is done using the copy algorithm out of necessity.


Rename SQL log and master database file I am working on SQL Server 2014. I want to know how can I rename the .ldf and .mdf files of my database after creating it? If it is possible what are the steps?

You can't rename the files while they are in use, and don't make them "not in use" by stopping the SQL Server, it won't pick up on the change automatically.

The quickest way is as follows:

Detach the database with EXEC sp_detach_db 'database_name' or the appropriate dialogue in SQL Server Management Studio.
The database must have no current connections otherwise SQL Server won't allow you to detach it - if you have trouble with this force that database into single user mode.
Rename the files as needed
Reattach the database which assuming you ave only a single data file and a single log files is done with EXEC sp_attach_db 'database_name', 'full_path_to_mdf', 'full_path_to_ldf', for example: EXEC sp_attach_db 'my_renamed_db', 'd:\dbs\data\my_renamed_db.mdf', 'd:\dbs\logs\my_renamed_db.ldf'
Again, the appropriate dialogue in SQL Server Management Studio can be used instead.

As you are specifying the database name in the call to sp_attach_db, this does the rename step also.

Update:

As Travis reminds me in the comments, sp_attach_db has been officially deprecated for a while and you should use the newer DDL method instead. The above example becomes:

CREATE DATABASE my_renamed_db
    ON (FILENAME = 'd:\dbs\data\my_renamed_db.mdf')
     , (FILENAME = 'd:\dbs\data\my_renamed_db.ldf')
FOR ATTACH;

Slightly safer even, you can avoid completely detaching the database at all by setting it offline and modifying the file locations instead (you still have to manually move or rename the files though):

ALTER DATABASE my_renamed_db SET OFFLINE;
ALTER DATABASE my_renamed_db MODIFY FILE (NAME='data_file_name', FILENAME='d:\dbs\data\my_renamed_db.mdf');
ALTER DATABASE my_renamed_db MODIFY FILE (NAME='log_file_name', FILENAME='d:\dbs\data\my_renamed_db.ldf');
ALTER DATABASE my_renamed_db SET ONLINE;

You can move/rename the files any time after the SET OFFLINE but before SET ONLINE.

If you don't know the logical filenames you can look them up in SSMS or via

SELECT f.name, f.physical_name
FROM   master.sys.databases d
JOIN   master.sys.master_files f
       ON d.database_id = f.database_id
WHERE  d.name = 'my_renamed_db'

If moving tempdb things are different: you can't offline it and it is recreated every time the server starts. Instead make the ALTER DATABASE MODIFY FILE calls and restart the SQL Server instance.

It's possible. But mostly avoid to rename of (.mdf) or (.ldf) live accessing database file.

There are some couple of steps through which you can do that through T-SQL as below mention steps:-

1) Set Database in single user mode
2)Rename the Database
3)Set Database to Multi-User Mode
4)Rename Logical File Name
5)Checking Physical name
6)Detach Database
7)Attach New Database

For ref  http://blog.codespecies.com/2013/07/rename-a-database-and-its-mdf-and-ldf-files-in-sql-server/  and

http://mediumcube.com/mctalk/2013/07/05/how-to-rename-mssql-database-mdf-and-ldf-files/.

NB:- Always consult to Database Team before Executing T-SQL file in live database in production Server. Because it's directy affect in DB.


Change archivelog location in Oracle Oracle instance I set up is using USE_DB_RECOVERY_FILE_DEST for archivelog location.

log_archive_dest_1                   string

LOCATION=use_db_recovery_file_
dest, valid_for=(ALL_ROLES,ALL
_LOGFILES)

I want to specify the exact location. Old and new location is same, only need to specify the path. I cannot restart the database as it is production server. Is there any way I can do it without restarting the database

There are the manuals Specifying Archive Destinations,LOG_ARCHIVE_DEST_n. But they are unclear about if it is possible to change it online.

Try it out on a test instance. You always should try out thinks on a test instance. Especially if you are using some tips from the internet.
If you an alter system set = ... scope=both; and the parameter cannot be specified in memory, you get a

ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

error, but nothing bad happens. So you can try it out if the parameter is specified in memory.

I tried to set the parameter to the value already set and it worked

SQL> alter system set log_archive_dest_1=
  2  'location=USE_DB_RECOVERY_FILE_DEST, valid_for=(ALL_LOGFILES,ALL_ROLES)'
  3  scope=both;

System altered.

SQL>

If you change the destination for the archives and there are still archives in the old destination then the database knows this an therefore RMAN knows this and will not forget them but backup them when you do your next archive log backup.

It is not possible to change the location of archive log destination without restarting the database. If the you provide the scope as memory they you will get an error.

And if you provide the scope as spfile, then you need yo restart the database.

So I think you need to ask some downtime as do the operation.


Is it possible to backup and restore part of a database in sql-server? We have a sql-server 2005 database that we regularly transfer from our client site to ours. This takes a long time because we don't have a direct connection and have to transfer the file over their web based file transfer application. The database is currently about 10GB however we don't need all the data - most of it is in audit tables and tables that hold calculated values that can be re-generated.

I have looked at creating a filegroup to hold the audit tables and was hoping I could just backup and restore the primary filegroup. I can backup fine but when restoring I get an error saying that I'm not restoring it to the same database. Is it possible to restore part of a database to a different server using filegroups? Is there a better way to do this?

To be honest, this is easiest:

backup/restore a copy locally
remove unwanted data from the copy (with DELETE or TRUNCATE TABLE, not DROP...)
ship the copy

I wouldn't bother with filegroups because of the added complexity you noted...

Right click and generate scripts, then change the name of the database to target (USE db).

Explained here


How often does Postgres or Mysql make the fsync call? How often does Postgres or Mysql make the fsync call?

Does Postgres or Mysql fsync for every write to the Write Ahead log?
Does Postgres or Mysql fsync for every transaction? so if I do a SQL Insert will postgres or mysql fsync immediately?

I can only answer for PostgreSQL:
About sync on the WAL:
The transaction log (WAL) is synced to disk on every commit (unless you set synchronous_commit = off.
In addition, the WAL writer regularly syncs the WAL; this is governed by wal_writer_delay (this will only take effect if WAL hasn't been synced for a while). Also, if more than wal_writer_flush_after bytes have been written without a sync, data will be synced to disk.
About sync on the data files:
Data files are normally synced during checkpoints.
Since PostgreSQL uses buffered I/O, there is the danger that too many dirty blocks in the file system cache cause write spikes. Since version 9.6, PostgreSQL has various *_flush_after parameters that avoid that by regulary syncing data when a lot is written:

bgwriter_flush_after (default 512K) makes the background writer flush data to disk after it has written that amount of data
backend_flush_after (default 0 = disabled) makes the client session flush data to disk after it has written that amount of data
checkpoint_flush_after (default 256KB) makes the checkpointer flush out data after writing this much, rather than flushing all at the end

Well short answer to both of your question is "NO" but as one of the answer listed here mentioned that it depends on the setting that you made up.
Now obvious question comes in mind why i am saying so. Let me try to be simple as much as possible . Remember I m using syntax of MySQL here.In MySQL we call it redo log files and in Postgres same thing is called as WAL file but both are same and concept is same . But also remember the transaction flow and process in both is entirely different . For me I found PG is bit complex as compared to MySQL and this complexity is sometimes useful and sometimes not.
fsync is a call made by DB to flush the "data in memory" which means that OS has asked memory to pass the data to the socket or file . Once it reaches there it get sync up with the permanent storage. So fsync do flushing of data file and log file.
Some links I must recommend you to read ( if you haven't yet )
https://man7.org/linux/man-pages/man2/fsync.2.html
https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_flush
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_method
Whenever any transactions comes , it firstly goes to the MTR buffer which is mini transaction buffer and is not visible to the client (i.e. YOU or person who is using mysql client). Post MTR buffer , it goes to the redo log buffer and the next stage is red log file i.e. iblogfile . Now depending on the parameters like "innodb_flush_at_trx_commit" and on the size of log buffer , data get flush from log buffer to the redo log files ( which can contain even uncommitted transactions ). Now lets say that you have sufficient amount of log buffer size and value of transaction commit is every second then it means that on every transaction being  made fync is not getting called. Infact I must say calling fsync on every transaction is an expensive operation in terms of I/O. But if log buffer is small and as per configuration innodb is flushing on every transaction then yes fsync will be called and I/O will increase.
If you wanna understand more then you can refer below code documentation of MySQL
https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_INNODB_REDO_LOG.html#sect_redo_log_general


Passing database name to input parameter from a stored procedure I have some issue with this application. I know that there are a lot of topics about passing a Database name as input but still I couldn't figure out what is the issue my code.
Hopefuly, you guys can help me out !
I have few database and everyday, a new database is created with XXXXX-YEARMONTHDAY as name.
What i want to achieve is to pass a new database name as input parameter from my stored procedure.
When it's hard written, it's working like a charm :
GO
/****** Object:  StoredProcedure [dbo].[WinccToAlarms]    Script Date: 03-12-20 08:26:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[WinccToAlarms]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO Reporting.dbo.LiaisonAlarmes
    SELECT * FROM [WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924].dbo.AlgRtTextsFRA
END

But i want to dynamically name the [WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924] part of the request.
Here is the code that i wrote :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE WinToAlarms20
    -- Add the parameters for the stored procedure here
    @NomDBAlarms sysname
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SET XACT_ABORT ON

    IF DB_ID(@NomDBAlarms) IS NULL
    BEGIN
    RAISERROR('Nom de base de donnée invalide',16,1)
    RETURN
    END

    DECLARE @Sql NvarChar(max)

    -- Insert statements for procedure here
    SET @Sql = N'INSERT INTO Reporting.dbo.LiaisonAlarmes
    SELECT * FROM ['+(@NomDBAlarms)+'].dbo.AlgRtTextsFRA'

    Exec @Sql
    END
GO

Can you help me figure out what is wrong with this piece of code?
Thanks a lot !!!

Ok. This is solved. I had to enter as input parameter the value [WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924] instead of WIN-9SEBDMDC9O9_HMI#B5N2_ALG_202012020924
Also for anyone who might want the final and working code; Here it is !
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE WinToAlarms22
    -- Add the parameters for the stored procedure here
    @NomDBAlarms sysname
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SET XACT_ABORT ON

    IF DB_ID(@NomDBAlarms) IS NULL
    BEGIN
    RAISERROR('Nom de base de donnée invalide',16,1)
    RETURN
    END

    DECLARE @Sql NvarChar(max)

    -- Insert statements for procedure here
    SET @Sql = N'INSERT INTO Reporting.dbo.LiaisonAlarmes
    SELECT * FROM ['+@NomDBAlarms+'].dbo.AlgRtTextsFRA'

    Exec sp_executesql @Sql
    END
GO

Thank you for your support and have a nice day !

I don't know your error message, but try to replace Exec @Sql with Exec sp_executesql @Sql


Database Design for Questions and Answers I'm building a website that will host a variety of questionnaires. Each questionnaire has a different number of questions and each question has a different number of answers. I have attempted to design a database to hold the questions and possible answers for each questionnaire, but I end up having separate tables for each question. Is this correct or am I going wrong somewhere?

For example

Table for question x

  Answer  | Answer ID
       1         019
       2         089

I cant have a fixed size table for all questions as I don't have a maximum amount of answers. This obviously means I could end up with hundreds of tables, one for each question.

We have an application at our workplace that does a similar thing. It works by having a table that contains a list of all possible questions like such:

CREATE TABLE QUESTIONS
(
   ID INT NOT NULL PRIMARY KEY,
   SUMMARY NVARCHAR(64) NOT NULL UNIQUE,
   DESCRIPTION NVARCHAR(255) NULL
);

Then you have an ANSWERS and a QUESTIONAIRES table defined using the same structure above. Once you have these two tables you then define a table to hold the list of question/answer possibilties as such:

CREATE TABLE QUESTION_ANSWERS
(
   ID INT NOT NULL PRIMARY KEY,
   QUESTION INT NOT NULL REFERENCES QUESTIONS(ID),
   ANSWER INT NOT NULL REFERENCES ANSWERS(ID)
);

Once you have these you can then create a table to contain the responses as such:

CREATE TABLE RESPONSES
(
   QUESTIONAIRE INT NOT NULL REFERENCES QUESTIONAIRES(ID),
   RESPONSE INT NOT NULL REFERENCES QUESTION_ANSWERS(ID)
);

This will give you maximum flexibility allowing you to add new questions and answers without having to change your database design frequently. It can get a bit complicated if you need to version the questions/answers but this should give you a good foothold to work from.

I hope this helps you.

QUESTIONNAIRE
QUESTIONNAIRE_ID (PK),
QUESTIONARIE_TITLE

QUESTIONNAIRE_QUESTIONS
QUESTIONARIE_QUESTIONS_ID (PK),
QUESTIONARIE_QUESTIONS,
QUESTIONNAIRE_ID (PK)

ANSWERS
ANSWER_ID (PK),
QUESTIONARIE_QUESTIONS_ID (FK)


SQL Server Isnull Returning 1900-01-01 when field is null The following piece of code is returning 1900-01-01 when the DOB field is null. I wanted (and expected) it to return an empty string ('') but it's not. How should I proceed to get my desired results?

isnull(convert(date,DOB,1),'')

You can't get an empty string because you're returning the DATE value type from ISNULL.
Per the docs, ISNULL

Returns the same type as check_expression. If a literal NULL is
provided as check_expression, returns the datatype of the
replacement_value. If a literal NULL is provided as check_expression
and no replacement_value is provided, returns an int.

If you're checking whether or not the value is NULL, there is no need to convert it to a date, unless you wanted to return a date value (which you don't seem to).
Instead, use:
SELECT ISNULL( DOB , '')

Which will return
''

if the value is NULL.
A NULL date is NULL (no value). An empty string, on the other hand, evaluates to 0, which in SQL Server is implicitly an integer representing the number of days since 1900-01-01.

This will convert the NULL date to space. The application layer (Excel) handles space fine

ISNULL (CONVERT(varchar(50), w.TRANSACTIONDT, 120), '')  as wTRANSACTIONDT


SQL query for matrix multiplication I am facing a problem in writing a sql query for multiplication of two matrices.
Consider that i have a 3x3 matrices A and B, having the following schema:

A(i,j,val) and B(i,j,val) where

i int: Row index

j int: Column Index

val int: Cell value

The product of matrix A and matrix B is matrix C having cell at row i and column j. I want a single sql query to get the matrix product of A and B.

Input (A, then B):

set pages 12

with A as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, rownum as val from dual connect by level <= 9
),
B as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, 12 - rownum as val from dual connect by level <= 9
)
select A.i, A.j, A.val from A union all
select B.i, B.j, B.val from B
;

         I          J        VAL
---------- ---------- ----------
         1          1          1
         1          2          2
         1          3          3
         2          1          4
         2          2          5
         2          3          6
         3          1          7
         3          2          8
         3          3          9

         I          J        VAL
---------- ---------- ----------
         1          1         11
         1          2         10
         1          3          9
         2          1          8
         2          2          7
         2          3          6
         3          1          5
         3          2          4
         3          3          3

And their product (AB):

with A as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, rownum as val from dual connect by level <= 9
),
B as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, 12 - rownum as val from dual connect by level <= 9
)
----------------------------------------------------------------
select
  A.i as i,
  B.j as j,
  sum (A.val * B.val) as val
from
  A, B
where A.j = B.i
group by A.i, B.j
order by
  1, 2
;

         I          J        VAL
---------- ---------- ----------
         1          1         42
         1          2         36
         1          3         30
         2          1        114
         2          2         99
         2          3         84
         3          1        186
         3          2        162
         3          3        138

Just join by i and j, and multiply val. If the samples are incomplete in either matrix, you may want to grab the union of both matrices and left join.

with A AS (...), B as (...),
     C AS (select i, j from A union i, j from B)
select C.i, C.j, COALESCE(A.val * B.val, 0) AS val
from C
left join A on (A.i = C.i AND A.j = C.j)
left join B on (B.i = C.i AND B.j = C.j)
order by 1, 2;


Patterns for Deleting large amounts of data So I'm looking at how to delete a large amount of data from a handful of tables that do not have the key I need to easily isolate the rows that need to be deleted.  The situation is as so:

I have a ID let's say a RequestID.  I've decided that all of an arbitrary RequestID's data entries are invalid and so I want to purge them out of my tables to clean up.

Table A is a dimensional table that has my RequestID and it also has all the associated URL IDs for any particular RequestID.

Table B also contains dimensional data but does not have RequestID, so I have to use Table A to look up which records in Table B are valid delete candidates.

These tables are anywhere from 1 million to a billion rows, so the deletes have to be batched to work properly.

My thought was to do something like this but it doesn't seem very performant:

WHILE EXISTS (SELECT TOP 1 1 FROM TableB JOIN TableA ON TableA.URLID = TableB.URLID)
BEGIN
DELETE TOP 50000 a
FROM TableB a
JOIN TableA ON TableB.URLID = TableA.URLID
WHERE TableA.RequestID = <some_value>
END

I'm not sure how better I could delete the data

Edit: sorry I forgot to include requestID in the delete code example

but you are missing RequestID
hopefully you have index on URLID  and RequestID

select 1
while (@@rowcount > 0)
BEGIN
  DELETE TOP (50000) b
  FROM TableB b
  JOIN TableA a
    ON b.URLID = a.URLID
   AND a.RequestID = @RequestID
END

Disable FK can help but be sure you are not breaking any FK


How do I update azure SQL baseline to remove certain db owners? I need to update the baseline to reflect only the approved list of owners and remove others.
Can anyone tell me how do I do that?
Here is what I see:

How do I remove the owners from baseline marked in red?

You can remove members from a role using the sp_droprolemember stored procedure. Similarly, the stored procedure sp_addrolemember can add additional users to the role.
Here is a statement to remove members from the db_owner role.
EXEC sp_droprolemember [db_owner],[<User Name here>]

Another easier way to do this is by clicking on 'Approve as a baseline' after adding or removing the database permissions of the users.


Upgrade in-place with low disk space on C: drive I am due to upgrade SQL Server 2005 Express to 2014 Express. This will be an in-place upgrade.

It's not my preferred way of upgrading, but being a physical machine and in a specific secure network zone, it has to stay put & keep its instance name, so it must be an in-place upgrade.

The only small issue is there is just under 10% free space available on the C: drive (the drive has 3GB space left). I hope to upgrade in-place and then once complete, go through getting disks replaced for bigger disks etc. (this is due to project upgrade deadlines).

Could someone tell me how much extra space will be put onto the C: drive with a SQL Server Express 2014 upgrade, please? Note no system databases live on the C: drive. Sadly no housekeeping can be performed by the Windows team etc.

Can you do it? Yes, you can. Should you do it? No, no shouldn't.

You're playing with fire, less than 10% free space on C drive is an urgent/critical flag in any decent company.

If you spot anything worth deleting, go for it. But in any case, since this is a physical server and you said Wintel team can't help you, present the scenario as an issue and a possible emergency in the future.

Per Microsoft recommendation, it does require 4.2 GB of Disk Space for a fresh instillation: https://www.microsoft.com/en-us/download/details.aspx?id=42299


Find the IDs and names of the employees whose salaries are at least 10% higher than the average salary of all employees I need to find the IDs and names of the employees whose salaries are at least 10% higher than the average salary of all employees. I can't get the code to work with the 10% higher than the average. I know how to get the average, but when I run my particular code, I get all names and know that it's not running correctly.

Here is my query:

Select employees.eid,
       CONCAT(efirstName,
       ' ', eLastName) AS Name
from employees
GROUP BY employees.salary
HAVING employees.salary > avg(salary * .10);

I have also tried:

Select employees.eid,
       CONCAT(eFirstName,
       ' ',
       eLastName AS Name
FROM employees
GROUP by eLastName
HAVING avg(salary) > avg(salary * .10);

Using a simple subquery should help with this:

SELECT e.eid, CONCAT(efirstname, ' ', e.elastname) name
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees) * 1.10

Can u try this:

    SELECT e.eid, concat(e.eFirstName, ' ', e.LastName) as name
    FROM employees e
    WHERE e.salary > AVG(e.salary) * 0.1;


What other databases have a feature similar to Oracle's Flashback Query? I am interested to know what other databases have features similar to Oracle's Flashback Query. It would seem that any database that supports MVCC could have the feature, but which do?  SO has this question for SQL Server, but it seems more appropriate here.

PostgreSQL does not currently have it.  But you are right, with MVCC it should theoretically be possible.  In fact, the old university Postgres had an equivalent feature called time travel, but it was later removed because it caused too much overhead and the practical use was limited.  While it would probably be possible to resurrect it, since then there have been many optimizations to the MVCC setup that would make it much harder.

It's a pity that flashback feature is an addon in Oracle and only available in Enterprise Edition. DB2 10.1 has time travel query which is integrated into its database engine and available in all editions. You can download its free version from IBM and start using it right away!


Composite primary key plus a separate (surrogate) id column for foreign key references I am trying to build a database about locations like so:

Country → Region → Town

Countries will be populated with a list of pre-defined worldwide countries, regions & towns are input by users.
Region may not apply to every location and so a blank string will be allowed (hence the many to many link table).
This is the best design I have come up with:

The town_id column is set to AUTO_INCREMENT & UNIQUE. It is then used as a foreign key reference in other tables.
The town column on its own is not UNIQUE as you may have the same town name in multiple places.
Is the towns table correct?
The reason I ask is because it just seems odd to me to have the town_id as the reference, however this cannot be included or used as the primary key (because if it did, it would cause duplicates)?
I would welcome feedback if this approach is correct.

I would recommend using it (town_id) as the only primary key (provided my first comment is true about the increment and uniqueness), and the Region/Country IDs should only be foreign keys. This would allow you to have duplicate town names.

You might want to put a unique index on country_id, region_id, town, as I'm pretty sure there's no political subdivision that has two towns of the same name, and it would reflect that business logic.

I believe you can create a primary key for region and reference region in country and reference country in town so the data will be normalized for 3nf. like this


How to set Persian calendar,currency,numbers,date and time and text in oracle database? I have installed oracle database 12c on my laptop.

I want to be able to store text,numbers,calendar,currency,... in my local language (Persian) in database tables and also show contents in Persian when retrieving data from tables.

I'm also using APEX 5 that I want these capabilities in there too?

Is it possible? Does Oracle support these in both database 12c and APEX 5?
How can I set these settings and where?

I'm using Windows 8.1 enterprise x64.

The magic key word is NLS (National Language Support). It will define the dateformat, calender format etc.

You can query your current NLS-settings with this command:

SELECT * FROM nls_database_parameters;

If you want to change it in a session or an instance you can query the current settings using this views:

SELECT * FROM nls_session_parameters;
-- or
SELECT * FROM nls_instance_parameters;

You need to set this NLS settings, maybe the proper ISO as character set too.

NLS_LANG=ARABIC_SAUDI ARABIA.UTF8
NLS_LANGUAGE=PERSIAN
NLS_CALENDAR=PERSIAN

You have some possible solutions to setting the NLS. If you want to use it globally I would suggest to set it in the initialization parameters.

You can read more about setting the NLS in the docs.

Oracle 12c should support persian, as stated in this whitepaper in the section "New Locale Coverage".

By the way, if you need a larger multi language support, you may want to use AL32UTF8 instead of normal UTF8 language sets. You can read something about it here and here.

You can go to the registry (considering you have a windows machine) can update the nls_lang variable to ARABIC_SAUDI ARABIA.UTF8. It should do the work.


Query to Access a specfic data in the row using python Am using wordpress and phpmyadmin for handling my database
Below is the code I use to view entries on my form using python. In the form I ask users to check/uncheck specific questions. It works as desired.

import MySQLdb as mdb
con = mdb.connect('localhost', 'root', 'password', 'WordpressDB', unix_socket="/opt/lampp/var/mysql/mysql.sock");
with con:
    cur = con.cursor()
    cur.execute("SELECT data FROM `wp_form_builder_entries` WHERE entries_id = 5")
    rows = cur.fetchall()

Output generated by this code is as follows:

Works just as desired but I don't want all the data. I just need to know whether its checked or unchecked. If the user has checked value is "s:9:'checked'" else its "s:9:'unchecked'"

What query should I use to retrieve "s:9:'checked'"?

If I understand it correctly, you are storing all data in one field of the table? If so, MySQL will not be of much use as you need to unserialize the data in python. If so, then this question is not really for DBA, but more for StackOverflow.

If you however want to restructure the table and keep various data elements in different columns instead of one column called "data", then you could SELECT only that particular column(s) that are relevant instead of querying whole string.

You need to use the limit statement! This will return only one result set.
Since you have more recourds for  entries_id = 5 without the limit statement you will get all the results.
Try the example bellow:

import MySQLdb as mdb
con = mdb.connect('localhost', 'root', 'password', 'WordpressDB', unix_socket="/opt/lampp/var/mysql/mysql.sock");
with con:
    cur = con.cursor()
    cur.execute("SELECT data FROM `wp_form_builder_entries` WHERE entries_id = 5 limit 1")
    rows = cur.fetchall()


Mysql Connection Pooling Similar To PostgreSQL's PgBouncer I'm looking for a pooling solution for mysql that is independent of the language. I am using PGBouncer as an example because it

Resides on the server the database is on
Just change the connection string to use the pooling connector
No modification to the code required

Is there something similiar in MySQL where I can using pooling by installing a 3rd party connector and just connect through that port?

This started off as a comment but it's getting a bit long.

Perhaps you could provide a better description of what it is you actually want rather than simply say it's like something else.

If you want multiple clients to use the same connection, then it must be implemented in the DBMS, otherwise you have no session management. Further you need to advise how you intend dealing with issues of state spanning more than a single request/response (i.e. transactions).

It would also be helpful to know why you want it. MySQL has a very different setup cost and per-session overhead cost compared to PostgreSQL - why do you think it needs it, and why you think it would actually yeild any benefit.

Actually PGBouncer is good in pooling and distributing connections from the DB server itself, however there is MySQL-Proxy and ProxySQL (not embedded in MySQL itself) which can accomplish connection pooling and other functionalities for you. Some connectors though can have connection pooling built-in like Java Connector , check this

Also, you should be aware of this issue or problem. Say you have 10 application servers and 1 database server configured for 500 maximum connections. You set the individual application server pool sizes to 50. Now you need to add a new application server. You would have to tune all 10 of your old servers dropping their pool sizes down to make room for the new one. This problem gets more and more severe as you add more servers.

Also, tuning individual pool sizes per client means there is a chance that one server is starving while others have pooled connections sitting idle – the connections aren’t shared


Why I don't need to COMMIT in database trigger? We can't COMMIT/ROLLBACK in DML triggers because transaction is handled manually after DML statement. However, database triggers seems to be an exception. For example, suppose there's a database trigger:

CREATE OR REPLACE TRIGGER user_login_as
  AFTER LOGON
    ON SCHEMA
BEGIN
  INSERT INTO user_login_log(username, log_date, action) VALUES (user, sysdate, 'User has logged in');
END user_login_as;

The trigger does not contain autonomous transaction procedure with commit inside that, so who is commiting the insert? This triggger works like a charm and inserts new record into log table after user logon. It smells like hidden Oracle functionality and I can't find any reference in Oracle docs about that. I'm using Oracle11g.

You get an autonomous transaction context for these triggers automatically.

From the CREATE TRIGGER docs:

  One or more particular states of the database that can cause the trigger to fire. You can create triggers for these events on DATABASE or SCHEMA unless otherwise noted. For each of these triggering events, the database opens an autonomous transaction scope, fires the trigger, and commits any separate transaction (regardless of any existing user transaction).

What happens if the trigger fails depends on the exact trigger/event. See Exception Handling in Triggers. In particular, a logon trigger that fails with an exception might very well lock non-dba users out - the exception makes the login fail unless the user has specific privileges.

CREATE OR REPLACE TRIGGER t_trigger
AFTER INSERT ON t1 FOR EACH ROW

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
 i PLS_INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO i
  FROM t1;

  INSERT INTO t2
  VALUES
  (i);
  COMMIT;
END;
/


MySQL - LOCK IN SHARE MODE for EXISTS query I am starting a transaction and I need to check that a record exists, I don't need any values, but I need it to exist for the duration of the transaction for everything to remain valid.

It's VERY unlikely of course that someone will delete at more or less the same time someone else tries to do what this transaction happens, but as with all things concurrent 'very unlikely' isn't good enough.

I don't want to experiment either - I want to know for certain it'll work. Will something like:

SELECT EXISTS(SELECT * FROM tbl1 WHERE ... LOCK IN SHARE MODE);

Work as I expect it to?

For

I would imagine that LOCK IN SHARE MODE is simply implemented as a flag at the parsing stage, telling MySQL to acquire certain locks, thus this isn't a special case.

Against

MySQL treats EXISTS differently to a normal SELECT for obvious reasons. As per it's query optimiser it often reads constants before it starts running the query. An over-eager optimisation could defeat this.

I'm leaning towards 'for' but I want to be certain.

There are multiple type of locks in Mysql.
LOCK IN SHARE MODE in a shared mode lock on any rows that are read. So you need to pay attention to your query, how many rows are read (look also at examined column when doing explain).

The isolation level will also affect behaviors of certain locks.
I strongly advise to let Mysql take care of the locks.
When an update needs to occur it is usually the first come first serve.
make sure your Exist query is fast (best is to query on the PK).

If exists (select 1 from tbl1 WHERE ) Then
 perform your update
Else
Select "record does not exists"
End if;


Setting up Master-Master Replication in MySQL on a live system I've just recently taken on the task of looking after our mySQL databases, and have been given the task of setting master-master replication. We have a live server which has a mySQL database which is constantly receiving data and a backup server which has been configured as a replication slave to the master. What I need to do now is set the live server up as a slave to the backup. I have an idea of what is necessary, and I guess I'm just looking for someone to confirm I've got it right before I go and do it (or tell me a better way of doing it).

Create slave user on backup-server
Grant replication privileges to the user
stop slave on backup-server
set database lock(read only) on live-server so no new data can be added (and hence replicated to backup)
Get master status from backup-server
-open new ssh session to live-server and issue change master command to point at backup-server
start slave on live
unlock database on live
start slave on backup

create replication user on backup
configure replication on live as slave and backup as master
start slave
profit!


Find the user with closest taste of mine I trying to write in SQL the following requirement:

Each user can rate as much films as they like. (1= I don't like, 5=I love it)
And the system should provide to the current user a list of films that he doesn't rate yet and that other users like too.

To simplify, I have this table and the following data:

create table wich (
  uid int,
  film varchar(50),
  rate int
);

insert into wich values
(1, 'usual suspect', 5), (1, 'gataca', 4), (1, 'goldeneye', 2),
(2, 'usual suspect', 4), (2, 'gataca', 5), (2, 'i am a legend', 4), (2, 'the hobbit', 1),
(3, 'usual suspect', 1), (3, 'gataca', 5), (3, 'goldeneye', 5),
(4, 'usual suspect', 5), (4, 'goldeneye', 5),
(5, 'usual suspect', 5), (5, 'gataca', 4), (5, 'goldeneye', 5),
(6, 'usual suspect', 4), (6, 'gataca', 3), (6, 'goldeneye', 3), (6, 'shrek', 4);

This can be read as: User 1 like 'usual suspect' and 'gataca' but not 'goldeneye'.

So what I want know is to find the user(s) that have the closest tastes of the current user and get a list of films that the current user could probably like.

Following, what I have done so far:

Step 1: for every films rated by user 1, if user1 and user2 rate the film in the same way, compute a score (should be 0 if the difference is greater than 2)
and it's great if both like a film or both doesn't like a film.

select w2.uid, 1 as nb, case when abs(w1.rate - w2.rate) <= 2 THEN 5-abs(w1.rate - w2.rate) ELSE 0 END as score
        from wich w1
        inner join wich w2 on w1.uid<>w2.uid and w1.film=w2.film
        where w1.uid=1

Step 2: compute a score by user

select scores.uid, SUM(scores.score) * 100 / sum(scores.nb) as score
from (
    select w2.uid, 1 as nb, case when abs(w1.rate - w2.rate) <= 2 THEN 5-abs(w1.rate - w2.rate) ELSE 0 END as score
    from wich w1
    inner join wich w2 on w1.uid<>w2.uid and w1.film=w2.film
    where w1.uid=1
) scores
group by uid
ORDER BY score desc

step 3: find films that user1 has not yet rated that he could like

select w3.uid, w3.film,w3.rate, matches.score
from (
    select scores.uid, SUM(scores.score) * 100 / sum(scores.nb) as score
    from (
        select w2.uid, 1 as nb, case when abs(w1.rate - w2.rate) <= 2 THEN 5-abs(w1.rate - w2.rate) ELSE 0 END as score
        from wich w1
        inner join wich w2 on w1.uid<>w2.uid and w1.film=w2.film
        where w1.uid=1
    ) scores
    group by uid
    ORDER BY score desc
) as matches
inner join wich w3 on matches.uid=w3.uid and w3.rate >= 3
left join wich w4 on w4.uid=1 and w3.film=w4.film
where w4.uid is null
order by w3.rate desc, matches.score desc;

This seems to work, but I am not sure that this will still respond in a small amount of time if each user rate of lot of films and the table become become bigger.

What do you think ?

Is there a better way to accomplish such a thing ?

A working fiddle: http://sqlfiddle.com/#!2/89c57/1/0

Edit:

In my real table, the film column is an integer and I have a second table with films data (title, description, year, ...)

create table wich (
  uid int,
  film int,
  rate int
);

The example given here is just to simplify the problem with just one table.

You can make a table which holds the users with similar taste for every user.

CREATE TABLE `similar_users` (
    `uid1` INT,
    `uid2` INT,
    `score` INT,
    PRIMARY KEY (`uid1`, `uid2`)
)

The algorithm for calculating the score can be anything, as long as a higher score means a closer taste.

You you should limit the amount of similar users to a fixed number and also exclude the ones with a score less than a certain value (the ones with a not so similar tastes).

The contents of the table will be calculated periodically (e.g. every day).

Then step 3 from your solution becomes something like this:

SELECT w.film
FROM similar_users su
JOIN wich w ON w.uid = su.uid2 AND w.rate > 2
LEFT JOIN wich wn ON wn.film = w.film AND wn.uid = {current_user}
WHERE su.uid1 = {current_user}
ORDER BY su.score * w.rate DESC

Normalize it

The biggest thing that stands out to me is that you need to normalize the relationship here. You can find tons of stuff online about normalizing your database and ER diagrams.  But to cut right to the code, it means pushing out the film titles to what you could consider a "lookup table".  Then your wich table will just be all int codes.  More difficult for humans to read, but much better for machines to process, and avoids a lot of data duplication and potential data anomalies.

CREATE TABLE wich (
  uid int,
  film_id int REFERENCES film(id),
  rate int,
  PRIMARY KEY (uid, film_id)
);

CREATE TABLE film (
  id int PRIMARY KEY,
  title varchar(50)
);

To get the film's title to show up in your queries, you'll just want to JOIN the film table to get the title.

On performance

Your database will probably slow down as you data size grows UNLESS you add some indexes.  The indexes to make depend largely on your queries that you will run against the database.  A good place to start is to create indexes on:

columns that you are using as part of a JOIN condition
columns that you reference in your WHERE clauses


Intermittent connection errors I am experiencing occasional connection errors from my webserver to one of my databases.

The code attempts to connect. If it fails, it sleeps for 1 second, then retries, then (if it still fails) sleeps for 2 seconds more and retries, then gives up if it didn't work

The error messages that come back are:

SQLSTATE[HY000] [2003] Can't connect to MySQL server on '10.0.0.210' (4)
SQLSTATE[HY000] [2003] Can't connect to MySQL server on '10.0.0.210' (113)
SQLSTATE[HY000] [2003] Can't connect to MySQL server on '10.0.0.210' (113)

4 = interrupted system call

113 = no route to host

I am trying to work out what sequence of events might cause this to happen so I can try and resolve the issue. My hunch is that it's a network issue, but the two machines are both VPSs in the same cloud, so I am not sure how this could be the case!

If you are using ubuntu, give a try by using the below steps:

    run the command nano /etc/mysql/my.cnf
    comment bind-address = 127.0.0.1 using the # symbol
    restart your mysql server once.


Is it worth to separate columns into multiple tables for one-to-one relational table I need to make a decision for database structure on whether to separate one-to-one relational columns into multiple tables and link with one relationship id or just add all columns into one table.

The number of columns would be around 45 and I need to sort data on different columns on different query (one sort per query).

I will be using MyISAM storage engine.

Furthermore, there will be millions of data in the table(s).

Another thing to consider in deciding to split the tables or not is the width of the table if you put them all in one table. Many databases will allow you to define a table where the total length of all the fields is wider than the total record length allowed. You cannot however, put data into a record that would exceed the width. Therefore, if you are going over the limit, it might be wise to split the table. Further, most databases can store and retrieve data more efficiently in tables that are less wide. So if the fields you want to split out are not always going to be queried with the fields in the orginal table and the table will be large, it might be in your best interests to split the tables out.

Another thing to consider is if you are in a 1-1 relationship now, is it likely or possible that you will want to change to 1-many later? For instance suppose the fields you want to move are out are address fields. Maybe now you only need one address, but having them in a separate table makes it much easier later to have multiple addresses.

If you split the tables, make sure you define a unique index on the FK to preserve the one-to-one relationship.

If the relationship is really one to zero or one, you may be dealing with a class/subclass situation.  In ER modeling this is known as generalization/specialization.

If this applies to your case, I recommend you visit the following tags defined in Stack Overflow, and read up on the info stored in them.

[single-table-inheritance]
[class-table-inheritance]
[shared-primary-key]

If your case is not a class/subclass situation then this answer won't help you.


Can multiple instances of procedure create same #Temp simultaneously? I have a procedure which inserts data into #temp table.
After performing certain manipulations to the data, it stores data from #temp table to some other table.
I cannot run multiple instances of this procedure simultaneously. Why?
We create #temp table with the same name in multiple tabs. Then why shouldn't we be able to do the same for multiple instances of temp table?
Any solutions?
Example:
--exec dbo.testing_temp_table
CREATE PROCEDURE [dbo].[testing_temp_table]
AS
BEGIN

    SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF OBJECT_ID('tempdb..#temp_1') IS NOT NULL drop table #temp_1
    select  * into #temp_1 from dbo.Emp

END

"I cannot run multiple instance of this procedure simultaneously. Why?"

Could you clarify what you mean by "cannot run"? Do you get an error?
Now, you don't need to check the existence of the table in your stored procedure with the IF OBJECT_ID() thing.
SQL Server will create an instance of the temp table per instance of the stored procedure, and it will only be visible to the creating scope, which is the stored procedure in this case, and to inner scopes, such as if you call another sub-procedure in your code. Once the procedure completes, the temporary table will be gone.
Under the covers, the query processor appends some unique string to the table name in TempDB for each instance.
BOL has all the details: CREATE TABLE (Transact-SQL)
-- In response to OP comment regarding the stored procedure generating the same suffix. --
You are not giving SQL Server enough credit here. If your claim was true, this would have been a major bug; executing multiple instances of a stored procedure which use temp tables is completely supported.
I suspect that you are testing it on a single connection, and seeing that upon re-execution of the same proc, you get the same suffix. This is an optimization for reusing the suffixes for repeated executions from the same session.
What you should test is:
CREATE PROCEDURE P
AS
BEGIN
CREATE TABLE #T(Col1 INT);
WAITFOR DELAY '00:00:10';
SELECT * FROM TempDB.sys.tables;
END

And then on two or more connection, execute concurrently
EXEC P

Here is what comes up for me:

name

#T_____________000000000004

#T_____________000000000005

HTH

Your problem is scope.  @SQLRaptor is completely correct in his response above but it might help if you look at it like this:

If you create a temp table it is scoped to your session.  In other words it will exist until your session is closed or you or drop it.  What you are expecting is for your temp table to be scoped to your stored procedure (which isn't the case) like a table variable would be.  Example:

-- Query window (otherwise known as session in this case)
Create #temp1 (temp table)
Create @temp1 (table variable)
     -- Run a stored procedure
     Create #temp2 (temp table)
     Create @temp2 (table variable)
     -- Currently all four exist.
-- On leaving the stored procedure the following exist:
-- #temp1
-- #temp2
-- @temp1
    -- Rerun the same stored procedure
    You attempt to create #temp2 but it fails because #temp2 still exists.

Now if you leave the session (query window) entirely then all of them go away.

There are also global temp tables.  The ones with ## at the beginning.  These are scoped to all sessions and will only go away when no one is using them anymore.

Basically, unless you plan on re-using  your temp table for some reason drop it at the end of your SP.  So like this:

--exec dbo.testing_temp_table
CREATE PROCEDURE [dbo].[testing_temp_table]
AS
BEGIN

    SET NOCOUNT ON;

    -- Insert statements for procedure here
    select  * into #temp_1 from dbo.Emp

    -- The rest of your code goes here

    DROP TABLE #temp_1
END


"lock request time out period exceeded" Error When Trying to See DB Hierarchies I'm having problems with a database.

I can run basic queries, albeit much slower than normal.
When I attempt to view the hierarchy trees for tables, views, or procedures in SSMS Object Explorer, I get lock request time out period exceeded.
My SSRS reports that run on objects in this database are no longer completing.
Jobs associated with procedures stored on this database also do not run.

I tried using sp_who2 to find and kill all connections on the database, however this has not solved the problem.

What is going on here? How can I resolve this?

It was being caused by a perpetual rollback of a transaction.  Had to eventually restart my server cluster

I had this error pop up after a database shrink operation completed (but didn't release the space).  To fix it:

CHECKPOINT  (this took a bit to run)
Shrink the file again with "Release unused space".


Best design to reference multiple tables from single column? Proposed schema

First and foremost, here is an example of my proposed schema to reference throughout my post:

Clothes
----------
ClothesID (PK) INT NOT NULL
Name VARCHAR(50) NOT NULL
Color VARCHAR(50) NOT NULL
Price DECIMAL(5,2) NOT NULL
BrandID INT NOT NULL
...

Brand_1
--------
ClothesID (FK/PK) int NOT NULL
ViewingUrl VARCHAR(50) NOT NULL
SomeOtherBrand1SpecificAttr VARCHAR(50) NOT NULL

Brand_2
--------
ClothesID (FK/PK) int NOT NULL
PhotoUrl VARCHAR(50) NOT NULL
SomeOtherBrand2SpecificAttr VARCHAR(50) NOT NULL

Brand_X
--------
ClothesID (FK/PK) int NOT NULL
SomeOtherBrandXSpecificAttr VARCHAR(50) NOT NULL

Problem statement

I have a clothes table which has columns like name, color, price, brandid and so on to describe the attributes for a particular item of clothing.

  Here's my problem: different brands of clothing require differing
  information. What is the best practice for dealing with a problem like
  this?

Note that for my purposes, it is necessary to find brand-specific information starting FROM a clothes entry. This is because I first display the information from a clothes entry to the user, after which I must use its brand-specific information to purchase the item. In summary, there has to be a directional relationship between clothes (from) and the brand_x tables.

Proposed/current solution

To cope with this, I have thought of the following design scheme:

The clothes table will have a brand column which may have id values ranging from 1 to x, where a particular id corresponds to a brand-specific table. For example, id value 1 will correspond to table brand_1 (which might have a url column), id 2 will correspond to brand_2 (which might have a supplier column), etc.

Thus to associate a particular clothes entry with its brand-specific information, I imagine the logic at the application-level will look something like this:

clothesId = <some value>
brand = query("SELECT brand FROM clothes WHERE id = clothesId")

if (brand == 1) {
    // get brand_1 attributes for given clothesId
} else if (brand == 2) {
    // get brand_2 attributes for given clothesId
} ... etc.

Other comments & thoughts

I'm attempting to normalize my entire database in BCNF, and although this is what I came up with, the resulting application code makes me feel very anxious. There is no way to enforce relations except at the application level, and thus the design feels very hacky and, I anticipate, very error-prone.

Research

I made sure to look through previous entries before making a post. Here's a post with a near-identical problem that I managed to find. I made this post anyway because it seems like the only answer provided does not have a SQL or design-based solution (i.e. it mentions OOP, inheritance, and interfaces).

I'm also kind of a novice when it comes to database design, and so I'd appreciate any insights.

It appears there are more helpful responses on Stack Overflow:

Here
And here
Aaaand here (key concept being: class table inheritance)

I have referred to the solutions there and suggest others finding my question do so as well.

Despite the above-provided links, I am still on the lookout for responses here and would appreciate any solutions provided!

I am using PostgreSQL.

I personally don't like to use a multi-table schema for this purpose.

It's hard to ensure integrity.
It's hard to maintain.
It's difficult to filter results.

I've set a dbfiddle sample.

My proposed table schema:

CREATE TABLE #Brands
(
BrandId int NOT NULL PRIMARY KEY,
BrandName nvarchar(100) NOT NULL
);

CREATE TABLE #Clothes
(
ClothesId int NOT NULL PRIMARY KEY,
ClothesName nvarchar(100) NOT NULL
);

-- Lookup table for known attributes
--
CREATE TABLE #Attributes
(
AttrId int NOT NULL PRIMARY KEY,
AttrName nvarchar(100) NOT NULL
);

-- holds common propeties, url, price, etc.
--
CREATE TABLE #BrandsClothes
(
BrandId int NOT NULL REFERENCES #Brands(BrandId),
ClothesId int NOT NULL REFERENCES #Clothes(ClothesId),
VievingUrl nvarchar(300) NOT NULL,
Price money NOT NULL,
PRIMARY KEY CLUSTERED (BrandId, ClothesId),
INDEX IX_BrandsClothes NONCLUSTERED (ClothesId, BrandId)
);

-- holds specific and unlimited attributes
--
CREATE TABLE #BCAttributes
(
BrandId int NOT NULL REFERENCES #Brands(BrandId),
ClothesId int NOT NULL REFERENCES #Clothes(ClothesId),
AttrId int NOT NULL REFERENCES #Attributes(AttrId),
AttrValue nvarchar(300) NOT NULL,
PRIMARY KEY CLUSTERED (BrandId, ClothesId, AttrId),
INDEX IX_BCAttributes NONCLUSTERED (ClothesId, BrandId, AttrId)
);

Let me insert some data:

INSERT INTO #Brands VALUES
(1, 'Brand1'), (2, 'Brand2');

INSERT INTO #Clothes VALUES
(1, 'Pants'), (2, 'T-Shirt');

INSERT INTO #Attributes VALUES
(1, 'Color'), (2, 'Size'), (3, 'Shape'), (4, 'Provider'), (0, 'Custom');

INSERT INTO #BrandsClothes VALUES
(1, 1, 'http://mysite.com?B=1&C=1', 123.99),
(1, 2, 'http://mysite.com?B=1&C=2', 110.99),
(2, 1, 'http://mysite.com?B=2&C=1', 75.99),
(2, 2, 'http://mysite.com?B=2&C=2', 85.99);

INSERT INTO #BCAttributes VALUES
(1, 1, 1, 'Blue, Red, White'),
(1, 1, 2, '32, 33, 34'),
(1, 2, 1, 'Pearl, Black widow'),
(1, 2, 2, 'M, L, XL'),
(2, 1, 4, 'Levis, G-Star, Armani'),
(2, 1, 3, 'Slim fit, Regular fit, Custom fit'),
(2, 2, 4, 'G-Star, Armani'),
(2, 2, 3, 'Slim fit, Regular fit'),
(2, 2, 0, '15% Discount');

If you need to fetch common attributes:

SELECT     b.BrandName, c.ClothesName, bc.VievingUrl, bc.Price
FROM       #BrandsClothes bc
INNER JOIN #Brands b
ON         b.BrandId = bc.BrandId
INNER JOIN #Clothes c
ON         c.ClothesId = bc.ClothesId
ORDER BY   bc.BrandId, bc.ClothesId;

BrandName   ClothesName   VievingUrl                  Price
---------   -----------   -------------------------   ------
Brand1      Pants         http://mysite.com?B=1&C=1   123.99
Brand1      T-Shirt       http://mysite.com?B=1&C=2   110.99
Brand2      Pants         http://mysite.com?B=2&C=1    75.99
Brand2      T-Shirt       http://mysite.com?B=2&C=2    85.99

Or you can easily get Clothes by Brand:

  Give me all clothes of Brand2

SELECT     c.ClothesName, b.BrandName, a.AttrName, bca.AttrValue
FROM       #BCAttributes bca
INNER JOIN #BrandsClothes bc
ON         bc.BrandId = bca.BrandId
AND        bc.ClothesId = bca.ClothesId
INNER JOIN #Brands b
ON         b.BrandId = bc.BrandId
INNER JOIN #Clothes c
ON         c.ClothesId = bc.ClothesId
INNER JOIN #Attributes a
ON         a.AttrId = bca.AttrId
WHERE      bca.ClothesId = 2
ORDER BY   bca.ClothesId, bca.BrandId, bca.AttrId;

ClothesName   BrandName   AttrName   AttrValue
-----------   ---------   --------   ---------------------
T-Shirt       Brand1      Color      Pearl, Black widow
T-Shirt       Brand1      Size       M, L, XL
T-Shirt       Brand2      Custom     15% Discount
T-Shirt       Brand2      Shape      Slim fit, Regular fit
T-Shirt       Brand2      Provider   G-Star, Armani

But for me, one of the best of this schema is that you can filter by Attibutes:

  Give me all Clothes that has the attribute: Size

SELECT     c.ClothesName, b.BrandName, a.AttrName, bca.AttrValue
FROM       #BCAttributes bca
INNER JOIN #BrandsClothes bc
ON         bc.BrandId = bca.BrandId
AND        bc.ClothesId = bca.ClothesId
INNER JOIN #Brands b
ON         b.BrandId = bc.BrandId
INNER JOIN #Clothes c
ON         c.ClothesId = bc.ClothesId
INNER JOIN #Attributes a
ON         a.AttrId = bca.AttrId
WHERE      bca.AttrId = 2
ORDER BY   bca.ClothesId, bca.BrandId, bca.AttrId;

ClothesName   BrandName   AttrName   AttrValue
-----------   ---------   --------   ----------
Pants         Brand1      Size       32, 33, 34
T-Shirt       Brand1      Size       M, L, XL

Using a multi-table schema whatever of the previous queries will require to deal with an unlimited number of tables, or with XML or JSON fields.

Another option with this schema, is that you can define templates, for example, you could add a new table BrandAttrTemplates. Every time you add a new record you can use a trigger or a SP to generate a set of a predefined attributes for this Branch.

I'm sorry, I'd like to extend my explanations by I think it is more clear than my English.

Update

My current answer should works on no matter which RDBMS. According to your comments, if you need to filter attributes values I'd suggest small changes.

As far as MS-Sql doesn't allow arrays, I've set up a new sample mantaining same table schema, but changing AttrValue to an ARRAY field type.

In fact, using POSTGRES, you can take advantatge of this array using a GIN index.

(Let me say that @EvanCarrol has a good knowledge about Postgres, certainly better than me. But let me add my bit.)

CREATE TABLE BCAttributes
(
BrandId int NOT NULL REFERENCES Brands(BrandId),
ClothesId int NOT NULL REFERENCES Clothes(ClothesId),
AttrId int NOT NULL REFERENCES Attrib(AttrId),
AttrValue text[],
PRIMARY KEY (BrandId, ClothesId, AttrId)
);

CREATE INDEX ix_attributes on BCAttributes(ClothesId, BrandId, AttrId);
CREATE INDEX ix_gin_attributes on BCAttributes using GIN (AttrValue);

INSERT INTO BCAttributes VALUES
(1, 1, 1, '{Blue, Red, White}'),
(1, 1, 2, '{32, 33, 34}'),
(1, 2, 1, '{Pearl, Black widow}'),
(1, 2, 2, '{M, L, XL}'),
(2, 1, 4, '{Levis, G-Star, Armani}'),
(2, 1, 3, '{Slim fit, Regular fit, Custom fit}'),
(2, 2, 4, '{G-Star, Armani}'),
(2, 2, 3, '{Slim fit, Regular fit}'),
(2, 2, 0, '{15% Discount}');

Now, you can additionally query using individual attributes values like:

  Give me a list of all pants Size:33

AttribId = 2 AND ARRAY['33'] && bca.AttrValue

SELECT     c.ClothesName, b.BrandName, a.AttrName, array_to_string(bca.AttrValue, ', ')
FROM       BCAttributes bca
INNER JOIN BrandsClothes bc
ON         bc.BrandId = bca.BrandId
AND        bc.ClothesId = bca.ClothesId
INNER JOIN Brands b
ON         b.BrandId = bc.BrandId
INNER JOIN Clothes c
ON         c.ClothesId = bc.ClothesId
INNER JOIN Attrib a
ON         a.AttrId = bca.AttrId
WHERE      bca.AttrId = 2
AND        ARRAY['33'] && bca.AttrValue
ORDER BY   bca.ClothesId, bca.BrandId, bca.AttrId;

This is the result:

clothes name | brand name | attribute | values
------------- ------------ ----------  ----------------
Pants          Brand1       Size        32, 33, 34

One easy solution is to include all possible attributes as columns on the main clothes table, and make all of the brand specific columns nullable. This solution breaks database normalization, but is very easy to implement.


Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes I try installing SQL2008R2 - SQL2012 - sql2014

2014-07-17 16:31:16.00 spid14s     Error: 17190, Severity: 16, State: 1.
2014-07-17 16:31:16.00 spid14s     Initializing the FallBack certificate failed with error code: 1, state: 20, error number: 0.
2014-07-17 16:31:16.00 spid14s     Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
2014-07-17 16:31:16.00 spid7s      Starting up database 'msdb'.
2014-07-17 16:31:16.01 spid12s     Starting up database 'mssqlsystemresource'.
2014-07-17 16:31:16.01 spid14s     Error: 17182, Severity: 16, State: 1.
2014-07-17 16:31:16.01 spid14s     TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
2014-07-17 16:31:16.01 spid14s     Error: 17182, Severity: 16, State: 1.
2014-07-17 16:31:16.01 spid14s     TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.
2014-07-17 16:31:16.01 spid14s     Error: 17826, Severity: 18, State: 3.
2014-07-17 16:31:16.01 spid14s     Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2014-07-17 16:31:16.01 spid14s     Error: 17120, Severity: 16, State: 1.
2014-07-17 16:31:16.01 spid14s     SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Following can be reason for this error

The account which user selected on Server Configuration page window ( during installation)  is somehow not able to bring SQL Server database engine services online. Either is lacks privilege or it is corrupted. During installation of database engine services SQL Server tries to bring online database services as a internal process but due to startup account either corrupt or not having appropriate privileges it fails to do so and ultimately installation fails.

Other reason is when installation fails first time due to some reason and user uninstall failed installation from add remove program, the un-installation leaves account in broken state so any further attempt to install flashes this error message.

Solution:

Uninstall SQL Server completely from Add remove program and start new installation by right click on Setup and selecting run as admin. Now when you reach Server Configuration page please select NT Authority\System as SQL Server Database Engine service startup account.

http://social.technet.microsoft.com/wiki/contents/articles/24364.sql-server-troubleshooting-could-not-find-database-engine-startup-handle-error-during-installation.aspx

PS: If this does not helps please revert. Please follow steps mentioned in link carefully

Select a valid user account on installation and the correct password instead of NTUSER/LOCAL and that's it. Preferably select also WINDOWS/SQL_SERVER ACCESS.


Mysql - How to Optimize Retrieval Time in a Table I have query which has 200 million Records in a single table. I am using BTree indexes in my table:

mysql> select COUNT(DISTINCT id) from [tablename] where [columname] >=3;
+------------------------------+
| COUNT(DISTINCT id) |
+------------------------------+
|    8242063
+------------------------------+
1 row in set (3 min 23.53 sec)

I am not satisfy with this timing.

How can I reduce the result time less than 30sec. Kindly give me any suggessions! It will be more helpful to me!

Maybe GROUP BY can help. See this MySQL reference on DISTINCT optimization

SELECT COUNT(*)
FROM (
  SELECT 1
  FROM [tablename]
  WHERE [columnname] >= 3
  GROUP BY id
) q

Can you try the following?

select COUNT(id) from [tablename] where [columname] >=3 GROUP BY id;


SELECT * WHERE VarCharColumn IN (...) Optimization I have a list of 3000 strings, and I am passing them (twenty at a time) into a parametrized IN clause. It definitely isn't getting the results I would like to see ~ 500ms per execution.

The column is an index. Do you know a better way than this:

SELECT * FROM [ohb].[dbo].[MasterUrls] WITH (NOLOCK) WHERE Hash
IN(@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19)

A list of 3000 takes between 3 and 5minutes. I really need that down to around 30 seconds. Is this possible?

I am using MSSQL 2008 R2 on a server with 24 gigs of RAM, and dual 8core NUMA Xeons @2.4Ghz running on a 6HDD (@15k/rpm) RAID 10 ISCSI.

The table has 1.4M rows, and the index is a non-clustered index.

Execution plan shows the index scan as 90% of the total execution.

SELECT * will invalidate any optimal use of an index (it isn't covering) even if hash is indexed. Your index scan is most likely on the clustered index because of this.

Personally, I'd start with

putting the 3000 search values into a table with an index
Edit: as per Marian's comment, this can be passed in a list or table already
use this in any of JOIN, IN, EXISTS (same plan usually)
ensure my index on MasterUrls suits using Hash and covers col1, col2, col3

Something like

CREATE TABLE #foo (Hash ...)
INSERT #foo...
CREATE INDEX IX_FOO ON #foo (hash)

--either
CREATE NONCLUSTERED INDEX IX_Hash ON MasterUrls (hash) INCLUDE (col1, col2, col3)
--or
CREATE CLUSTERED INDEX IXC_Hash ON MasterUrls (hash)

SELECT col1, col2, col3
FROM [ohb].[dbo].[MasterUrls] M
JOIN
#foo F ON M.Hash = F.Hash

I actually solved this in a MUCH faster SQL-less fashion.

In another step before this one, I grab the URLs and IDs from the table (instead of just the URL, which i would hash for the lookup -- this question), save them in memory, then on the FS (in case memory failed -- async, of course), then when it came time to do the lookup I read from my data I stored in memory/FS.

The process now takes less than 5 seconds on average to do a lookup and update (the step after this question) the data for 3000 rows. Much better than 240 seconds on avg.