Questions tagged [varbinary]
varbinary is the SQL Server datatype used to hold variable-length binary data
35
questions
0
votes
1
answer
719
views
VARCHAR automatically changes to VARBINARY
I am new to MySQL. When I declare a column as VARCHAR, why does it automatically change to VARBINARY? How should I avoid this?
Database changed
...
CommunityBot
- 1
1
vote
1
answer
145
views
Rolling up multiple varbinary(max) rows into a single row
My table looks something like this:
BinFiles
0x1101ABB1C...
0x2202DEE2F...
0x3303GHH3I...
I tried this to get a single line:
SELECT
STUFF((SELECT d.BinFiles
FROM dbo.demo d
WHERE d.ID ...
- 66
5
votes
2
answers
650
views
Varbinary update attempt
Is it possible to adapt this into update query which has to seek a free sequence of 3 bytes or (0x000000) and replace it with a new sequence if the data is split like this every 3 bytes? The issue is ...
- 88.3k
2
votes
1
answer
1k
views
Varbinary pattern search
I'm trying to make a query which goes through varbinary data. The issue is that I can't really finish what I'm trying to achieve. What you should know about the column is varbinary(50) and the ...
- 85.9k
1
vote
1
answer
1k
views
LIKE or match by beginning for binary strings in PostgreSQL
I need to match a binary column with its beginning.
For text columns it's easy: LIKE 'image/%'. This uses BTREE index if any.
Is it possible to do this also using an index for a binary column?
BTW, I ...
CommunityBot
- 1
0
votes
1
answer
355
views
Detecting whether a varchar value is already encrypted by EncryptByPassPhrase() in SQL Server
Starting with a varchar(100) column named Password in a table Users with one record in it, after performing an update on the column to encrypt it with EncryptByPassPhrase() on the Password column the ...
- 26.6k
1
vote
1
answer
661
views
Why can't binary data be inserted/displayed as ones and zeroes?
If I have a column of type binary or varbinary, I imagine the data as a sequence of bits. For example, it makes sense to me that 01001 (as a base 2 number) could be a valid value in a binary(5) column....
- 18.1k
1
vote
0
answers
628
views
Speed up reading data from a table
Problem
We have a table that has just 44 rows, and one of the columns is varbinary(max).
This column stores XML files converted to varbinary(max).
Average length of data in this particular column is ...
- 88.3k
3
votes
3
answers
609
views
Varbinary startswith query using index
I've got this tree-like key (think object OIDs--it's very similar) to store and index in a table, for which queries want to select subtrees thereof. The most straightforward way to do this on a btree ...
- 88.3k
1
vote
2
answers
4k
views
How does SSMS display varbinary data?
When you store a .jpg file in a varbinary(max) column and then view the data in SQL Server Management Studio, it appears like "0xFFD8FFE000..."
Is this Hex Encoded or something? What is SQL ...
- 121
6
votes
3
answers
8k
views
SQL Server - Varbinary Column - Extremely Slow Statistics Update
I have a table in my database that we use as a filestore, the file itself is stored in a varbinary column, which all seemed to work well until recently, we noticed one of our instances of this table ...
CommunityBot
- 1
5
votes
1
answer
2k
views
GZip existing varbinary(max) column in Sql Server 2008+
I have an existing legacy table that is ~180GB in size due to a client application storing PDF files as varbinary. I'd like to be able to compress that column for all rows using GZIP to help save ...
- 69.4k
0
votes
2
answers
8k
views
BLOB or VARBINARY to store simple Strings that are converted into byte[ ]?
I've read that In MySQL, BLOBs are often used to store image files, file path pointers, video & audio files, and any other big data objects.
But can BLOBs be used for simple Strings that are ...
CommunityBot
- 1
0
votes
1
answer
451
views
How can I decrypt data on a replicated MSSQL database?
I have a database which uses a symmetric key to encrypt a credit card field. There is a trigger on the credit_card field which runs every time it is updated, to encrypt the contents, and save it to ...
- 34.9k
4
votes
1
answer
5k
views
Insert String Value To Binary(64) Column
I am attempting to use a stored procedure to insert data into a table. The DDL of my table is like this:
CREATE TABLE [dbo].[data1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[fname] [varchar](100) ...
- 181k
5
votes
2
answers
5k
views
Please enlighten me about FILESTREAM and BLOB files
This is a really confusing topic for me.
I can understand what a BLOB is, I can easily use it.
I can understand what FILESTREAM is. I can easily implement in on a database, I can backup the database ...
- 62.4k
1
vote
1
answer
282
views
Exporting Stored PDF into an Email
Just a quick question on whether something is possible to do.
Database: MSSQL
We have a customer that we generate emails for based on sales (e.g. Sales Person X sold Y amount of stock). Against ...
- 21
0
votes
2
answers
1k
views
Store Mesh in database
I hope this is the correct forum but will ask anyway:
I have lots of Mesh data I want to store. Mesh is actually triangles. I hope it's not too of beginner question but wouldn't Geometry type of SQL ...
- 13
8
votes
1
answer
2k
views
Best way to shrink a DB after nulling data from varbinary(max)?
We have a database with a large amount of data stored in a field of type varbinary(max). At some point we can purge that data for most rows, but not all. Our plan is to make that field nullable, and ...
- 88.3k
1
vote
2
answers
18k
views
Update a binary field
How to update a binary field from 0x54 to 0x46 (T to F)?
Update dbo.table1
set field = convert(varchar,field,0x46)
where fieldID = '1'
CommunityBot
- 1
0
votes
1
answer
1k
views
How to handle the junk characters upon conversion of the data from image datatype to varchar?
We are trying to convert the column of type image to column of type varchar(max). We tried the below way to do the conversion.
--col1 is of type image
--col2 is of type varchar(max)
set col2 = ...
- 69.4k
2
votes
1
answer
2k
views
Does IMAGE column affect query performance even if it's not included in the query?
Given the following table storing multiple milions of rows:
CREATE TABLE [dbo].[files]
(
[ID] UNIQUEIDENTIFIER CONSTRAINT [a] DEFAULT(NEWID()) NOT NULL,
[OOUID] UNIQUEIDENTIFIER ...
CommunityBot
- 1
2
votes
3
answers
3k
views
Index Maintenance for varbinary
We have a SQL 2016 database that has a 1.9bn row table which has a varbinary(255) column which we use to store the HashBytes of an nvarchar(2000) field in the same table.
We have a Non-Clustered ...
- 69.4k
2
votes
1
answer
19k
views
SQL Server - How can varbinary(max) store > 8000 bytes?
In SQL Server 2005 and 2008 R2, I'm calling into a stored procedure that has an out parameter defined as varbinary(max). The out parameter returns 10020 bytes according to DATALENGTH.
However, SQL ...
- 70.3k
0
votes
1
answer
3k
views
SQL Server Transaction Commits but results are missing
I am not sure how to describe the problem, so I will do the best I can and answer any questions.
My project is a Database Project for SQL Server 2008 (client dependent). Within that project I have a ...
- 69.4k
1
vote
1
answer
803
views
Why does updating a large VARBINARY break replication (MSSQL_REPL21100)?
We have replication set up on a VARBINARY(MAX) column that stores image data (yes, yes, I know - don't blame me, I didn't design it). Recently, we started inserting larger images (around 200kb) and ...
- 371
4
votes
1
answer
10k
views
Convert Byte Array From XML to VARBINARY
I receive image files as XML data, each byte of the image being a node with its decimal value, e.g. for this example .png file, , the xml I get is:
DECLARE @xml XML = N'<?xml version="1.0" ...
CommunityBot
- 1
0
votes
1
answer
2k
views
Update image using dynamic sql
I have been battling with this statement:
ALTER PROCEDURE [dbo].[transact_image_update]
-- Add the parameters for the stored procedure here
@transact_recordID_int int,
@image1_bin image,
@...
- 181k
22
votes
2
answers
226k
views
Converting a VARCHAR to VARBINARY
I've been keeping a log of expensive running queries, along with their query plans, in a table to allow us to monitor trends in performance and identify areas that need optimising.
However, it's come ...
- 1
3
votes
2
answers
2k
views
8Kb row size limits ignored - but only sometimes!
As a result of poorly tested code (not mine!) I've ended up with a table containing 319 VARBINARY(MAX) fields, plus two DATETIME fields and two UNIQUEIDENTIFIER fields. Obviously this is not ideal, ...
CommunityBot
- 1
1
vote
1
answer
141
views
sql server replication - how to get combination of values from a binary(8) field?
I am using the table sysschemaarticles to collect information in my transactional replication.
It is used for a variety of things including adding new articles and finding out who are the subscriber ...
- 70.3k
0
votes
1
answer
6k
views
converting then outputting varbinary(max) FOR XML PATH
I have a vendor application that has stored some of its text data in a varbinary(max) column. I am exporting this data (from several tables with different data types), ideally using FOR XML PATH. What ...
- 103
1
vote
2
answers
2k
views
Trying to filter msdb..sysjobs by job_id
I'm attempting to determine the name of the SQL Server Agent Job that is currently running by comparing the output of master.sys.dm_exec_sessions.program_name to msdb..sysjobs.job_id
I'm using the ...
- 181k
1
vote
2
answers
2k
views
How do I migrate varbinary data to Netezza?
I got a warning message while migrating DDL from SQL Server to Netezza:
Warning: [dbo].[spec_binarymessage].[blobdata] data type [varbinary] is not supported the target system and will be scripted ...
CommunityBot
- 1
8
votes
2
answers
12k
views
Comparing binary 0x and 0x00 turns out to be equal on SQL Server
It seems that SQL Server considers 0x and 0x00 equal values:
SELECT CASE WHEN 0x = 0x00 THEN 1 ELSE 0 END
This outputs 1.
How can I get true binary bit-for-bit comparison behavior? Also, what are ...
- 31